Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Needed to make make command button visible (

    My spreadsheet contains 4 optionbuttons and a checkbox. I'm looking to have different command buttons appear depending on which optionbutton is clicked & if the checkbox is checked. Example: if optionbutton1 is checked & the checkbox is checked, commandbutton1 will appear, but if the checkbox was not checked commandbutton2 will appear.
    I know little to nothing about writing macros so if someone has idea on how this can be done, please give step by step. Thanks in advance for any helpful suggestions

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Needed to make make command button visible (

    Welcome to Woody's Lounge!

    Which toolbar did you use to create the controls? The Forms toolbar of the Control Toolbox?

  3. #3
    New Lounger
    Join Date
    Oct 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Needed to make make command button visib

    Hans,
    I used the control toolbox.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Needed to make make command button visib

    See the attached workbook (zipped). Right-click the sheet tab and select View Code to see the code for the check box and option buttons. The command buttons do nothing, there is no code for them.

  5. #5
    New Lounger
    Join Date
    Oct 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Needed to make make command button visib

    Hans, thanks so much for the macro. I tried it today and it works great. I made a few adjustments to customize it to my exact needs and assigned macros to the command buttons, it still works as desired with one exception. One of the changes involves having different command buttons appear depending on if the checkbox is checked. Example: option1button checked & checkbox checked command button1 appears, if checkbox not checked command button2 appears. Basically, different combos determine which command button will appear. The problem is, for example, optionbox1 is checked and checkbox is checked command button1 appears, but if I uncheck the checkbox the command button doesnt change unless I choose a different optionbox . Is it possible to get the command buttons to change as soon as the checkbox is changed? Not a big issue, more of an annoyance.
    Again, Thanks for the code.

  6. #6
    New Lounger
    Join Date
    Oct 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Needed to make make command button visib

    Hans, thanks for the quick response. Tried your updated code and it did the trick. Thank you!
    You have helped make my spreadsheet quite impressive.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Needed to make make command button visib

    The On Click code for the check box in the version I posted is:

    Private Sub CheckBox1_Click()
    If Me.CheckBox1 = False Then
    Me.CommandButton1.Visible = False
    Me.CommandButton2.Visible = False
    Me.CommandButton3.Visible = False
    Me.CommandButton4.Visible = False
    Else
    OptionButton1_Click
    OptionButton2_Click
    OptionButton3_Click
    OptionButton4_Click
    End If
    End Sub

    which basically means that the state of the option buttons is only taken into account if the check box is "on". Try simplifying the code to

    Private Sub CheckBox1_Click()
    OptionButton1_Click
    OptionButton2_Click
    OptionButton3_Click
    OptionButton4_Click
    End Sub

    Assuming that you have adapted OptionButton1_Click etc., it should now work OK whether the check box is ticked or cleared.

Posting Permissions

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