Auto Click a Tab button

Hello is there anyway to set up a macro the activate a macro when the tab button is pressed on either of these cells z26,z28,z30,z32,z34,z36,z38,z40,z42.  Thanks
stormhunter2000Asked:
Who is Participating?
 
bruintjeCommented:
Hi stormhuner2000,

The closest you can get to this in Excel is with the selectionchange event

-open the VB Editor with ALT+F11
-then goto the icon upper left sheet1
-open it and paste this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Select Case Target.Offset(0, -1).Address
  Case "$Z$26", "$Z$28", "$Z$30", "$Z$32", "$Z$34", "$Z$36", "$Z$38", "$Z$40", "$Z$42"
    MsgBox "your changing the cell"
  End Select
End Sub

-this will work on the tab out of one of the cells you mentioned above
-instead of teh message you can call your macro

HTH:O)Bruintje
0
 
bruintjeCommented:
Hi stormhunter2000, what are you trying to do here?

i presume excel?

here are two subs

a.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  MsgBox "Other sheet selected"
End Sub

b.
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Rem declare vars
Dim i As Integer
i = 26
  Rem select case to look for the address of current clicked cell
  While Not i > 42
    If InStr(1, Target.Address, "$Z$", vbTextCompare) Then
      If Target.Address = "$Z$" & i Then
        Rem selected cell in range so show message
        MsgBox "Cell within Range selected"
      End If
    Else
      Rem cell is not in column so exit early
      Exit Sub
    End If
    i = i + 2
  Wend
End Sub

a> will show a message box but you can easily add your macro to the handler

b> will show a message box if the cell in your range above is rightclicked

but i guess you need something different could you please explain it better?

:O)Bruintje
0
 
stormhunter2000Author Commented:
Hello, Yes the user enter data in each cell,so when the user gets to cell z26 he will enter his data then when he/she hits the tab button again. It will run a create macro that i have setup
0
 
stormhunter2000Author Commented:
Good, that is what i needed thank you for the script
0
 
bruintjeCommented:
glad i could help, thanks for the grade
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.