VBA: Run Macro When Sheet Changes

B

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
I currently have code that runs when it's selected however, i would like for the code to run once the sheet is changed.

e.g. when the activesheet is Sheet(1) i would like for it to run once the tab for Sheet(2) is pressed. . . as of now. . the code runs once the sheet is selected. e.g. Sheet(1) is the active sheet when Sheet(2) is clicked it runs on sheet(2).

code:
Code: 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim aSh As Worksheet
Dim aShRange As Range
Set aSh = ActiveSheet

Set aShRange = aSh.Range("C3")

If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then

    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    aSh.Name = aShRange.Value
    End If
End If


End Sub


As always thanks

Cheers!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Maybe

Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim aSh As Worksheet
Dim aShRange As Range
Set aSh = ActiveSheet

Set aShRange = aSh.Range("C3")

If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then

    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    aSh.Name = aShRange.Value
    End If
End If

End Sub
 
Upvote 0
VoG said:
Maybe

Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim aSh As Worksheet
Dim aShRange As Range
Set aSh = ActiveSheet

Set aShRange = aSh.Range("C3")

If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then

    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    aSh.Name = aShRange.Value
    End If
End If

End Sub
Click to expand...

That what i originally tried Vog. however, i have the same results. I sheet(1) is the active sheet and i click sheet(2) nothing happens but i click sheet(1) and it makes the change. . .


ideally i want it to make the change immediately following the click?
 
Upvote 0
Does this work?

Code: 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim aShRange As Range

Set aShRange = Sh.Range("C3")

If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then

    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    aSh.Name = aShRange.Value
    End If
End If


End Sub
 
Upvote 0
VoG said:
Does this work?

Code: 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim aShRange As Range

Set aShRange = Sh.Range("C3")

If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then

    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    aSh.Name = aShRange.Value
    End If
End If


End Sub
Click to expand...


this changed the name of the sheet after the active sheet?!?!?!?!? it had the right idea. but the active sheet should get it's name from the value in C3. When the file was opened Sheet(1) was the first sheet. When i clicked Sheet(2) it changed the name of the Sheet(2) to the value of C3 on Sheet(1)?
 
Upvote 0
I would like. whenever a user uses a sheet and switches sheets that the previous sheet checks to see if the value of C3 is blank and if so uses the index number as the name. If it is not blank then i would like for the value in c3 to be the name of that sheet.

E.g. references the above example. When the workbook is opened the user will be on sheet(1). when the user clicks Sheet(2) I want the macro to run for sheet(1) to see if there is a value in C3. if so name sheet(1) the value. If not name sheet(1) by it's index.


i hope that gives you a better idea. and as always thanks for your help.
 
Upvote 0
VoG said:
In that case I don't understand why this http://www.mrexcel.com/forum/showpost.php?p=2867215&postcount=4 did not work.
Click to expand...

When I pasted the code you provided. it did the following

my activesheet = Sheet(1)


When i clicked Sheet(2) the value in "C3" from Sheet(1) was applied to the name of Sheet(2).

What i'm trying to accomplish is the following:

My activesheet=Sheet(1)

when i click Sheet(2) the value in "C3" from Sheet(1) should be applied to the name of Sheet(1).

Not Sheet(2)

that's my issue. The value is being a applied to the sheet that i'm clicking instead of the one that i'm leaving.
 
Upvote 0
Try

Code: 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim aShRange As Range
Set aShRange = Sh.Range("C3")
If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then
    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    Sh.Name = aShRange.Value
    End If
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Speeding up VBA
Replies
9
Views
243
jvoss
J
S
  • Question Question
Run Time Error 5 - In converting PDF sheets in a workbook into individual PDFs
Replies
4
Views
68
Micron
M
D
  • Solved
How to sheet index in calculate event
Replies
3
Views
64
dmt32
D
I
  • Question Question
Value not shown in ComboBox1 when code run
Replies
1
Views
57
ipbr21054
I
A
  • Question Question
Rename sheets by cell value in date format
Replies
1
Views
184
Kaper
Kaper

Forum statistics

Threads
1,224,517
Messages
6,179,233
Members
452,898
Latest member
Capolavoro009

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top