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

.

.