Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Sheet By Macro
Emma,
Place this macro in the worksheet module for sheet Input. This will create a list in column A of all your worksheets. Private Sub Worksheet_Activate() Application.EnableEvents = False Dim x As Long For x = 1 To ActiveWorkbook.Worksheets.Count Cells(x, 1) = Sheets(x).Name Next Application.EnableEvents = True End Sub From the Insert menu, Insert name: wsh =OFFSET(Input!R2C1,0,0,COUNTA(Input!C1)-1,1) (this assumes that Input is the first sheet) Set the Rowsourch for the ComboBox wsh In the code module for the combobox place this macro: Private Sub cboChangeSheet_Click() Application.EnableEvents = False Dim ws As String On Error Resume Next ws = cboChangeSheet.Value ' or you could use ' ws = Sheets("Inut").Range("D2") Sheets(ws).Select Application.EnableEvents = True End Sub This works for me... steve "Emma Hope" wrote in message ... I have a workbook with lots & lots of sheets, i want to put a combo box on the first sheet and when the user selects the name of the sheet from the combox box, it automatically takes them to that sheet. Additionally if possible, i would like the list of sheet names to be generated automatically and this to be updated in the combo box list range also automatically. My first sheet is called Input, all the others are 10 digit numbers. My combo box is called cboChangeSheet and links to cell D2 & the list of sheet names is currently (hand typed) in cells H1:H70. Hope you can help. Emma |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro change sheet tab color and name | Excel Discussion (Misc queries) | |||
change in colunms of sheet- and macro ? | Excel Discussion (Misc queries) | |||
Why doesnt it change sheet in the macro | Excel Worksheet Functions | |||
change sheet name macro | Excel Worksheet Functions | |||
Change Sheet By Macro | Excel Programming |