Simple Data Entry form

[dustmaker]

I use to be pretty good with old Lotus macros, but when the world went to Excel and visual basic, it left me behind and I've been trying to avoid VBA for 15 years.

However, now I am faced with trying to create a simple input screen and I find I lack the basic skills to do this. I've built an input form with data validation fields, vlookups and formulas that change the fill color of certain cells based on date entered, but I want apply a simple VBA which will make the cursor move to the right when the person hits enter. I know this is very basic but I've search this site for two hours and I can't find the information I need to build this simple input document. I want the VBA to wait for a user to enter something in a cell and press enter.

The complication I've added to my spreadsheet is that the sheet is protected and I only want the person to move to the next nonprotected cell and once they reach the last non protected cell on a row (column J), I want the cursor to move down one row and 9 rows to the left and repeat.

I want this VBA to start when the spread sheet is opened.

Can some one provide me with some basic code to achieve this simple form.


I thank you in advance for any guidance you can provide.


DustMaker

[russtownsend]

Can you provide an example workbook please ?

[Aussiebear]

We can easily set up a macro to control where the User goes to (a sheet or form) on opening the workbook. We can also control where the focus is set to, and control next cell to focus on, and remind the User to fill certain cells before moving to the next cell.

Please attach a sample workbook, by clicking on Go Advanced, scroll down to Manage Attachments and follow the prompts from there.

[Bob Phillips]

You already have what you need. Get them to hit the TAB key after entering data.

[dustmaker]

attached is as extract from my working file.

this is to be used for a physical inventory and specifically for tag entry.

the sheet must be protected, which will restrict access to fields I don't want them in.

My goal is to minimize the key strokes while at the same time ensuring correct data.

The attached file has sheet protection turned on, but no password.

Thank you

[dustmaker]

Can you provide an example workbook please ?

[Kenneth Hobs]

When you protect the sheet, unselect the first option that allows them to select locked cells.

[dustmaker]

When you protect the sheet, unselect the first option that allows them to select locked cells.

Thank you. I am already doing this.

What I am trying to do, is change individual cells from unprotected to protected so that the option you mention works completely. What i am building is a physical inventory tag input screen. Items can be counted as eaches or feet or pounds. the unit of measure is pulled in from a lookup table. Based on this value, I want to protect the cells that do not apply. For example, if the part number has a Uof M of pounds, then I want to protect the cell for eaches and the cell for feet. This way, it will save key strokes because their cursor will go directly to the pounds field and won't let them enter a value in feet or each.

I'm unable to find a formula that would change the format of a cell to protected or unprotected, so I am attempting to do this in visual basic.

I hope this clarifies what I am trying to do.

[Kenneth Hobs]

Protect the columns and it should work ok and then the protect that I explained. I don't see a reason to edit the protected cells.