Mark Dalley
asked on
Get the value of a control on a worksheet
Dear Experts
I have a pair of option buttons embedded on a sheet. They alternate true and false values between each other just as they should.
But, How do I retrieve the value of an option button inside Excel VBA, when the option button is NOT on a form?
NB Neither of them are linked to a cell. The values given to them do however persist through a save/reload, so must be stored somewhere.
I have searched the help, and EE, yet not found the answer to what seems a very simple question. All the examples I have seen assume that a form is being used.
Many thanks in advance.
Hopeful Kiwi
I have a pair of option buttons embedded on a sheet. They alternate true and false values between each other just as they should.
But, How do I retrieve the value of an option button inside Excel VBA, when the option button is NOT on a form?
NB Neither of them are linked to a cell. The values given to them do however persist through a save/reload, so must be stored somewhere.
I have searched the help, and EE, yet not found the answer to what seems a very simple question. All the examples I have seen assume that a form is being used.
Many thanks in advance.
Hopeful Kiwi
ASKER
They are actually Forms controls.
Also, I need to be able to retrieve the value of the control as soon as the worksheet opens (before anything gets clicked and loses the previous state), so putting it into a click event handler is too late, so to speak.
Hopeful Kiwi
Also, I need to be able to retrieve the value of the control as soon as the worksheet opens (before anything gets clicked and loses the previous state), so putting it into a click event handler is too late, so to speak.
Hopeful Kiwi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Kyle / imnorie
Both your latest methods work fine.
Who would have guessed that an option button is a drawingObject! The help is basically silent about DrawingObjects, likewise about the fact that a worksheet has an OptionButtons collection, and OptionButton properties.
The help did however disclose that the two possible values of an option button revealed by kgerb's code (1 and -4146) correspond to the constants xlOn and xlOff respectively. Not what one would call an intuitive correspondence.
Many thanks to both of you - I will split the points equally.
Hopeful Kiwi
P.S. EE made me choose a best solution but I think they were both equally good / instructive.
Both your latest methods work fine.
Who would have guessed that an option button is a drawingObject! The help is basically silent about DrawingObjects, likewise about the fact that a worksheet has an OptionButtons collection, and OptionButton properties.
The help did however disclose that the two possible values of an option button revealed by kgerb's code (1 and -4146) correspond to the constants xlOn and xlOff respectively. Not what one would call an intuitive correspondence.
Many thanks to both of you - I will split the points equally.
Hopeful Kiwi
P.S. EE made me choose a best solution but I think they were both equally good / instructive.
If we assume they are ActiveX then put this code in the worksheet module of the worksheet they are on.
Open in new window