Excel enthusiast needs expert help!

Hello everyone,

I've always found Excel to be the most flexible and easiest program to use.  I'm trying to build a spreadsheet, and I need it do several specific functions.  I've done a lot of experimenting, but have not got it all.  I'm hoping someone here can assist me, or at the very least, let me know if what I need is even feasible/possible using Excel. 

I am using Excel 2003 (I'd love to go 2007, but it's my company).  There are multiple users at different locations that will need to access and make changes to the spreadsheet.  I'll accomplish this by posting the spreadsheet on a shared server.  Granted, only one user at a time will be able to access and make changes to the spreadsheet at one time, and this is acceptable.  The spreadsheet will be tracking inventory movement between multiple locations, and will keep a running log indefintely.  It will log dates, times, items, etc.

The first thing I'd like to do is set up conditional formatting to change the cell color to gray if the cell is blank.  Once data (any data) is entered into the cell I want it to change to white.  The idea is to create a "visual cue" showing what other information is required.

The second thing I'd like is to require that once the information in the first cell on a row is entered (a control number), that the user must enter information in the remaining cells before saving (and closing) the spreadsheet.

The third thing I'd like is to allow the user to enter data in a blank cell, but prevent the user from deleting data once entered.  I'd also like to remove from the user the ability to delete a row or column, or the worksheet itself.  Basically I'm concerned about data loss through user error.

Any help/ideas/suggestions/directions/etc. is much appreciated.  THANKS!!!!!

I'd seriously look at using an input form. Basically you use a macro to open and initialize (maybe put today's date in etc) the form. Then a macro (activated by clicking a button on the form) that verifies the data fields and then places them on a row on a spreadsheet tab. You could have the tab hidden, or make it read only so that only the macro can add information to the tab.

Dave,

As Omar suggests, an input form may be your best bet.  I have attached a simple data entry form for you to have a look at.  The form is launched when the user changes selection on the worksheet.

To prevent data loss, the worksheet is protected, and this can be disabled/reenabled through the userform only when the data is being entered.

You can add validation routines to the form to ensure data types meet your requirements.  I haven't included that function in the attached file, but this should get you started.

You can also edit the form so that if no control number is entered, the data can not be added.  Similarly, you can modify the data entry so that the validation routines (not included) will not allow data entry if there is missing data.

To make the sheet multi-user, you could pass the data to a database (Access should be suitable) and re-populate the worksheet every time the workbook is opened.  Even if there are multiple users accessing the data, they can all add or edit data simultaneously, even if the workbook is opened as "Read Only".

Mattheq:

To make the sheet multi-user, you could pass the data to a database (Access should be suitable) and re-populate the worksheet every time the workbook is opened.  Even if there are multiple users accessing the data, they can all add or edit data simultaneously, even if the workbook is opened as "Read Only".

Thanks for fleshing out my initial thought.

Tell me more about the multi-user idea. Will Excel 2007 read/write to an Access database directly? I have a few places where such an idea might be useful.

That's fantastic, my thanks to you both!!  This is actually better than what I hoped to achieve, the data entry form is perfect.  I appreciate your help, and I'm hoping to impose upon you one final time.  I'm self taught on Excel, and I've had good success learning as I go.  I'm hoping that maybe you can point me towards some information on line where I can learn the following:

- Data Entry Form

- Macros

- Validation (I'm somewhat familiar with it already, however I can't always make it work the way I think it should, so there are probably some gaps in my understanding). 

I've tried the MS help function, and with moderated success.  It can be a challenge if you don't phrase your question correctly (which is difficult when you don't fully understand the subject matter to begin with). 

Again, I'm grateful for any assistance you can provide, and thanks again.

Omar / Dave,

I have attached a Zip file that contains two files:

• Excel 2007 spreadsheet containing user form and database connection code.
• Access 2003 database containing one table.  The Access database must be in 2003 format for the code to work..

This shows the way I use data entry forms in Excel along with examples of select, insert, update and delete SQL statements for interacting with the data in an Access database.

It's pretty raw, so the forms aren't pretty.  Email me if you need any specific help with these.

Dave,

The data entry form will need to incorporate validation routines including:

• IsNumeric
• IsDate
• IsNull

These are included in VBA and there should be reference material in the local help files.  These functions return boolean values, so you can trap data errors in your form as the user enters data.  This should be a useful starting point.  You will need to decide when to check the data by using the control events (change, enter, etc.).  That's a whole different topic, though!

Matthew, thanks for the example. I've spent a few minutes reviewing the methods you used. This should be a great training aid for me. On a recent project, I stored some data in spreadsheets, against my better judgement. You've given me the tools to migrate that data to a single Access database.

Thank you,

Omar

Mattheq,

Whew! I've been digging through your first file, and VBA is like a whole other world!  I think I'm starting to see how it all ties together, and what makes everything work.  I'm starting to try and adapt it to my own spreadsheet, so here's hoping.  In the meantime, I have a couple other hypotheticals for you (if you don't mind).

- On your spreadsheet, I can still delete the entire worksheet.  Is there any way to disable this function in VBA?

- I'd like to create a drop down menu on the Data Entry form with a list of entry/exit points.  I could do this in Excel with Data Validation List and I would assume there is something similar with VB, just not sure where to start looking.  Suggestions?

- This one is more tricky to explain.  Think of the Data Entry form as a 'ticket' to track a piece of property, it has a control number, basic info (name, company, etc.), date and time.  Below that would be a button, "add property".  Clicking on it would open four windows to the right for color, make, model, and serial number.  Ideally, I'd like to have 3-5 of these on the Data Entry form.  I'd like to use VB to assign each property entry a seperate Control Number, but list the same basic info and date/time.  Once the user clicks "ok" it would enter the data in subsequent lines of the spreadsheet.  Any suggestions or ideas you might have are appreciated.

That's more or less everything for right now.  As always, my goal is to figure it out for myself as much as possible (I'll have to maintain it after all!), mostly I just need someone to point me in the right direction.  I hope you don't mind my constant questions, I've never done anything with VB, so having a guide is a real life saver!!

Best Regards,

Dave

Dave,

With respect to deleting the worksheet, you can do this without VBA:

1. Tools
2. Protection
3. Protect Workbook
4. Check Protect Workbook for Structure (Use the password to prevent inquisitive users from 'experimenting')

This will prevent users from deleting the worksheet while enabling data entry.

Your drop down on the data entry form can come from a named range, and the use of a combobox.  When initialising the form, add the items to the combobox using the AddItem method.  The named range can be anywhere in the workbook, but you might want to think about it being on a hidden worksheet, something that users can't access or modify.

I'm not quite clear on your last point: four windows from one button or one window with the four controls on it (colour, make, model, serial no.)?  You could open four windows, but they would be sequential, not simultaneously (to my knowledge, but I'm no expert).  Either way, you can use the combobox AddItem method.  each control (combobox) text could then be passed to a declared variable and stored until ready to assign to a property.

Mattheq,

Thanks for the answers on the first two, and I think I made things more complicated on the third.  What I am building is a spreadsheet to track property movement.  User A may have 3 items to register and move, User B only one.  What I was looking for on the Data Entry form is to have all of the basic data first (name, date, etc.), then an "add property" button (similar to the cancel/ok) that when clicked would reveal four boxes to the right for the color, make, model, serial number (as opposed to a seperate form and/or window).  There would be 3-5 of these buttons, so using the past example, User A is moving 3 items, the basic data would be filled out once, and then the first three "add property" buttons would be selected, and information for each of the three pieces of property would be entered, and the the "ok" button would be selected.  Ideally at this point the VB would input data for each piece of property, using the same basic info for each.

Basically I'm trying to avoid requiring someone to type the same info multiple times if there are multiple pieces on the "ticket".  Hopefully that makes more sense.  I apologize, I know what I want it to do in my head, but I'm unsure of the the actual mechanics of it.

As always, thanks for your time (and patience).

Best Regards,

David

David,

If I understand correctly what you are trying to do, the attached workbook should be a good guide as to how to achieve your goal.

You can edit the code to create the controls at run time, however I have just copied and pasted rows of controls for each property.  I also haven't added any functionality behind the "OK" button on the properties form, as this was demonstrated a couple of files ago.