Macro that choose which worksheet to view that is on ALL wor - Page 2
Macro that choose which worksheet to view that is on ALL wor
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: Macro that choose which worksheet to view that is on ALL wor

  1. #11
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Damon's solution seems better suited for your needs.

    Why don't you adapt his macro to work in the NewSheet event in ThisWorkbook:

    Private Sub Workbook_NewSheet(ByVal Sh As Object)

    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  2. #12
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cosmos,

    This is what I recommend:

    Run the AddButtons macro for existing sheets. Then add the following code to the ThisWorkbook module. It should work nicely.
    Code:
    Private Sub Workbook_NewSheet(ByVal WS As Object)
    Dim C As Range 'The cell where the button will be placed
    Dim Width As Single 'The button width
    Set C = WS.[B4] 'put button in cell B4
    If C.Width > 60 Then Width = C.Width Else Width = 60
    With WS.Buttons.Add(C.Left, C.Top, Width, C.Height)
    .OnAction = "SelectWorksheetMenu"
    .Characters.Text = "Select Sheet"
    .Characters.Font.Size = 8
    End With
    End Sub
    Damon,

    I really like "Workbook tabs". I never knew it existed and I will definitely be able to use it. Thanks.

    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-17 12:39 ]

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's another way to navigate between sheets, particulary formulas that link to other sheets or other workbook.

    Change you edit in cell option by selecting Tools > Edit from the menu bar. Click the Edit tab. Uncheck the Edit directly in Cell option.

    Now, whenever you double click on a formula the references another cell it will highlight/select that cell, even if it's on another sheet or in another workbook. This works great when you're working with linked files since it will open the source file being linked to and select the cell.


    It's never too late to learn something new.

    Ricky

  4. #14
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 12:43, Ricky Morris wrote:
    by selecting Tools > Edit from the menu bar. Click the Edit tab. Uncheck the Edit directly in Cell option.
    For my XL2000 it is Tools>Options>Edit tab...
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #15
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Cosmos,

    Okay, here is the macro that adds buttons modified to delete any old buttons off before adding new. I decided to do it this way in case you want to change the location of the button on the sheets between runs. You will need to delete all the previous buttons off the sheets before running this. It is okay if there are other buttons on the sheets--it will not affect them. It gives each button a name and this is how it can tell if this button already exists on the sheet.

    Sub AddButtons()
    Dim WS As Worksheet
    Dim C As Range 'The cell where the button will be placed
    Dim Width As Single 'The button width
    For Each WS In Worksheets
    Set C = WS.[B4] 'put button in cell B4
    If C.Width > 60 Then Width = C.Width Else Width = 60
    On Error Resume Next
    'delete button if it exists
    WS.Buttons("btnSelWS").Delete
    On Error GoTo 0
    With WS.Buttons.Add(C.Left, C.Top, Width, C.Height)
    .Name = "btnSelWS"
    .OnAction = "SelectWorksheetMenu"
    .Characters.Text = "Select Sheet"
    .Characters.Font.Size = 8
    End With
    Next WS
    End Sub
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  6. #16
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon,

    THANKS!

    Al Chara,

    Had a question, I don't know much about Events so please bear with me.

    You have this

    Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Is NewSheet a name you gave to the event like you do to a macro or is NewSheet an event built INTO excel that runs the code based off the event name?

    i.e. IF NewSheet means NewSheet added.
    THEN if newsheet is added Run VBA code in NewSheet event?

    Am I just totally lost or what?

  7. #17
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, if you are in excel, hit Alt+F11, to get to VBA. Then look to the left for the project explorer. Double click "ThisWorkbook" and it will open a blank page to the right. This is the area where you put code that will run on Workbook events. Look to the top for a dropdown box (General) and select workbook. Then in the dropdown box to the right select whichever event you want.

    In this case you need "NewSheet" Whatever code you place in the NewSheet procedure will run when a new sheet is added. Put the addbutton code that I posted earlier (adapted from Damon's) in this area and it should work automatically for you everytime you add a worksheet.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  8. #18
    New Member
    Join Date
    Apr 2002
    Location
    London England
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Question: When I run the following code :

    Sub SelectWorksheetMenu()
    CommandBars("Workbook tabs").ShowPopup
    End Sub

    I return the message

    Object Variable or With Block Variable not set.

    Any thoughts?

  9. #19
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Cosmos75,

    I thought I had posted an answer to your followup question, but now don't see it so here goes again.

    Here is an updated routine to add the buttons. You will need to delete all of the previous buttons from the worksheets before using this, but not any other buttons you might have--they will not be affected. This version names the buttons, and deletes the previous ones off each time before adding the new ones.

    Sub AddButtons()
    Dim WS As Worksheet
    Dim C As Range 'The cell where the button will be placed
    Dim Width As Single 'The button width
    For Each WS In Worksheets
    Set C = WS.[B4] 'put button in cell B4
    If C.Width > 60 Then Width = C.Width Else Width = 60
    On Error Resume Next
    'delete button if it exists
    WS.Buttons("btnSelWS").Delete
    On Error GoTo 0
    With WS.Buttons.Add(C.Left, C.Top, Width, C.Height)
    .Name = "btnSelWS"
    .OnAction = "SelectWorksheetMenu"
    .Characters.Text = "Select Sheet"
    .Characters.Font.Size = 8
    End With
    Next WS
    End Sub
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  10. #20
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    mvivian,

    In order to make the popup work from a workbook event code module, the CommandBars collection object must be qualified, so the code must be:

    Sub SelectWorksheetMenu()
    Application.CommandBars("Workbook tabs").ShowPopup
    End Sub

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

User Tag List

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