Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Sheet
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 "Howard" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Sheet
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 "Jim Thomlinson" wrote: 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 "Howard" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ON OPEN VBA Code input incorrectly now excel sheet wont open | New Users to Excel | |||
Why does a Multi-sheet/user file open on sheet two every time? | Excel Discussion (Misc queries) | |||
why, when i open a work sheet does a blank sheet open as well | Excel Discussion (Misc queries) | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |