Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Combobox that uses Macros

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Wisconsin - USA
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Whitchurch, England
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Greg

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    York, PA
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It should read then

    Case 1
    YourMacro1
    Case 2
    YourMacro2
    Case 3
    ...

    and so on.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •