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

.

.

.