Programmatically allowing user to select worksheet

[Barb Reinhardt]

I'd like to instruct the user to select a worksheet to work on and then I'll
identify it as WS1 or WS2. How would I do this?

Thanks,
Barb Reinhardt

 

[Jim Thomlinson]

How about something like this. Place a ComboBox (from the control toolbox) on
a sheet and add the following code... You will probably want to clean it up a
bit but it allows the user to select a sheet and you can then capture the
sheet name...

Public wksSelected As Worksheet

Private Sub ComboBox1_Change()
If ComboBox1.Text <> "" Then
Set wksSelected = Sheets(ComboBox1.Text)
wksSelected.Select
End If
End Sub

Private Sub ComboBox1_GotFocus()
Dim wks As Worksheet

With ComboBox1
.Clear
For Each wks In Worksheets
If wks.Name <> Me.Name Then .AddItem wks.Name
Next wks
End With
End Sub

 

[Bob Phillips]

Dim rng As Range

Set rng = Application.InputBox("Select a range (any range) on the target
sheet?", Type:=8)
If Not rng Is Nothing Then

Set WS1 = rng.Parent
End If

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)