Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Tab Macro
How do you run a macro created in a worksheet tab (right click on tab)
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Tab Macro
That depends on what it's called (its first line). If it doesn't have
'Private' in front of it then you can run it by going into the dropdown menus: Tools|Macro|Macros.., and it should appear in the list with the sheet name in front of it eg. 'Sheets1.MyMacro'. Just select it and click Run. If it's got the word 'Private' in front of it you won't see it in the list above, but you can still type it in, using the sheet name as prefix, and click Run. If its name is akin to some of these: Private Sub Worksheet_Activate() Private Sub Worksheet_Change(ByVal Target As Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) and quite a few more, then it is triggered (run) by an event on that worksheet. In the above three examples, by the sheet being activated, by a cell or cells being changed and by the selected cell/s changing to other selected cell/s. I'm not sure, but I don't think you'll have success running any of these event triggered macros manually. -- p45cal "Beep Beep" wrote: How do you run a macro created in a worksheet tab (right click on tab) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Tab Macro
Thanks p45cal
Here is the macro Private Sub Worksheet_change(ByVal Target As Range) Const WS_RANGE As String = "A2:A2" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Worksheets(.Row).Name = .Value On Error GoTo 0 End With End If ws_exit: Application.EnableEvents = True End Sub Are you saying it will run automatically. I changed the name in WS_RANGE, but it did not change. Thanks Frank "p45cal" wrote: That depends on what it's called (its first line). If it doesn't have 'Private' in front of it then you can run it by going into the dropdown menus: Tools|Macro|Macros.., and it should appear in the list with the sheet name in front of it eg. 'Sheets1.MyMacro'. Just select it and click Run. If it's got the word 'Private' in front of it you won't see it in the list above, but you can still type it in, using the sheet name as prefix, and click Run. If its name is akin to some of these: Private Sub Worksheet_Activate() Private Sub Worksheet_Change(ByVal Target As Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) and quite a few more, then it is triggered (run) by an event on that worksheet. In the above three examples, by the sheet being activated, by a cell or cells being changed and by the selected cell/s changing to other selected cell/s. I'm not sure, but I don't think you'll have success running any of these event triggered macros manually. -- p45cal "Beep Beep" wrote: How do you run a macro created in a worksheet tab (right click on tab) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Tab Macro
Yes, it should run automatically, make sure that application's events are
enabled. In the immediate pane type the following, then press Enter: ?application.EnableEvents If you get the answer True, then that's fine, there's something else wrong. If it returns False, then type this in the immediate pane application.EnableEvents=True and try again changing cell A2. If Events were enabled and nothing changed, you need to check that it's doing what you intended. At the moment, since it's checking the cell being changed is cell A2, to test it you need to change that cell. Change any other cell and nothing will happen. OK, let's say you do change cell A2, to say "dylan". The macro should execute, and this line: Worksheets(.Row).Name = .Value will do its stuff which is to change the name of Sheet(2) to "dylan". ou should see this on the sheet's tab. You won't see it if the sheet is hidden, or it doesn't exist. Why sheet(2)? Because .row is 2, because A2 is in row 2, and it's only when A2 is changed that anything happens. Just for clarity, because: ..row is within the lines: With Target End with it means: Target.row. (Target is the cell/range of cells being changed.) Now change Const WS_RANGE As String = "A2:A2" to Const WS_RANGE As String = "A3" (btw, it doesn't need to be a constant) Now the important part of the macro can only execute if cell A3 is changed, but this is in row 3, so Sheet(3)'s name will be changed. But is that what you intended? -- p45cal "Beep Beep" wrote: Thanks p45cal Here is the macro Private Sub Worksheet_change(ByVal Target As Range) Const WS_RANGE As String = "A2:A2" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Worksheets(.Row).Name = .Value On Error GoTo 0 End With End If ws_exit: Application.EnableEvents = True End Sub Are you saying it will run automatically. I changed the name in WS_RANGE, but it did not change. Thanks Frank |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |