Solved

Auto Click a Tab button

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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 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…

776 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