ComboBox and select item

[Soniya]

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

 

[Richard Daniels]

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).PrintPreview
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

 

[soniya]

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

 

[Richard Daniels]

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

 

[Richard Daniels]

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

 

[Harald Staff]

Hi Soniya

If your items are in sheet order then
Sheets(ComboBox1.ListIndex+1).Activate