Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    UserForm OptionButton ControlSource (2000 sp3)

    I haven't been able to find any other post about this so it must be me doing something wrong. I have a userform with 4 optionbuttons. Each optionbutton has its ControlSource property set to a cell. This solves my problem to have the values retained between uses of the userform but now the buttons don't work right! If one is true when the form is loaded I have to click any other optionbutton twice to get it to be true. The first click makes them all false. If I exit when they are all false and then reload, each button will work correctly ONCE only. I also think that occasionally it works correctly for two clicks in a row but I have to try it so many times to get it to happen I might be imagining it. Any ideas? I've attached the spreadsheet.
    Thanks for any help you can give.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm OptionButton ControlSource (2000 sp3)

    Option Buttoins have a GroupName property.

    You need to set the same GroupName for all connected Option Buttons.

    Andrew C

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

    Re: UserForm OptionButton ControlSource (2000 sp3)

    As far as I know, this is a bug in userforms without a solution. It was also mentioned in <post#=382804>post 382804</post#>. Even if the GroupName property is set to the same value for all option buttons, the first click in an "off" option button turns all of them off.

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm OptionButton ControlSource (2000 sp3)

    Thanks, but I tried that and it didn't help, unless I did it wrong. Did you try it?

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm OptionButton ControlSource (2000 sp3)

    Thanks Hans! Sorry I didn't find that post when I searched. I think the final comment in that thread sums it up for me, no need to waste any more time trying to make it work.
    Thanks for your quick and always tremendous help!

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm OptionButton ControlSource (2000 sp3)

    I tried your Form example (your attached .xls) in my Excel2003 but wasn't able to duplicate your problem. Each load of the form retained the last state (on/off for one of the radio buttons) and no matter which ones I picked only one was on or off at a time (as radio buttons are of course). I wish I still had xl2000 on my PC as I'd like to see the problem you see. This is very weird and I do a lot of VBA and don't remember coming across this problem.

    Are you saying that this is only a problem IF you use the controlsource field to retain the state of the buttons after the form is unloaded? I'd be curious to see if it's still an issue if you don't use the controlsource. I say this because I've never had to use that field (because I didn't need to know the state after the form was unloaded, I did my code as each button was clicked instead) so maybe it's related to controlsource only. There are other ways (not as simple though) to retain the state of the buttons after the form has been unloaded that may work for you.

    Deb

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm OptionButton ControlSource (2000 sp3)

    Wow, sounds like they fixed the problem in the 2003 version. The double click is a problem ONLY if I use the controlsource property. But the lost state information is a problem if I don't. The state of the buttons always defaults to false when the form is loaded. The state of the buttons is not retained when the form is unloaded unless I use controlsource or use code to value cells.
    I'm curious, what other ways are you referring to?
    Thanks

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm OptionButton ControlSource (2000 sp3)

    Attached is one way (actually two) to retain the state of which button was selected after the form is closed. I used named variables just like you did with Opt1, Opt2, etc. I stored the button's state in a named var which then gets read at form init to make sure that the last selected button is also the one selected the next time the form is loaded.

    Deb

  9. #9
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm OptionButton ControlSource (2000 sp3)

    Clever, I like that you only kept the number of the one option that is true. It makes sense that I only need to change that one when the form is intialized since the others are false by default.
    Thanks!

Posting Permissions

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