How to save status of an option button group?

[trailbarge]

I've looked all over. I can't find out how to do this. For reference, I am using Excel 2003 SP3 and my control toolbar says "Controls" and the buttons are all Microsoft Forms 2.0

Here is the scenario. I am building an engineering calculations suite. For a certain project, the engineer may want to do code calcs one day and siesmic calcs several weeks later.

My userform has a page for general information... customer name, project number, etc. I linked these text boxes to cells in a hidden worksheet so that when the engineer comes back to the file weeks later to do another section of calcs, the form opens with the relevent data already filled in.

One set of options is units. Some customers want feet, some inches, some millimeters some centimeters. I have an option group on the form to select the length unit. (There are other units... pressure, temp, etc... I'm keeping the example small inscale).

The option groups all worked as advertised until I tried to build in this save state stuff. I linked each option button to its own control source. Now when I run the dialog, I have to click an option twice to get my change. The first click falsifies whatever option was true by default. I'm left with all option button registering false (weird). A second click registers the button as true.

I read somewhere that they were all supposed to be linked to the SAME cell. When I did this, the option group jammed on the last option and would not budge.

Anybody got an idea how to pull this off? I've read lots of people who had this problem but all the workarounds don't fit into the scenario I described above.

 

[BrianB]

The solution suggested is for option buttons from the *Forms toolbar* that uses an additional frame ("GroupBox") to group them in sets.

The *Controls toolbar* options are grouped by selecting sets and giving them the same GroupName property. They are not dependant on a frame. In fact their handling, apart from the grouping, tends towards needing to treat each one as a separate entity. Trying to give them the same LinkedCell will result in the behaviour you are experiencing (they all take on that value)

The best method of saving their setting is to save the workbook.

Failing that you will need 2 macros. One to copy the set of linked cell values to somewhere else eg. 2 named ranges

'partial code
MyRange2.Value=MyRange1.Value

and another to copy them back to the original location. eg

'partial code
MyRange1.Value=MyRange2.Value

 

[BrianB]

This is another current query for which I have suggested something similar
http://www.mrexcel.com/forum/showthread.php?p=1716762

 

[trailbarge]

Thanks, Brian.

I tried using group frames. I used group names. I even grouped the options, trying to get a value out of that. I tried using a control source for each button and a single control source for all of the buttons. Then I ran a permutation of all those ideas.

nothing worked.

What I DID do was code a click event for each button. It changed the value of a disabled text box in each frame (I supposed it didn't really HAVE to be in the frame, but it looked nicer that way). Now that text box has a control source that works just fine and I can use the value of the text box to control my unit conversions throughout the module.

I'm still open to change back to a simpler code that uses the option buttons or the group directly, but this works exactly as advertised.

======================================

This is where I was going to attach a screenshot of the form, but I can't find the "manage attachments" button.

Sub UserForm_Initialize()
     If TxtUnitPressure.Value = "psig" Then
          OptPSI.Value = True
     ElseIf TxtUnitPressure.Value = "barg" Then
          OptBAR.Value = True
     End If
 
     If TxtUnitLength.Value = "in" Then
          OptINCH.Value = True
     ElseIf TxtUnitLength.Value = "mm" Then
          OptMM.Value = True
     End If
 
     If TxtUnitMass.Value = "lbm" Then
          OptLB.Value = True
     ElseIf TxtUnitMass.Value = "Kg" Then
          OptKG.Value = True
     End If
 
     If TxtUnitTemp.Value = "°F" Then
          OptFarenheit.Value = True
     ElseIf TxtUnitTemp.Value = "°C" Then
          OptCelcius.Value = True
     End If
End Sub
 
 
Private Sub OptPSI_Click()
     TxtUnitPressure = "psig"
End Sub
 
Private Sub OptBAR_Click()
     TxtUnitPressure = "barg"
End Sub
 
Private Sub TxtUnitPressure_change()
     LblDesPUnits.Caption = TxtUnitPressure.Value
     LblTestUnits.Caption = TxtUnitPressure.Value
End Sub
 
 
Private Sub OptINCH_Click()
     TxtUnitLength = "in"
End Sub
 
Private Sub OptMM_Click()
     TxtUnitLength = "mm"
End Sub
 
Private Sub TxtUnitLength_change()
     LblCorrAllUnits.Caption = TxtUnitLength.Value
End Sub
 
 
Private Sub OptLB_Click()
     TxtUnitMass = "lbm"
End Sub
 
Private Sub OptKG_Click()
     TxtUnitMass = "Kg"
End Sub
 
Private Sub TxtUnitMass_change()
      ' I haven't coded anything for this one yet
End Sub
 
Private Sub OptFarenheit_Click()
     TxtUnitTemp = "°F"
End Sub
 
Private Sub OptCelcius_Click()
     TxtUnitTemp = "°C"
End Sub
 
Private Sub TxtUnitTemp_change()
     LblMaxDesTUnits.Caption = TxtUnitTemp.Value
     LblMinDesTUnits.Caption = TxtUnitTemp.Value
End Sub

 

[BrianB]

Without examining your code in detail ....
as mentioned, with Controls optionbuttons we have to deal with them separately, so if your code is working that is Ok. You could also use a set of separate linked cells - useful if you are not using code - but still with the "separation" principle.

The Forms toolbar optionbs in a frame can use the same linked cell - which then shows the number of the button showing True.

 

[trailbarge]

BrianB,

Thanks for the reply. I can't find a "linked cell" option in my opt button properties. I believe you are thinking of the forms that you put on worksheets. I am using the forms and controls exclusively in VBA.

The code is working great so far (yes, I know that the if statements are not airtight... I'm still in development... ) but I'd like to satisfy my curiosity on how to do this without having to maintain / document all that extra code.

I'm not a programmer and the engineers who will be maintaining this thing after I've been beer trucked will be even less so.... so keeping it simple is a high priority for me. Any other ideas would be appreciated.

Thanx
TrailBarge

 

[BrianB]

ControlSource then