Combobox that uses Macros

[greg0226]

Hi. I am trying to make a combobox of three values. Each time you select one of the options it will perform a macro. If anyone can help this would be great. I am trying to get the first option to print the first page, 2nd option will print the first two pages. The 3rd option will print all three pages. Any help would be great. thanks.

Greg

[Russell Hauf]

On 2002-02-25 20:45, greg0226 wrote:
Hi. I am trying to make a combobox of three values. Each time you select one of the options it will perform a macro. If anyone can help this would be great. I am trying to get the first option to print the first page, 2nd option will print the first two pages. The 3rd option will print all three pages. Any help would be great. thanks.

Greg

Have you solved this? If not, can you tell me which ComboBox you are using? (did you create it from the Forms toolbar, or the Control Toolbox toolbar)

-rh

[greg0226]

I am using the Combobox from the Control Toolbox. I have not been able to solve this problem yet, so if you can help that would be awesome. thanks.

[turbotoan]

Hi there,
I haven't played with selected pages, but you can definitely add a Choose Case statement into the script behind the combobox. From the VBA editor...under the Project Explorer, drill down to the Form you have this combobox listed.
Then, left-Double-click on the combobox field. You should automatically be placed in a script window with the name of the combobox & the _change...
i.e. Private Sub cboAssignNm_Change()
End Sub
In this script, define variables & place your case statement.

As for the printing...
See this example..
- Worksheets(strWorksheetName).Activate - ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

[greg0226]

Sorry I dont think i was really clear on my question. The problem that i am having is trying to get the options to run the macros. I can't write the script right or something. Not sure how to go about it. If anyone can help with this, that would be great. Thanks for replying everyone though.

Greg

[Barfbagger]

A combo box has a linked cell. A combobox from the forms toolbox (known as a dropdown box) returns an integer while the control toolbox returns a string. When you select from a combo list it changes the value in the linked cell. Try this:

Create a new sheet and type "red" "blue" and "green" in cells A1 to A3.
Open the forms toolbox and create a combobox on the worksheet right click and select Format Control. Got to the Control tab and enter A1:A3 for the input range, G12 for the linked cell. OK. Right click again and select Assign Macro, type in "Dropdown1_change"
Open the Controls toolbox and create another combobox on the worksheet. Right click and select properties. Find the LinkedCell property and enter G15, find the ListFillRange and enter A1:A3. Now right click and select View Code then Change from the righthand dropdown list.
Enter

Application.Run Range("g15").Text

between the Private Sub and End Sub.

Now enter the following into a macro module for the work book


Sub Macro1()
Application.Run "Red"
End Sub

Sub Macro2()
Application.Run "Blue"
End Sub

Sub Macro3()
Application.Run "Green"
End Sub

Sub Red()
Range("a10:b15").Select
With Selection.Interior
.ColorIndex = 3
End With
End Sub

Sub Blue()
Range("a10:b15").Select
With Selection.Interior
.ColorIndex = 5
End With
End Sub

Sub Green()
Range("a10:b15").Select
With Selection.Interior
.ColorIndex = 4
End With
End Sub

Sub Dropdown1_change()
Dim intChoice As Integer
Dim strChoice As String
intChoice = Range("G12").Value
strChoice = Trim(Str(intChoice))
Application.Run "Macro" & strChoice
End Sub


Close the toolboxes and try them out


[ This Message was edited by: Barfbagger on 2002-02-27 00:05 ]

[ This Message was edited by: Barfbagger on 2002-02-27 00:06 ]

[ This Message was edited by: Barfbagger on 2002-02-27 01:38 ]

[Ivan F Moala]

On 2002-02-26 19:51, greg0226 wrote:
Sorry I dont think i was really clear on my question. The problem that i am having is trying to get the options to run the macros. I can't write the script right or something. Not sure how to go about it. If anyone can help with this, that would be great. Thanks for replying everyone though.

Greg

Try something along these lines for your
combobox code;

Private Sub ComboBox1_Change()

Select Case ComboBox1.ListIndex + 1

Case Is = 1
MsgBox "1"
Case Is = 2
MsgBox "2"
Case Is = 3
MsgBox "3"
End Select
End Sub

HTH

Ivan

Private Sub ComboBox1_DropButt*******()
Dim x
If ComboBox1.ListIndex = -1 Then
For x = 1 To 3
ComboBox1.AddItem x
Next
End If
End Sub

[greg0226]

Thanks everyone. I got it to work. I appreciate all the responses.

Greg

[mdennis624]

Hi Gregg,

I'm trying to do the same thing using a combo box to run other macros. What code did you substitute for the [MsgBox "1", "2",etc] line in the last example from Ivan? I'm having trouble getting the right code to call the macros. Thank you, Mike D.

[ This Message was edited by: mdennis624 on 2002-02-27 18:24 ]

[Juan Pablo González]

It should read then

Case 1
YourMacro1
Case 2
YourMacro2
Case 3
...

and so on.