Solved

Auto Click a Tab button

Posted on 2002-03-29
5
200 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:stormhunter2000
  • 3
  • 2
5 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6904789
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
 

Author Comment

by:stormhunter2000
ID: 6906797
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
 
LVL 44

Accepted Solution

by:
bruintje earned 25 total points
ID: 6910390
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
 

Author Comment

by:stormhunter2000
ID: 6957654
Good, that is what i needed thank you for the script
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6957658
glad i could help, thanks for the grade
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now