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

[Cosmos75]

Is there a macro that choose which worksheet to view? Maybe a combo box on each page? BUT I need those combo boxes to appear automatically on each worksheet and be popoulated/updated with the worksheets automatically since this workbook is updated continually.

And YES, I know you can use the tabs at the bottom, but that can be cumbersome with a lot of worksheets.

Thanks!

 

[Al Chara]

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

 

[Al Chara]

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.

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

 

[Ricky Morris]

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.

 

[Al Chara]

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

 

[Damon Ostrander]

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

 

[Cosmos75]

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?

 

[Al Chara]

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.

 

[mvivian]

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?

 

[Damon Ostrander]

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

 

[Damon Ostrander]

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