Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Auto Click a Tab button

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

809 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