Input Box Question

[marcia2026]

Please forgive me if I have asked this before, but I searched the threads and
cannot find an answer.
I have a workbook with 31 sheets in it. Each sheet is used for a different
day during the month to enter data. I am attempting to put a macro in place
to copy unique values to separate sheets from one user defined sheet in the
workbook, but I do not know how to let the user tell the macro which sheet to
sort and copy. I think that I have the Input box set up correctly, but don't
know how to incorporate it into the code.

Here is what I have so far:

'Name of the sheet with your data
Dim strWhichSheet As String
strWhichSheet -InputBox("Enter sheet name to sort", "WhichSheet",
"WhichSheet")
If strWhichSheet = "" Then End

Set ws1 = Sheets("WhichSheet")

Then goes on to execute code to copy the unique values on one sheet to
several sheets.

 

[john]

I've cobbled this together very quickly but think it may set you off in right
direction.

Private Sub SelectSheet()
Dim strWhichSheet As Variant
Dim ws1 As Worksheet

strWhichSheet = Application.InputBox(prompt:="Enter Sheet Name To Sort" _
, Title:="Sort Worksheet",
Type:=2)
If VarType(strWhichSheet) = vbBoolean Then
If strWhichSheet = False Then
Debug.Print "user cancelled"
Exit Sub
End If
End If

If Worksheets(strWhichSheet) Is Nothing Then
msg = MsgBox("Sheet Name " & strWhichSheet & _
" Does Not Exist!", vbCritical, "Sort WorkSheet")
Else
'Do your stuff here
Set ws1 = Sheets(strWhichSheet)
MsgBox ws1.Name
End If

End Sub

 

[Kent Prokopy]

The way I would go about it is to use a UserForm with one CommandButton and
one Combobox with the following code:

'This would be code run by a button or custom Menu Item.
Private Sub Main()
UserForm1.Show
End Sub

' Add this to the UserForm to build you a list of available sheet names.
Private Sub UserForm_Initialize()
Dim s As Worksheet
Me.ComboBox1.ListRows = Sheets.Count
For Each s In Sheets
Me.ComboBox1.AddItem s.Name, s.Index - 1
Next s
End Sub

Private Sub CommandButton1_Click()
'Replace next line with calling your code and passing it the name of the
sheet that was selected by the user.
Debug.Print ComboBox1.Value
Unload Me
End Sub

 

[john]

whoops - sorry, in my hurry I omitted the on error statement - without it you
will get an error when canels is pressed:

Private Sub SelectSheet()
Dim strWhichSheet As Variant
Dim ws1 As Worksheet

strWhichSheet = Application.InputBox(prompt:="Enter Sheet Name To Sort",
Title:="Sort Worksheet", Type:=2)
If VarType(strWhichSheet) = vbBoolean Then
If strWhichSheet = False Then
Debug.Print "user cancelled"
Exit Sub
End If
End If

On Error Resume Next
If Worksheets(strWhichSheet) Is Nothing Then
msg = MsgBox("Sheet Name " & strWhichSheet & " Does Not Exist!",
vbCritical, "Sort WorkSheet")
Else
'Do your stuff here
Set ws1 = Sheets(strWhichSheet)
MsgBox ws1.Name
End If

End Sub