Change Sheet By Macro
 

Private Sub cboChangeSheet_Click()
Dim sh as Worksheet
On Error Resume Next
set sh = thisWorkbook.worksheets(cboChangeSheet.Value)
On Error goto 0
if not sh is nothing then
Sh.Activate
sh.Range("A1").Select
End if
End sub

You can also populate the comoboxbox with the activate event in the same
module.

Private Sub Worksheet_Activate()
Dim sh as Worksheet
cboChangeSheet.clear
for each sh in thisworkbook.Worksheets
if Ucase(sh.name) < "INPUT" then
cboChangeSheet.additem sh.name
end if
Next
End sub

Might need to run similar code in Workbook_Open to get the initial list.

Regards,
Tom Ogilvy


"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