Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > ComboBox and select item
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default 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

.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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

.

.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default 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

.

.

.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default 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

.

.

.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 47
Default 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

.

.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use match function to add different item in Combobox Leonard Lan Excel Worksheet Functions 2 September 9th 09 06:26 PM
How to select item with filters? Eric Excel Discussion (Misc queries) 1 February 29th 08 01:58 PM
item limit for a combobox? KarenH Excel Worksheet Functions 3 July 20th 06 09:30 PM
select from combobox and show a price in textbox gem New Users to Excel 1 June 14th 06 11:37 AM
Select Text Files from Combobox D.Parker Excel Discussion (Misc queries) 4 June 28th 05 03:46 PM


All times are GMT +1. The time now is 05:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement