Open Sheet

[Guest]

Excel 2000
I want the user to click on a down arrow in a cell and open a second sheet.
Then the total from a cell in the second sheet would populate the cell in the
first sheet with the down arrow. I added a combo box to the first sheet and
used the "Linked Cell" and "ListFillRange" properties. That works, but how do
I open the second sheet by clicking on the down arrow?

Thanks,

Howard

 

[Guest]

What exactly are you selecting from the combo box. If you are selecting the
sheet name then here is some code for you...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Address = "$B$2" Then Sheets(Target.Value).Select

Exit Sub
ErrorHandler:
MsgBox "Sheet " & Target.Value & " does not exist. Please Try again",
vbCritical, "Sheet Error"
End Sub

It assumes that you are selecting the sheet name and that the combo box is
in Cell B2. You add this code by right clicking on the tab you want and
selecting view code.

HTH

 

[Guest]

Jim,
Thanks!
Someone told me it's possible to put a combo box on a sheet (sheet 1), click
on the down arrow of the combo box, which would open sheet 2.
I think I can do what I want to do using a command button.
Howard