How to detect if TAB key has been pressed?

[bgreeson]

Hi guys,

I'm trying to write a code that detects when the tab key has been pressed, and if it has, send an email, if it hasn't, kick you out and reset the cell. All of this is in Worksheet Change
I have everything working except the tab key detection and could use some help on that.

Please Login or Register  to view this content.

Thanks!

[jaslake]

Hi bgreeson

Play with this...

In the Sheet Module

Please Login or Register  to view this content.

In a General Module

Please Login or Register  to view this content.

[bgreeson]

John,

Works fantastically! No further edits were needed Thanks so much for the help!

Brad

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.

[bgreeson]

If I want to do this for multiple UserForms, how would I do that? For instance:

In Worksheet:

Please Login or Register  to view this content.

In General Module (all one module):

Please Login or Register  to view this content.

When I do that, it will bring up the wrong UserForms

[jaslake]

Hi Brad

First go to the Immediate Window in VBA...type this Application.OnKey "{TAB}", press enter.

Then try this Code

Please Login or Register  to view this content.

[bgreeson]

I get "Compile Error: Invalid use of property" and it highlights "Call DDA". My thoughts are that I need to use DDA.show rather than a call, but I could be wrong.

I also tried to add multiple Modules, each with its own version of the first solution above, but that didn't work either.

[jaslake]

Hi Brad

Try the Code in the attached...

[bgreeson]

No luck. There's some more code that might be interfering. I'll attach the file here. The woman I am making this for isn't the best at excel. I'm trying to make it bulletproof so that the only way you can bring the UserForms up is by putting a date in the desired cells, and then pressing TAB (because having both ENTER and TAB "makes it too complex". The UserForm then send out an automated email using Outlook, for whomever's computer this is on, to the desired recipients. I've deleted the proprietary info.

Architectural & Estimating Status Master (1) - Copy.xlsm

[jaslake]

Hi Bra

So I understand...you want the UserForm to appear ONLY IF the TAB key is pressed?

[bgreeson]

Correct. I want to be able to enter a date (which is really just a number) into one of the 5 columns and have the corresponding UserForms "pop-up"/show only when TAB is pressed. If ENTER or clicked-out, do nothing. TAB should still be able to be pressed like normal in other cells.

[jaslake]

Hi Brad

Let me look at it a bit further...no guarentees...

[jaslake]

Hi Brad

I've adapted some Code from here to capture the Enter Key Press.
http://www.experts-exchange.com/Soft..._27118905.html

I will caution you, if you're going to modify the Code either run this procedure in Module 3

Please Login or Register  to view this content.

or select one of the Information Worksheets before entering VBA as the above Code is triggered upon Deactivation of the Status Worksheets.

I don't pretend to understand what the Hook Keyboard Code is doing except to say the Code is monitoring all the Key Presses and if the Enter Key is pressed and the Target Cell is in one of the Five Columns, the User will get a message to use the TAB key.

[bgreeson]

Sorry for the delayed reply. Some unexpected life events happened for me.

This is pretty complex. I'll look into it and try to figure it all out. Thanks for all the help, I really appreciate it it might be a week or two before I confirm or "deconfirm" anything