Tab Key Change Event

[jaslake]

I have a user input box (VBA) with two columns of data entry. The leftmost column has text boxes labeled color1, color2, color3, etc.. The rightmost column has text boxes labeled tag1, tag2, tag3, etc..

I have the TAB sequence set to go from color1 to tag1, color2 to tag2, color3to tag3, etc..

A user can inadvertently tab over the color1 (or color2, etc. columns) into the tag1 (or tag 2, etc. columns) column without entering data in the color column.

Is there coding to allow a TAB key entry to be a Change Event such that, if a user TABS out of color1 without entering data, a MsgBox could signal that they must enter data in the color1 field before they can continue?

Alternately, can you suggest a different approach? The goal is to require an entry in the leftmost column (color1) before they can proceed to the tag1 field. Of course, they are given a "Cancel" option.

I've searched the web and haven't found any references to this subject.

Thanks for you kind assistance.

John

[mikerickson]

You could use the Before (or After) Update event.

Please Login or Register  to view this content.

[jaslake]

Mike:

It's half working. It's not firing on the first TAB out of the box and when it does fire, it fires at inappropriate times. I'll spend some time Debugging and see if I can find out where the misfires are happening.

My code is as follows:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

I'm working one color at a time.

Thanks for your input. I think you have me headed in a direction; I just need to figure out what that direction is. Hope you don't mind if I get back to you.

John

[jaslake]

Mike:
Well, I've done some Debugging and I get these results:

In:

Please Login or Register  to view this content.

the value of Cancel is "False".
the value of NoEntryIn(color1) is "Y". This is a valid entry.

In:

Please Login or Register  to view this content.

the value of NoEntryIn is "False"
the value of (color1.text) is "Y"
the value of vbNullString is '' "

So, I suspect that the problem is the value of 'vbNullString" being blank or empty is part of the issue. I tried to set "vbNullString=color1.text" but VBA didn't like that.

Any clue where I need to go from here? For what it's worth, I've attached a copy of the file. Please don't laugh at the code (you can smile if you wish). I know it's not tight. I'm a relative beginner in this stuff.

John

[jaslake]

Mike:

While you are thinking about my issue (if you are thinking about it , can one use "wildcards" in macros? For example, I've written change events for each textbox in the project. Can one use "wildcards" to reference textboxes, depending on where the cursor is so as to use the same change event with a "wildcard" reference?

Know what I'm asking?

John

[jaslake]

I realized that I sent you an unprotecetd copy the the worksheet. If you do indeed run the sheet, protect it first so you simulate the working environment. Also, I have an auto-save macro running in the workbook.

Thanks John

[mikerickson]

I haven't had a chance to look at your workbook, but I notice that the you are using color1 as the name of the argument of NoEntryIn. NoEntryIn was designed to take any textbox as its argument.

[jaslake]

I'll take a look at that. In the meantime, I recoded Tag1 input box as follows:

Please Login or Register  to view this content.

and it traps the fact that color1 box is empty.

I will look at coding you provided and see if I can figure out what it's doing. I'd like to learn how to use the BeforeUpdate feature.

Thanks for your help.

John