Novice q: allow user to select worksheet in middle of macro

[canary2211]

This should be easy but I am stuck and would welcome some help or
clues.

I have a macro which does various processes. In the middle of the
macro , I need to pause, ask the user to select a particular worksheet
from a list of 20. The macro continues, pasting a range to the
selected worksheet.

At present I simply do this using a userform and the user types in the
worksheet name from the list of 20 but this is prone to user typing
error. I would prefer a drop down list, ideally showing only the
remaining choices (so that after 18 iterations, there are only the
last two sheet names left).

Here is what I have at present:
Worksheets("Index").Activate
SelectAnswer = InputBox("Type in - carefully! the worksheet name from
this list.")
On Error Resume Next
Worksheets(SelectAnswer).Activate
Range("A1").Select
ActiveSheet.Paste

 

[Wouter HM]

Hi canary2211.

In Excel 2003 I have first made an userform with a combobox and a
commandbutton
The form is named frmCanary2211
The combobox is named "cboSheets"
The commandbutton is named "cmdProces"

In the form this code
' Start on code

Option Explicit

Private Sub cmdProces_Click()
Dim strName As String
Dim intIndex As Integer

strName = cboSheets.Text
intIndex = cboSheets.ListIndex

Worksheets(strName).Activate
Range("A1").Select
ActiveSheet.Paste

If cboSheets.ListCount > 1 Then
cboSheets.RemoveItem intIndex
cboSheets.ListIndex = 0
Else
MsgBox "Al sheets done"
Me.Hide
End If

End Sub

Private Sub UserForm_Activate()
Dim shtLoop As Worksheet
Dim intLoop As Integer

intLoop = 0
For Each shtLoop In ThisWorkbook.Worksheets
If shtLoop.Name <> "Index" Then
intLoop = intLoop + 1
Me.cboSheets.AddItem shtLoop.Name
End If
Next
End Sub

' end of code

Second I created a small starter macro:

Sub canary2211()
Selection.Copy

frmCabary2211.Show
End Sub

To use it, just select the cells to be copied and hit [Alt][F8].
select canary2211 and the form will be shown.

HTH,

Wouter

 

[Barb Reinhardt]

Have you thought about adding a combo box to your user form and
pre-populating that with the worksheet names?

 

[canary2211]

Barb, Wouter

Thanks for this - both methods work nicely. Now I just need to work
out how to eliminate the sheets (or perhaps grey out the options) that
are dealt with so that usr can only select from the remaining sheets

Nick