Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Trigger Macro with Worksheet Tab selection

This is a discussion on Trigger Macro with Worksheet Tab selection within the Excel Questions forums, part of the Question Forums category; I have a macro I'd like to run when a worksheet tab is selected. The macro updates content in various ...

  1. #1
    Board Regular
    Join Date
    Dec 2003
    Posts
    136

    Default Trigger Macro with Worksheet Tab selection

    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..!!

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,011

    Default Re: Trigger Macro with Worksheet Tab selection

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2003
    Posts
    136

    Default Re: Trigger Macro with Worksheet Tab selection

    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

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,011

    Default Re: Trigger Macro with Worksheet Tab selection

    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.

  5. #5
    Board Regular
    Join Date
    Dec 2003
    Posts
    136

    Default Re: Trigger Macro with Worksheet Tab selection

    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

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,011

    Default Re: Trigger Macro with Worksheet Tab selection

    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.

  7. #7
    Board Regular
    Join Date
    Dec 2003
    Posts
    136

    Default Re: Trigger Macro with Worksheet Tab selection

    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

  8. #8
    New Member
    Join Date
    Jul 2008
    Posts
    5

    Post Re: Trigger Macro with Worksheet Tab selection

    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.

  9. #9
    Board Regular cornflakegirl's Avatar
    Join Date
    Nov 2004
    Location
    UK
    Posts
    2,021

    Default Re: Trigger Macro with Worksheet Tab selection

    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.
    Emma

    Your apparent nonchalance belies the fact that you can only think of me.

  10. #10
    New Member
    Join Date
    Jul 2008
    Posts
    5

    Post Re: Trigger Macro with Worksheet Tab selection

    Thanks for the welcome.

    Actually what I want is quite similar to this thread.

    But Still how to create new thread over here?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com