Calling a worksheet with an inputbox

H

herman

Hello,
I am writing a vba code where I want to copy some range "RAN" (well
specified)from worksheet "WSname" (name not yet known) in workbook
"WBook" to a specific worksheet in another workbook.
I want to do this by clicking my way to "WSname".

So I started out like this :

Dim fileToOpen$
ChDir "C:\"
fileToOpen = Application.GetOpenFilename("xls files (*.xls), *.xls")
If fileToOpen <> "" Then
Workbooks.Open Filename:=fileToOpen
End If

This works fine but now I want to get to this sheet "WSname" by just
activating it or clicking its name tab (i.e. without typing the name
of the sheet). Should I work with an inputbox here and if so, what is
the proper code?
Thank you very much for your help.
Herman
 
J

Jim Rech

(i.e. without typing the name of the sheet).

But an input box would require you to type the name(?)

If you'd like a popup list of the visible sheets you might try this code:


Sub ShowWorksheetsPopup()
With CommandBars(28)
.Controls(.Controls.Count).Execute
If Err <> 0 Then .ShowPopup
End With
End Sub
 
Top