ActiveX ComboBox to drive workbook presentation
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: ActiveX ComboBox to drive workbook presentation

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ActiveX ComboBox to drive workbook presentation

    Hi all,


    I would like to use an an ActiveX ComboBox to drive the formatting of the Excel workbook.

    Particulars of relevance:
    - ComboBox name: ComboBox1
    - ComboBox options: Total, Products, Services


    What I have thus far:

    Sub ComboBox1_Change()

    Select Case ComboBox1.Value
    Case Total
    MsgBox "Total"
    Case Products
    MsgBox "Products"
    Case Services
    MsgBox "Services"
    End Select

    End Sub
    However selecting the drop-down options in the ComboBox does absolutely nothing pertaining to the Macro. No errors, nothing. My formulas work as expected but nothing from the Macro.

    Any thoughts? I've spent two full days on it now and I really should do some proper work.

  2. #2
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Forgot to mention the MsgBox rows are there as proxies just while I get it working. They will then be overriden with row hiding, etc.

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,203
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    4 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Try this:
    Code:
    Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value
    Case "Total"
    MsgBox "Total"
    Case "Products"
    MsgBox "Products"
    Case "Services"
    MsgBox "Services"
    End Select
    End Sub
    You needed quotes
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Quote Originally Posted by My Aswer Is This View Post
    Try this:
    Code:
    Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value
    Case "Total"
    MsgBox "Total"
    Case "Products"
    MsgBox "Products"
    Case "Services"
    MsgBox "Services"
    End Select
    End Sub
    You needed quotes
    No, still nothing.

    Two questions:
    1.) I notice you've made the macro private. I know this hides it from the macro list but are there any other benefits?
    2.) I feel as though the ComboBox isn't linking to the macro. Is there a way to verify this? When I say "Select Case ComboBox1.Value" is this the name of the ComboBox or the first ComboBox in the spreadsheet? More often than not they'll be the same but I'd like to confirm.

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Oh and thanks for your help

  6. #6
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    BINGO!!!!! Solved.

    The macro was in only in Module1. When I copied to Sheet1 it worked a treat.

    Thanks so much for your help!

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,203
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    4 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Well if it's a Activex Combobox on your sheet it's always shown as a Private.
    Right click on the sheet tab and select view code and you will see the code

    Yes Combobox1 is the name of the Combobox

    That is the name you had in your script.

    Did the script post the Message Box?

    I can not have it do something else unless you tell me what you want it to do.
    And a change only occurs when you select another value in the combobox not the current one selected.


    You said:

    I feel as though the ComboBox isn't linking to the macro

    What macro?

    The message box popping up is the macro
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Yes, it's working perfectly now. Thanks for your help.

    This is the current product:

    Sub ComboBox1_Change()

    Select Case ComboBox1.Value
    Case "Total"
    Rows("24:35").Select
    Selection.EntireRow.Hidden = False
    Range("A1").Select
    Case "-----------"
    Rows("24:35").Select
    Selection.EntireRow.Hidden = False
    Range("A1").Select
    Case "Products"
    Rows("24:35").Select
    Selection.EntireRow.Hidden = True
    Range("A1").Select
    Case "Services"
    Rows("24:35").Select
    Selection.EntireRow.Hidden = True
    Range("A1").Select
    Case "Licensing"
    Rows("24:35").Select
    Selection.EntireRow.Hidden = False
    Range("A1").Select
    End Select

    End Sub

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,203
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    4 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Glad to see you have things working:
    You could write it like this if you wanted:
    Code:
    Case "Total"
    Rows("24:35").Hidden = True: Range("A1").Select
    Last edited by My Aswer Is This; May 24th, 2018 at 11:14 AM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ActiveX ComboBox to drive workbook presentation

    Yes thatís a great suggestion.

    I didnít like the fact you could visibly see the rows being selected first. This will eliminate that thereby masking the macro workings from the user.

User Tag List

Tags for this Thread

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
  •  

 

DMCA.com