When closing excel remember radio button choice

Hi there,

How can i save the radio button choice and calculate a sum?
If i press the radio button the sum its ok, but if i close the excel workbook and open it again, the correct radion button is selected but doesnt make the sum requested in the function.

I had made so far this: (maybe i have to pass the boolean as a parameter to the workbook_open, but i dont know how to do it, and i am confused if the things have to be private or public)

This code is on: ThisWorkbook

Please Login or Register  to view this content.

.
This code is on: Sheet1

Please Login or Register  to view this content.

But it doesnt work when i close the excel and then open it again, as i was explaning,

Can anyone help me with this problem?
Thanks

You need to store the value of the option box and then retrieve it when the file is opened (or when the sheet is selected). You can store the value in the workbook, a separate file, or the windows registry. The simplest method is to put it someplace in the workbook (remember to save before closing). If you still need help with this process, let me know and I'll post an example. Thanks!

Hi Blue_Wings,

I've run into this topic before. My old solution was to take some not-used cell and put a value in it that reflected which radio button was clicked. When a radio button is selected, I'd simply plug a number in this cell to remember it. When the workbook was closed the value of this cell is saved also. When the workbook is opened you will need routines to look at the not-used cell and reset your radio buttons to what they were when last saved.

A few months ago I found Range Names that may do the same thing without needing a cell. After looking they are called Named Constants in http://pubs.logicalexpressions.com/p...cle.asp?id=281

You could create a Named Constant that reflects which radio button instead of using a cell. The constant will be saved when you save the workbook.

Attach a short example with radio buttons on it, if you need, and we can explore the code to make all this high powered theory work.

Here's an example using the workbook you uploaded. This uses Marvin's suggested method of storing it as a constant name (I called the name OptionA). You'll notice the VBA has some changes/additions which include the name. The workbook should prompt you to save before closing--I was only reminding you to do so.

Please Login or Register  to view this content.

You are correct; you have to create the named constant first. Here you will find step by step instructions for doing so:
http://www.dummies.com/how-to/conten...xcel-2007.html

Constant created
Now i am dealing with another problem, while i am working on the sheet, if i change the value of the cell "H29" it doesnt refresh the result aotmatically, i have to press the radio button again to refresh the result.

How do i solve that problem when i open the workbook, change sheet or change cell value?

I guess i will need these sub's :

Please Login or Register  to view this content.

when i change from another sheet to this one.

Please Login or Register  to view this content.

when i open the excel file.

Please Login or Register  to view this content.

when the cell value has changed

But can you tell me if these ones ane correct? And how to refresh in this whole cases?
Thanks.

Does pressing F9 update the calculation? If so, automatic calculations are turned off and can be turned on in the Excel Options. If you want the option buttons to change when a cell value changes, they should probably have their source value set to a cell rather than a named constant. I don't have Excel 2007 at work, but if you repost your workbook I can at least review what you have later tonight (if the above doesn't work/make sense). Thanks!

F9 does the automatic calculation in the cells, but because i have inside the

Please Login or Register  to view this content.

the

Please Login or Register  to view this content.

the "TotalPayment" doesnt refresh if i change the value in the cell H29 in "PaymentA".
You can use the example that you gave to me in reply nš 6 OptionButton.xlsm it fits the same problem.
Thanks.

When the cell value changes you should run the update...but it sounds like you may need to be using formulas instead of hard-coded values. Personally I try to avoid worksheet change events when possible. Attached is a non-vba method to do what you want. This uses two named ranges (OptionBtnA and OptionBtnB). They're assigned to the linked property of each Option Button.