TabKey to CommandBox

[GColeman]

I'm sure its in one of these threads somewhere, but I couldn't find a working answer. Here's what I got...

Active cell is G8. User will enter a name and press "Tab". Target Cell is A10. But I want it to automatically select a commandBox (Cmnd1) or open a userform (PRForm1) instead of selecting A10. I've tried a couple ideas listed here, but they have me using selectionchange and creating a macro. And I'm not sure how to activate that macro either with "Tab" from G8, or with target address.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "($A$10)" Then
PRForm1.Show
End If

End Sub

 

[My Aswer Is This]

You said:
Active cell is G8. User will enter a name and press "Tab". Target Cell is A10. But I want it to automatically select a commandBox (Cmnd1) or open a userform (PRForm1) instead of selecting A10

So why do you say active cell is G8 but target cell is A10

And user will enter a name I guess that means any value and you want that action to open a UserForm and you want this to happen without using a Macro. Well this is impossible without using Vba Macro.

You cannot do things like this with just formulas.

 

[AkaTrouble]

I thinkMy Aswer Is This misunderstood that you simply cant use a macro or sheet change event only because you do not know howq rathr than not wanting to

it is true the only way to achieve what you want would be use of VBA / Macro the simplest of which would be a onchange event in the sheet code

there would be a couple of questions need answering

you say pushing tab what if user enters name and pushes enter / return or clicks another cell.

i would suggest that you opt for an on change event that if anything is enter in cell it triggers your userform / macro to run you do not need to worry about the target cell as you can return to that cell on closing your userform if so required

 

[GColeman]

Not against using VBA at all! I have several user forms and modules throughout the spreadsheet. But how do I attach the code or macro to a cell, and not to an object, shape, etc? If it is an event change, I get that it would go to the sheet code

Active cell is G8. This is the last cell for the user to directly input information. When that information is completed, I want the next action to be filling out a userform. I don't care if it is "Tab" or "Enter" triggering the event change. If it can be either, all the better. The user will not be selecting another cell, as the remainder of the cells are locked and data entry is restricted to userform (It unprotects sheet at the beginning of the process, and protects it again at the end). A10 is active, only because I was trying to use Target. The command box is covering A10. In a nutshell, in need automatically activate the userform after the last manual entry is completed. I'm not familiar with "on change".
Does that help clarify?

 

[My Aswer Is This]

Try this: Change Userform name to meet your needs:
Now when you enter a value into Rage("G8") the Userform will open.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G8")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
UserForm1.Show
End If
End Sub

 

[GColeman]

Seems to work perfectly, thank you! I forgot that the user does have the option of NOT entering text in G8, and could simply leave it blank. But I simply added "None" in that cell, then changed the
Range("G8").Select
Selection.ClearContents
to
Selection = "None"
in the "Clear Sheet" macro, which works just as well in this case

Thanks again for the quick help!

 

[My Aswer Is This]

I'm glad you have things working for you. Not sure I understand. You said:
"Active cell is G8. This is the last cell for the user to directly input information. When that information is completed, I want the next action to be filling out a userform.

A sheet event change script needs to know when to activate. And you said when Range("G8") value changes.

 

[GColeman]

I'm not sure, and it only worked a couple times.... Now it only works if I type in it again, and not when I use code to fill the cell.

 

[My Aswer Is This]

Well you need to explain again what you want. You peviously said when the user enters a value in "G8" Then open the Userform.

I'm not sure, and it only worked a couple times.... Now it only works if I type in it again, and not when I use code to fill the cell.

 

[GColeman]

I apologize for that. Your help has been righ on point thus far.
I assumed the user would be entering text in this field. But there is going to be the rare occasion where they may not. Your solution works great, except for this one scenario I didn't take interesting consideration.
I tried to use data validation in that cell to force the user to input SOMETHING. even just a "0". I unchecked the Ignore Empty box. But the user can just Tab through the cell. It opens the information box when the cell is highlighted, but doesn't execute the "warning" unless the user actually selects the cell manually.
Is there something other than a change event I can use to trigger the userbox?