Solved

Auto Click a Tab button

Posted on 2002-03-29
5
201 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vb.net 2008 2 56
VB error "Type mismatch" 2 49
Put text in a picture ASP.NET C# 2 50
Help me. 3 46
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

910 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

16 Experts available now in Live!

Get 1:1 Help Now