Creating frames inside excel

A

Arska

Hi

1.
I would like to keep a navigation bar, visible on all worksheets, a bit
like frames on a web page. Is there anyway of doing this in excel?


2.
Another issue. I have a macro (code below) which creates a dropdown list
of worksheets, by clicking I can access directly that sheet, however if
I want this on every sheet, I must make a new macro for each combobox.
Is there anyway I can using the same combobox for all sheets?



Private Sub cboTheList_Click()

Worksheets(cboTheList.Text).Activate

End Sub



Private Sub Worksheet_Activate()

Dim ws As Worksheet

cboTheList.Clear

For Each ws In Worksheets

cboTheList.AddItem ws.Name

Next ws

End Sub


Cheers

Arska
 
A

Andy Brown

Why not try the Name Box, left of the formula bar? ; eg:

Select Sheet1!A1, click in the Name Box, type Sheet1, press Enter.

Then from any other sheet you can select this from the box to jump to
Sheet1!A1.

Rgds,
Andy
 
A

Arska

Yeah that's one idea, but I would like to include information e.g. year
under evaluation and region selected ... also if I have manco assigned
to the menu I need to duplicate it for each sheet

-Arska
 
A

Andy Brown

also if I have manco assigned to the menu

I didn't mention a menu, perhaps we're not on the same page (no pun
intended).
I need to duplicate it for each sheet
Click to expand...

Not so -- you could try something on these lines.

First sheet = "Menu". Add a dropdown from the Forms toolbar, place it over
B1:C2. Link it to Menu!$A$1. Input Range = Menu!$A$4:$A$6. In A4:A6, enter
Menu, Sheet1, Sheet2 respectively. In A2, this formula -

=INDEX($A$4:$A$6,A1,0).

Assign this code to the dropdown -

Sub Menu()
n = Range("Menu!A2")
Sheets(n).Select
End Sub

Hide ColumnA. Insert a new sheet "Sheet1". Copy the dropdown to Sheet1
(position over A1:B2). Select C1 and Freeze Panes. Create a copy of Sheet1,
rename as Sheet2. You now have a dropdown that jumps to the sheet (name)
selected.

Alternatively, just use HyperLinks, then you've no code at all.

Rgds,
Andy
 
A

Arska

Thanks

This worked like a charm :)

-Arska

Andy said:
I didn't mention a menu, perhaps we're not on the same page (no pun
intended).




Not so -- you could try something on these lines.

First sheet = "Menu". Add a dropdown from the Forms toolbar, place it over
B1:C2. Link it to Menu!$A$1. Input Range = Menu!$A$4:$A$6. In A4:A6, enter
Menu, Sheet1, Sheet2 respectively. In A2, this formula -

=INDEX($A$4:$A$6,A1,0).

Assign this code to the dropdown -

Sub Menu()
n = Range("Menu!A2")
Sheets(n).Select
End Sub

Hide ColumnA. Insert a new sheet "Sheet1". Copy the dropdown to Sheet1
(position over A1:B2). Select C1 and Freeze Panes. Create a copy of Sheet1,
rename as Sheet2. You now have a dropdown that jumps to the sheet (name)
selected.

Alternatively, just use HyperLinks, then you've no code at all.

Rgds,
Andy
Click to expand...
 
Top