How to save the value of an OptionButton

birdieman

Well-known Member
Joined
Jan 13, 2016
Messages
551
I have two option buttons (labeled automatic and manual) on a userform that are linked via a GroupName in the properties window. If I click "automatic" and save the file, the next time I open the file, the "automatic" button is not clicked and the "manual one is clicked -- like it did not save the setting.

1. How to I save the value of an OptionButton?

2. I have other pairs of linked OptionButtons on the form in other places. While in the VB editor, I noticed that one button in some pairs is clicked (no code has been written to them yet) while no buttons are clicked in other pairs. Why is this and does it matter while in the VB editor?

thanks
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows
Save the values to refill by some method.
1. Cells
2. Registry (GetSetting(), SaveSetting())
3. External file, txt file, ini file, etc.

(1) is usually done through the ControlSource property. That can be set at design time.
 

birdieman

Well-known Member
Joined
Jan 13, 2016
Messages
551
I do not quite understand what you mean. The buttons are on a userform-- and I am in design time. I save textbox values, but just do not understand what you mean? Can you be more specific? thanks for responding
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows
For which method? Obviously, (1) is the most simple. Select the View menu on the VBE toolbar and select Properties if you don't have it open. In the ControlSource property, type A1 or whatever cell you want to hold the value of True or False. Do the same for the other control but a different cell.
 
Last edited:

birdieman

Well-known Member
Joined
Jan 13, 2016
Messages
551

ADVERTISEMENT

Ok, select a cell on a particular sheet and put in properties box. Then in userform_initialization, I should code "optionbutton3=sheetx( A1)" (in properformat of course)?? Is this correct?

If this is correct, it seems like Excel would have stuck in another property that simply said "Save value of button?" then a yes or no.


thanks for your help
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows
I guess you could do it that way but it is much more involved.

Sounds like you don't know the basics of VBE or ActiveX controls. If clicking the View menu in VBE and then Properties Window is too difficult, just press the F4 key. Now that the Properties Window is open, click the userform or other controls which in this case would be each of the options button co. Notice how the property options change. Then set the ControlSource property as I explained.

There are many tutorials for how to use the VBE. e.g. https://www.techonthenet.com/excel/macros/properties_window2010.php
https://www.youtube.com/watch?v=-XsNDxlE04o
 

birdieman

Well-known Member
Joined
Jan 13, 2016
Messages
551

ADVERTISEMENT

I know how to open the properties window and make changes there. I just did not know how to save the optionsbutton value and I did not know how to reestablish that value when the file is opened again. I suppose that when the file is opened, the optionsbutton value will be established automatically by looking at the cell I put in ControlSourse? I guess I will have to read about it.

thanks for responding
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows
I would have thought so with 529 posts but one never knows for sure.

Yes, just like the method you were trying to use, the ControlSource takes care of it even more easily. I most always test my solutions so once your true and false values are set, it will "remember" from click to click and session to session.

Of course since they are grouped, just setting one ControlSource is sufficient. While the usual practice is to use the Activesheet of say A1, you can force the source to another sheet. e.g. If sheet's tab name was ok then I would use say: ok!A1
 
Last edited:

birdieman

Well-known Member
Joined
Jan 13, 2016
Messages
551
The 529 posts were me asking questions as I learned VBA over the past year, but I have never used optionbuttons. Kind and knowledgeable people on this board like you have helped me a lot -- thank you again.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows
No problem. I learn more too when I answer questions...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,555
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top