database spreadsheet set up for EASY data entry 4

[electricpete]

I would like to set up a very simple database with the goal of making data entry as “easy” (intuitive) as possible. Hopefully someone who doesn’t know excel can walk up, launch the file, and be immediately presented with a data entry screen.

Is it possible. The fields I have in mind are:
DATE
MACHINE ID
VOLUME OF OIL ADDED (OUNCES)
REASON FOR ADD
COMMENTS

If possible we would like REASON FOR ADD to offer the following choices.
LEAKAGE
NORMAL USEAGE
SAMPLE
MAINTENANCE OIL CHANGE
UNKNOWN

I have looked a little at the help for the forms feature but I’m not famiiliar with it and any suggestions would be appreciated to get me started.

One hurdle is getting the form to launch immediately upon opening the file.

Another hurdle - From brief experimenting with forms it seems like the form sometimes seems to come up with the last data filled in and I have to press “new” button to get a new data entry screen. I don’t want that... want a fresh (blank) data entry screen immediately when open the file.

Another hurdle - I don't know how to create the "list-of-values" choice for "REASON FOR ADD" using forms.

Any thoughts, suggestions, comments, or examples?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[WMO]

electricpete,
To get you started:
To display your form at workbook open:
in a module insert a sub procedure to display your form:

SUB ShowForm
USERFORM1.SHOW
END SUB

In the THISWORKBOOK object add the call

Private Sub Workbook_Open()
Module1.ShowForm
End Sub

 

[Guidoo]

Are you familiar with the data validation option in Excel? In the Data menu select Validation. On the Settings tab chose Allow "List". In the Source section you can either type a list Leakage,Normal Use, etc. or select a range in your spreadsheet that contains these items.

 

[electricpete]

Guidoo - It seems like validate data works on direct entry to cells but doesn't cooperate well with forms. I get an error message when trying to enter invalid data but the form doesn't give me any clue what the valid data is.


WMO - So far I launch the form by selecting range and selecting data/form. I think yours works with a form template. I haven't figured out how to use a form template to update a database spreadsheet.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[WMO]

electricpete,
Are you wanting to do this in VBA?

 

[Guidoo]

electricpete,

Do you really need to use forms, or can you just use the standard Excel spreadsheet? As per the Excel help, forms are used "to collect data from Web and e-mail users."

 

[WMO]

electricpete,
I agree with Guido on this. If you want the benefit of a dropdown list choice then data validation is the ticket but it will not work with data/form.

By selecting the range and using data/forms if you enter into a cell (where data validation governs)and you violate the data validation it generates an error with a retry or cancel option. You would have to know what the data validation rule is. So if you set up a data validation list that included "LEAKAGE" and someone enters "LEAKS" it kicks out and the user would not know that it has to be leakage.

I think either keep it as a regular spreadsheet as Guido suggested or delve into creating it with VBA

 

[electricpete]

The problem is that data will be entered by a wide variety of plant operators, many of whom just don't know what excel is. While it may seem obvious to us that the way to add data to a list is to go to the bottom and type it in, that's not obvious to some of them.

So, data entry on some kind of data entry screen (not the bottom of a lspreadsheet) is mandatory.

Data type checking in the "REASON FOR ADD" field is nice but optional.

I have used two or three vba functions. I want to learn more about it one day and maybe this is the day. If it can be broken into manageable chunks like the example above to open a form upon opening a spreadsheet, I think I can handle that.

One thing I am thinking is that I can almost mimick an excel form by creating a data entry tab with instructions and macro's.

Still open to ideas/suggestions and comments. I haven't devoted a lot of time to the problem yet, just hoping to get some input before I really get started.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

You should still be able to use the worksheet itself as the entry form, by locking out all but the actual entry cells. This would allow you to use whatever features you desire from the worksheet.

You can then use VBA to take the validated data from the cells and transfer the data to where you want. The VBA could be activated by a simple button embedded in the sheet.

TTFN

 

[bpeirson]

Here is some code that I use in a form which loads data lists from an Excel spreadsheet into ComboBoxes on the form when the form is activated.
Ranges A1 through I1 on the sheet have a Counta formula to count the number of items in the list. This number is loaded into i and used as a counter which then adds each cell item into the combobox, The ComboBoxes are named Company, Project, Price_index, Reciever and Contact. The .Additem method is what puts the info into the combobox.

When the form opens the user will either type in the boxes or use a pull down list to select a choice. If typing, the combobox can auto complete the most logical choice. Mine is currently set to allow entries which do not appear in the pre loaded list, I am not sure if you can limit the input but it seems likely that you can.

When the user is done entering data he can click the finish button which then transfers the values of the combo boxes to the applicable cells. You should not have problems with persistent values remaining in the boxes. If you do you can have the code set all values to "" or 0 after writing chosen valuse to the Excel spreadsheet.

Your validation data will probably not be on the same sheet as the information to be kept, therefore you will have to activate the appropriate sheets before reading or writing information. If you need help with any of this respond with your questions.

Private Sub UserForm_Activate()
    On Error Resume Next
    Dim x As Integer, i As Integer, NM As String
    Windows("^Customer Data.xls").Activate
    Sheets("Sheet3").Select
    i = Range("A1")
    For x = 1 To i
    NM = Cells(x + 2, 1)
    Company.AddItem NM
    Next
    i = Range("D1")
    For x = 1 To i
    NM = Cells(x + 2, 4)
    Project.AddItem NM
    Next
    i = Range("C1")
    For x = 1 To i
    NM = Cells(x + 2, 3)
    Price_index.AddItem NM
    Next
    i = Range("I1")
    For x = 1 To i
    NM = Cells(x + 2, 9)
    Receiver.AddItem NM
    Next
    Sheets("Sheet2").Select
    i = Range("B1")
    For x = 1 To i
    NM = Cells(x + 2, 1)
    Contact.AddItem NM
    Next
   
End Sub

 

[francesca]

Surely this would be much, much easier in Access? It is very easy to set up a Table and a Form in Access and then to customize the database so that only the Form shows up for certain users.

If you're not confident analyzing data in Access, you can always export the Table data to Excel.

 

[bpeirson]

You're probably right about Access. I had to make a decision about 2 years ago regarding the tracking of data. I could either learn how to use Access or how to use VBA with Excel.

I chose to learn VBA due to my opinion that it would be easy since I was adept at using Excel. It was not easy.

Learning VBA may have been the long route but I feel that it was more versatile since VBA can be applied to more than one application.

Using Excel as a database is awkward and prone to error. Excel's strength remains in calculating and information translation. The best solution would be to use Access (or another database system) to manage the data and Excel to interpret what the data means.

 

[electricpete]

The computers that will enter data into the "database" don't have MS Access installed. I'm trying to use the tool readily available (excel)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[johnwm]

Yes you can use Excel as a database, same as you can use a micrometer as a welding clamp - but it won't perform as well as the right tool for the job.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[pmover]

a few years ago, i looked into using the built-in feature of excel called Template Wizard with Data Tracking. it is an add-in that came with excel; so it must be activated before using.

the excel help system provides information to get you started in creating what you need. btw, it is possible to use the excel template and store the data in an access database without having ms access installed. the computer needs the drivers. i would suggest searching the mighty web for further help with this feature. perhaps john walkenbach, ozgrid, mrexcel, etc, websites may be of further help. sorry for not listing the exact website, but i'm being sloppy today - sort of anxious too.

also, excel can also be used to extract data (via query) from ms access & other databases. the user has many options to select data and then sort and arrange as needed.

concluding, investigate the template wizard with data tracking feature or add-in within excel and then search the web for further help.

good luck!
-pmover