Combobox that uses Macros

greg0226

New Member
Joined
Feb 24, 2002
Messages
31
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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<quote>Application.Run Range("g15").Text</quote>
between the Private Sub and End Sub.

Now enter the following into a macro module for the work book<quote>
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</quote>

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
 
Upvote 0
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_DropButtonClick()
Dim x
If ComboBox1.ListIndex = -1 Then
For x = 1 To 3
ComboBox1.AddItem x
Next
End If
End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top