Input Box Question
 

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.

Input Box Question
 

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
--
jb


"marcia2026" wrote:

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.

Input Box Question
 

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

"marcia2026" wrote:

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.

Input Box Question
 

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
--
jb


"marcia2026" wrote:

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.