Trigger Macro with Worksheet Tab selection

torweb

Board Regular
Joined
Dec 1, 2003
Messages
136
I have a macro I'd like to run when a worksheet tab is selected. The macro updates content in various cells. That action is now triggered with a command button (linked to macro), but I'd like it to take place when the user selects that worksheet tab....

Thank you for any help...pointers..!!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sounds like all you need is to place a workshet change event in your sheet module:

Private Sub Worksheet_Activate()

'Your code goes here

End Sub



To access your sheet module, right click on your sheet tab, left click on View Code, and paste the procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.
 
Upvote 0
Tom,

I pasted the Private Sub Worksheet_Activate() as you explained, saved, and restarted. Upon selecting that worksheet tab, I was posted the following error;

"Run-time Error 1004" Select Method of Range Class Failed"

Additionally, I get an "Debug" error (bold type in code below). What the macro is doing, is going to another worksheet, copying data to the worksheet in question, then removing any blank rows. It does a few more things (reason you don't see the "end sub), but it doesn't seem to want to go to the other specified worksheet for starters.


Private Sub Worksheet_Activate()
'
'Begin unprotect

Application.ScreenUpdating = False

ActiveWorkbook.Unprotect Password:="mypassword"
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="mypassword"
Next ws

'Begin copy and paste routine into worksheet
Application.ScreenUpdating = False
Sheets("Daily Journal").Select
Range("A4").Select--------"Identifier under cursor not recognized"
ActiveWindow.ScrollRow = 273
Range("A4:N302").Select
Selection.Copy
Sheets("Daily Meals Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Begin Cell Zero Out Routine
For Each cell In Range("A2:A302")
If cell.Value = 0 Then
cell.Value = ""
End If
Next

'Begin Row Delete Routine

[A:A].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Upvote 0
When you said in your first post "The macro updates content in various cells.", I assumed you were only manipulating cells on the worksheet you were activating. Sheet change events do not mix well with procedures that involve selecting or activating ranges on other sheets.

Restore your original macro and place it in a standard module, which was probably where it was to begin with. Let's say your macro is called "Macro1". Change your sheet activation procedure to be:

Private Sub Worksheet_Activate()
Macro1
Application.EnableEvents = False
Sheets("YourSheetName").Activate
Application.EnableEvents = True
End Sub

This way, the activation of the sheet of interest will run the procedure, and you will be put back onto that sheet when it's complete.

Keep in mind, you rarely need to select or activate ranges or sheets, but instead of rewriting your macro now, try the above suggestion first to see if we are on track.
 
Upvote 0
Oh Boy...didn't want this to become a burden. Now I get a 'Run-time Error 28 - Out of Stack Space'

The Debug Error is'Run-Time Error 1004 - PasteSpecial Method of Range Failed' with the following section of the macro selected.

Sub Mealreport()
'
' Mealreport Macro
' Macro recorded 12/24/2003 by Administrator

'Begin unprotect

Application.ScreenUpdating = False

ActiveWorkbook.Unprotect Password:="my password
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="my password
Next ws

'Begin copy and paste routine into worksheet
Application.ScreenUpdating = False
Sheets("Daily Journal").Select
Range("A4").Select
ActiveWindow.ScrollRow = 273
Range("A4:N302").Select
Selection.Copy
Sheets("Daily Meals Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Begin Cell Zero Out Routine
For Each cell In Range("A2:A302")
If cell.Value = 0 Then
cell.Value = ""
End If
Next

'Begin Row Delete Routine

[A:A].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Upvote 0
You are doing a lot of unnecessary looping, and your posted code can be boiled down to this if I understand it correctly:

Sub Mealreport()
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="my password"
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="my password"
Next ws
With Sheets("Daily Meals Report")
.Range("A2:N300").Value = Sheets("Daily Journal").Range("A4:N302").Value
With .Range("A2:A300")
.Replace What:="0", Replacement:="", LookAt:=xlWhole
.SpecialCells(xlBlanks).EntireRow.Delete
End With
End With

Then finish with whatever you have not posted, but re-do it based on the above principle of replacing zeros with blanks and deleting them all at once instead of all that looping.
 
Upvote 0
Z-O-W-I-E!!! Thanks for the fantastic revision!! I think it's time to invest in the 'Power User Bundle."

Thanks again!!! It works great
 
Upvote 0
Hello,

I need to create multiple excel workbooks depending on the Data in one sheet. For e.g a list of animals.

The new workbooks should be created on basis of the animal names with certain format. For e.g the workbook should have columns labelling ...

height,length,speciality etc.

Plz guide me regarding this.
 
Upvote 0
Suhas - welcome to the board.

You'll probably have better luck with an answer if you start a new thread with a title that accurately describes your problem. Then describe what you want to do with plenty of detail - posting an extract of your data (eg with Excel Jeanie) is also helpful.
 
Upvote 0
Thanks for the welcome.

Actually what I want is quite similar to this thread.

But Still how to create new thread over here?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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