Creating frames inside excel

  • Thread starter Thread starter Arska
  • Start date Start date
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
 
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
 
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
 
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
 
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...
 
Back
Top