Run Tab Macro

[Guest]

How do you run a macro created in a worksheet tab (right click on tab)

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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?