ComboBox and select item
Hi All,
I have a UserForm with a ComboBox and Two CommandButtons ComboBox1 has Item1, Item2, Item3.. Item8 and CommandButton1 =Print and CommandButton2 = Print Preview No How can I make code to select for Eg. Item1 then it selects the specific sheet eg. Sheet("Item1") and do my code when I click the Command Button? I have somethinlike this: userform_initialise RType = ComboBox1.ListIndex ComboBox1.SetFocus With ComboBox1.Object .Clear .AddItem "ITEM 1" .AddItem "ITEM 2" .AddItem "ITEM 3" .AddItem "ITEM 4" .AddItem "ITEM 5" End With Sub ReportMain() Application.ScreenUpdating = False If RType = 0 Then Sheets("ITEM 1").Select PrintR ElseIf RType = 1 Then Sheets("ITEM 2").Select PrintR ElseIf RType = 2 Then Sheets("ITEM 3").Select PrintR ETC.... End If TIA Soniya |
ComboBox and select item
Hi Soniya
Try this create a userform add a combobox and 2 command buttons. When the userform initializes it will add all the sheet name to the combobox. Clicking the preview command btn will display the print preview window, and the print btn will print the selected sheet. Private Sub CommandButton1_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintPrevie w End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintOut Copies:=1, Collate:=True End Sub Private Sub UserForm_Initialize() Dim i As Integer With Me For i = 1 To ThisWorkbook.Sheets.Count .ComboBox1.AddItem Sheets(i).Name Next End With End Sub Richard Daniels -----Original Message----- Hi All, I have a UserForm with a ComboBox and Two CommandButtons ComboBox1 has Item1, Item2, Item3.. Item8 and CommandButton1 =Print and CommandButton2 = Print Preview No How can I make code to select for Eg. Item1 then it selects the specific sheet eg. Sheet("Item1") and do my code when I click the Command Button? I have somethinlike this: userform_initialise RType = ComboBox1.ListIndex ComboBox1.SetFocus With ComboBox1.Object .Clear .AddItem "ITEM 1" .AddItem "ITEM 2" .AddItem "ITEM 3" .AddItem "ITEM 4" .AddItem "ITEM 5" End With Sub ReportMain() Application.ScreenUpdating = False If RType = 0 Then Sheets("ITEM 1").Select PrintR ElseIf RType = 1 Then Sheets("ITEM 2").Select PrintR ElseIf RType = 2 Then Sheets("ITEM 3").Select PrintR ETC.... End If TIA Soniya . |
ComboBox and select item
Thanks Richard, My sheet name is not the same as my combobox list item for eg. if i select "A" from ComboBox i have to select "sheet1" How can I do this? TIA soniya -----Original Message----- Hi Soniya Try this create a userform add a combobox and 2 command buttons. When the userform initializes it will add all the sheet name to the combobox. Clicking the preview command btn will display the print preview window, and the print btn will print the selected sheet. Private Sub CommandButton1_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintPrevie w End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintOut Copies:=1, Collate:=True End Sub Private Sub UserForm_Initialize() Dim i As Integer With Me For i = 1 To ThisWorkbook.Sheets.Count .ComboBox1.AddItem Sheets(i).Name Next End With End Sub Richard Daniels -----Original Message----- Hi All, I have a UserForm with a ComboBox and Two CommandButtons ComboBox1 has Item1, Item2, Item3.. Item8 and CommandButton1 =Print and CommandButton2 = Print Preview No How can I make code to select for Eg. Item1 then it selects the specific sheet eg. Sheet("Item1") and do my code when I click the Command Button? I have somethinlike this: userform_initialise RType = ComboBox1.ListIndex ComboBox1.SetFocus With ComboBox1.Object .Clear .AddItem "ITEM 1" .AddItem "ITEM 2" .AddItem "ITEM 3" .AddItem "ITEM 4" .AddItem "ITEM 5" End With Sub ReportMain() Application.ScreenUpdating = False If RType = 0 Then Sheets("ITEM 1").Select PrintR ElseIf RType = 1 Then Sheets("ITEM 2").Select PrintR ElseIf RType = 2 Then Sheets("ITEM 3").Select PrintR ETC.... End If TIA Soniya . . |
ComboBox and select item
Hi Soniya
Sorry misunderstood. Here is another piece of code Private Sub CommandButton1_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(getWorksheet (Me.ComboBox1.Text)).PrintPreview End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(getWorksheet (Me.ComboBox1.Text)).PrintOut Copies:=1, Collate:=True End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .AddItem "A" .AddItem "B" End With End Sub Private Function getWorksheet(userChoice As String) As String Select Case userChoice Case Is = "A" getWorksheet = "Sheet1" Case Is = "B" getWorksheet = "Sheet2" 'etc 'etc End Select End Function The function getWorksheet will return a string of the worksheet name, based on the input ie the text from the combobox. It's a bit messy as you will have to maintain the hardcoded select case options. Hope this helps Richard -----Original Message----- Thanks Richard, My sheet name is not the same as my combobox list item for eg. if i select "A" from ComboBox i have to select "sheet1" How can I do this? TIA soniya -----Original Message----- Hi Soniya Try this create a userform add a combobox and 2 command buttons. When the userform initializes it will add all the sheet name to the combobox. Clicking the preview command btn will display the print preview window, and the print btn will print the selected sheet. Private Sub CommandButton1_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintPrevie w End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintOut Copies:=1, Collate:=True End Sub Private Sub UserForm_Initialize() Dim i As Integer With Me For i = 1 To ThisWorkbook.Sheets.Count .ComboBox1.AddItem Sheets(i).Name Next End With End Sub Richard Daniels -----Original Message----- Hi All, I have a UserForm with a ComboBox and Two CommandButtons ComboBox1 has Item1, Item2, Item3.. Item8 and CommandButton1 =Print and CommandButton2 = Print Preview No How can I make code to select for Eg. Item1 then it selects the specific sheet eg. Sheet("Item1") and do my code when I click the Command Button? I have somethinlike this: userform_initialise RType = ComboBox1.ListIndex ComboBox1.SetFocus With ComboBox1.Object .Clear .AddItem "ITEM 1" .AddItem "ITEM 2" .AddItem "ITEM 3" .AddItem "ITEM 4" .AddItem "ITEM 5" End With Sub ReportMain() Application.ScreenUpdating = False If RType = 0 Then Sheets("ITEM 1").Select PrintR ElseIf RType = 1 Then Sheets("ITEM 2").Select PrintR ElseIf RType = 2 Then Sheets("ITEM 3").Select PrintR ETC.... End If TIA Soniya . . . |
ComboBox and select item
Hi Soniya
Sorry, the function getworksheet will return as string of the worksheet name based in the input ie text from the combobox. It's a but messy as you will have to maintain the select case statement, so no error occurs. Private Sub CommandButton1_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(getWorksheet (Me.ComboBox1.Text)).PrintPreview End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(getWorksheet (Me.ComboBox1.Text)).PrintOut Copies:=1, Collate:=True End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .AddItem "A" .AddItem "B" End With End Sub Private Function getWorksheet(userChoice As String) As String Select Case ucase(userChoice) Case Is = "A" getWorksheet = "Sheet1" Case Is = "B" getWorksheet = "Sheet2" 'etc etc End Select end function -----Original Message----- Thanks Richard, My sheet name is not the same as my combobox list item for eg. if i select "A" from ComboBox i have to select "sheet1" How can I do this? TIA soniya -----Original Message----- Hi Soniya Try this create a userform add a combobox and 2 command buttons. When the userform initializes it will add all the sheet name to the combobox. Clicking the preview command btn will display the print preview window, and the print btn will print the selected sheet. Private Sub CommandButton1_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintPrevie w End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintOut Copies:=1, Collate:=True End Sub Private Sub UserForm_Initialize() Dim i As Integer With Me For i = 1 To ThisWorkbook.Sheets.Count .ComboBox1.AddItem Sheets(i).Name Next End With End Sub Richard Daniels -----Original Message----- Hi All, I have a UserForm with a ComboBox and Two CommandButtons ComboBox1 has Item1, Item2, Item3.. Item8 and CommandButton1 =Print and CommandButton2 = Print Preview No How can I make code to select for Eg. Item1 then it selects the specific sheet eg. Sheet("Item1") and do my code when I click the Command Button? I have somethinlike this: userform_initialise RType = ComboBox1.ListIndex ComboBox1.SetFocus With ComboBox1.Object .Clear .AddItem "ITEM 1" .AddItem "ITEM 2" .AddItem "ITEM 3" .AddItem "ITEM 4" .AddItem "ITEM 5" End With Sub ReportMain() Application.ScreenUpdating = False If RType = 0 Then Sheets("ITEM 1").Select PrintR ElseIf RType = 1 Then Sheets("ITEM 2").Select PrintR ElseIf RType = 2 Then Sheets("ITEM 3").Select PrintR ETC.... End If TIA Soniya . . . |
ComboBox and select item
Hi Soniya
If your items are in sheet order then Sheets(ComboBox1.ListIndex+1).Activate -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please "soniya" skrev i melding ... Thanks Richard, My sheet name is not the same as my combobox list item for eg. if i select "A" from ComboBox i have to select "sheet1" How can I do this? TIA soniya -----Original Message----- Hi Soniya Try this create a userform add a combobox and 2 command buttons. When the userform initializes it will add all the sheet name to the combobox. Clicking the preview command btn will display the print preview window, and the print btn will print the selected sheet. Private Sub CommandButton1_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintPrevie w End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.Text = "" Then Exit Sub Me.Hide ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintOut Copies:=1, Collate:=True End Sub Private Sub UserForm_Initialize() Dim i As Integer With Me For i = 1 To ThisWorkbook.Sheets.Count .ComboBox1.AddItem Sheets(i).Name Next End With End Sub Richard Daniels -----Original Message----- Hi All, I have a UserForm with a ComboBox and Two CommandButtons ComboBox1 has Item1, Item2, Item3.. Item8 and CommandButton1 =Print and CommandButton2 = Print Preview No How can I make code to select for Eg. Item1 then it selects the specific sheet eg. Sheet("Item1") and do my code when I click the Command Button? I have somethinlike this: userform_initialise RType = ComboBox1.ListIndex ComboBox1.SetFocus With ComboBox1.Object .Clear .AddItem "ITEM 1" .AddItem "ITEM 2" .AddItem "ITEM 3" .AddItem "ITEM 4" .AddItem "ITEM 5" End With Sub ReportMain() Application.ScreenUpdating = False If RType = 0 Then Sheets("ITEM 1").Select PrintR ElseIf RType = 1 Then Sheets("ITEM 2").Select PrintR ElseIf RType = 2 Then Sheets("ITEM 3").Select PrintR ETC.... End If TIA Soniya . . |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com