Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input box question | Excel Programming | |||
Input Box Question | Excel Programming | |||
Check Input, into Input box question | Excel Programming | |||
Input Box Question | Excel Discussion (Misc queries) | |||
Input box question | Excel Programming |