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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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.
 

torweb

Board Regular
Joined
Dec 1, 2003
Messages
136
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
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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.
 

torweb

Board Regular
Joined
Dec 1, 2003
Messages
136

ADVERTISEMENT

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
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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.
 

torweb

Board Regular
Joined
Dec 1, 2003
Messages
136

ADVERTISEMENT

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
 

Suhas

New Member
Joined
Jul 8, 2008
Messages
5
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.
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
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.
 

Suhas

New Member
Joined
Jul 8, 2008
Messages
5
Thanks for the welcome.

Actually what I want is quite similar to this thread.

But Still how to create new thread over here?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,545
Members
417,151
Latest member
ChickenTenderer

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
Top