VBA triggers on tabkey

[jt]

I have this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Cells(3, ActiveCell.Column).Value = "NAME" Then
n = Target.Row
If Cells(n, ActiveCell.Column).Value = "TOOLING" Then
Excel.Range("A" & n).Value = "T"
Target.Offset(, 1).Select
Target.Offset(, 3).FormulaR1C1 = "=IF(RC[-2]=""TOOL
LAYOUT"",""N/A"","""")"
Target.Offset(, 4).FormulaR1C1 = "=IF(RC[-3]=""TOOL
LAYOUT"",""N/A"","""")"
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="CUTTER PATH (REFERENCE ONLY),TOOL
LAYOUT"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End If
If Cells(3, ActiveCell.Column).Value = "FEATURE NO." & Chr(10) & "/
TOOL ASSEMBLY" Then
n = Target.Row
If Cells(n, ActiveCell.Column).Value = "TOOL ASSEMBLY" Then
Excel.Range("A" & n).Value = "T"
Target.Offset(, 1).Value = "TOOLING"
Target.Offset(, 2).Select
Target.Offset(, 4).Value = "N/A"
Target.Offset(, 5).Value = "N/A"
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween,
Formula1:="CUTTER,DRILL,BORINGBAR,REAMER,TAP,GAUGE,BRUSH"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub

works great if I hit enter but is it possible to trigger it using the
tabkey also?

 

[GS]

The code in Worksheet_Change fires when you exit the cell. Using the
tab key exits the cell if your entry was done by just typing without
entering 'Edit Mode' by menu, F2, or double-clicking the cell.

 

[GS]

GS presented the following explanation :

The code in Worksheet_Change fires when you exit the cell. Using the tab key
exits the cell if your entry was done by just typing without entering 'Edit
Mode' by menu, F2, or double-clicking the cell.

Correction: the Tab key exits whether in 'Edit Mode' or not.

 

[jt]

GS presented the following explanation :


Correction: the Tab key exits whether in 'Edit Mode' or not.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

but it doesn't fire when I hit the tab key?

 

[GS]

jt formulated on Monday :

but it doesn't fire when I hit the tab key?

It does when I hit the Tab key (if the contents were edited).

 

[Gord Dibben]

You have a sheet_change event.

Something has to be changed or edited in a target cell.

Are you typing in the cell then hitting the Tab Key to exit?

Should fire your event.

If you don't type or edit something in the cell Tabbing out will fire nothing.

Hitting ENTER key will not fire your event if you do not change or edit the cell
contents.

D-click then Tab or ENTER will fire the event.

F2 then Tab or ENTER will fire the event.

Picking from a Dropdown list will fire the event.


Gord Dibben MS Excel MVP

 

[jt]

You have a sheet_change event.

Something has to be changed or edited in a target cell.

Are you typing in the cell then hitting the Tab Key to exit?

Should fire your event.

If you don't type or edit something in the cell Tabbing out will fire nothing.

Hitting ENTER key will not fire your event if you do not change or edit the cell
contents.

D-click then Tab or ENTER will fire the event.

F2 then Tab or ENTER will fire the event.

Picking from a Dropdown list will fire the event.

Gord Dibben     MS Excel MVP





- Show quoted text -

I am typing in the cell then hitting the tab key and the event does
not fire, but i guess i will have to just hit enter all the time,
thanks for your help

 

[Clif McIrvin]

You have a sheet_change event.

Something has to be changed or edited in a target cell.

Are you typing in the cell then hitting the Tab Key to exit?

Should fire your event.

If you don't type or edit something in the cell Tabbing out will fire
nothing.

Hitting ENTER key will not fire your event if you do not change or
edit the cell
contents.

D-click then Tab or ENTER will fire the event.

F2 then Tab or ENTER will fire the event.

Picking from a Dropdown list will fire the event.

Gord Dibben MS Excel MVP





- Show quoted text -

I am typing in the cell then hitting the tab key and the event does
not fire, but i guess i will have to just hit enter all the time,
thanks for your help


-------

Is there something like

Application.OnKey "{TAB}", "TabProc"

anywhere? It wouldn't even have to be in the workbook you are working
on ... if any code traps a keystroke that trap overrides the normal
behavior until code turns the trap off.

Do you have any addins installed?

 

[jt]

I am typing in the cell then hitting the tab key and the event does
not fire, but i guess i will have to just hit enter all the time,
thanks for your help

-------

Is there something like

   Application.OnKey "{TAB}", "TabProc"

anywhere?  It wouldn't even have to be in the workbook you are working
on ... if any code traps a keystroke that trap overrides the normal
behavior until code turns the trap off.

Do you have any addins installed?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket - Hide quoted text -

- Show quoted text -

not that I'm aware of...