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

.

.

.