Solved: Turning off optbuttons in other tabs

[nardella]

I've created a gallery in Word - a form with 5 tabs, each with option buttons to insert a series of different tables. We're using the tabs to organize the tables by type.

I'm having problems deselecting options buttons used previously. In other words, if a table was already inserted into the document using an obtion button, when we open the gallery form a second time to insert a new table from a different tab, the previous selection remains on. Thus, instead of the current selection, both tables are inserted, one embedded within the other.

How do I disable all tabs not selected?

[lucas]

hide the form instead of closing it?

[nardella]

neither close nor hide form.... I need to deselect all option buttons previously used before selecting a new option button

[lucas]

can you post the document? click on post reply on the lower left of the last post and after typing your message, scroll down and look for a button that says Manage attachments.

Be sure to remove any personal or private info before posting. For the most help, post in 2003 format.

[nardella]

please see attached. thanks.

[lucas]

I added this to the top of the code module for the userform and changed the unhide to unload at the bottom of the module:


Private Sub UserForm_Initialize()
Dim ctrl As Control
For Each ctrl In frmEquationsGallery.Controls
If TypeName(ctrl) = "OptionButton" Then
ctrl.Value = False
End If
Next ctrl
End Sub


It finds all the option buttons when the userform is loaded and resets them.

You have some other serious problems with your code though. You should try to avoid selection as it is an unnesessary step.

I advise that you add option explicit to the top line of every module, thisdocument, the userform and any modules. It will point to errors in your code so you can address them.

[nardella]

wonderful... i'll see how it works and look into your other suggestions - i am really a VBA newbie. thx.

[nardella]

I'm experiencing a similar problem as described previously. I've created a form to insert preset tables with predetermined content into a Word document.

The form has five tabs, each tab houses a set of tables. I need this form to insert a single table at a time. I need to be able to select one item from the entire form. So, if I select an item from one tab, then change my mind and decide to make a different selection, I need the first selection to be deactivated.

Please help!

[SamT]

Set a CommandButton to do what Lucas' code above does. See the OnClick Event for cbut's for help.

[fumei]

I am with Steve, the use of Selection is huge, and should be avoided.


How are firing this - to Show the userform???
Public Sub OpenEquationGallery()

frmEquationsGallery.Show

End Sub
And...how are you RE-showing it when you have used Hide?

BTW; if you do indeed want to use .Hide (and again, how are you getting it back?), you can clear the option buttons with the same code Steve posted for Initialize. Except put it in Activate.
Private Sub UserForm_Activate()
Dim ctrl As Control
For Each ctrl In frmEquationsGallery.Controls
If TypeName(ctrl) = "OptionButton" Then
ctrl.Value = False
End If
Next ctrl
End Sub
Now when you reShow it, all option buttons are cleared.


As for the issue of a option button on one Tab, clearing off any others on other Tabs...well, it is brute force I'm afraid. You have to check ALL the others and clear them. You can only have one option button set on each frame, true. But you have multiple frames, and as you see, they are independent of the other frames. It is going to take careful and tedious logic. It is not difficult, but it takes some effort.

I would also suggest being consistent. Some tabs have the controls in a frame, some do not. The logic can be constructed much easier if they are ALL in frames. Then you can check the frames themselves.

For Each Frame In Userform.Controls

[nardella]

Thanks guys for your imput. But, I'm probably not making myself clear.

The problem is not occuring when the form is initialized. Once open, as we navigate from tab (or from page to page within the form), a user may select from multiple tabs/pages.

I need the user to be able to select only one item from the entire form.

For example, we add frames to forms to limit the items selected to one.... I need this across frames/tabs/pages.

[lucas]

Thanks guys for your imput. But, I'm probably not making myself clear.

The problem is not occuring when the form is initialized. Once open, as we navigate from tab (or from page to page within the form), a user may select from multiple tabs/pages.

I need the user to be able to select only one item from the entire form.

For example, we add frames to forms to limit the items selected to one.... I need this across frames/tabs/pages.

We understand and Gerry gave you the solution:


As for the issue of a option button on one Tab, clearing off any others on other Tabs...well, it is brute force I'm afraid. You have to check ALL the others and clear them. You can only have one option button set on each frame, true. But you have multiple frames, and as you see, they are independent of the other frames. It is going to take careful and tedious logic. It is not difficult, but it takes some effort.

I would also suggest being consistent. Some tabs have the controls in a frame, some do not. The logic can be constructed much easier if they are ALL in frames. Then you can check the frames themselves.

For Each Frame In Userform.Controls

[fumei]

Yup.

"For example, we add frames to forms to limit the items selected to one.... I need this across frames/tabs/pages."

Indeed, using a frame limits the checkboxes inside THAT frame to one selected. However, this logic does not apply to any other frames. Thus - as I stated - you will have to brute force the checking and clearing. AND, it is easier if all of them are in frames. Again....

For Each Frame In Userform.Controls

In other words, say you got your five tabs, and each tab has one frame. You have to check if frame1 (on tab 1) has any checkbox = true. If yes, then, clear all the others.

If not...check if frame2 (on tab 2) has any checkbox = true. if yes, then, clear all the orthers.

Brute force I'm afraid.

[SamT]

Not tested.
This should initialize the complete form when any page is selected.

Sub MultiPage1_Change()
UserForm_Initialize
End Sub



Private Sub UserForm_Initialize()
Dim ctrl As Control
For Each ctrl In frmEquationsGallery.Controls
If TypeName(ctrl) = "OptionButton" Then
ctrl.Value = False
End If
Next ctrl
End Sub

[fumei]

Nice use of logic. Good one.

[SamT]

I think this is what the Tag Property is designed for






Sub MultiPage1_Change()
ClearOptions
End Sub







Private Sub ClearOptions()
Dim ctrl As Control

For Each ctrl In frmEquationsGallery.Controls
If ctrl.Tag = "ClearMe" Then ctrl.Value = False
Next ctrl

End Sub


It's a little more work to set up, but it leaves your options open to use OptionButtons that you don't want to reset. I think of the .Tag as the Programmer's Property.


ctrl1.Tag = "ClearMe"
ctrl2.Tag = "ClearMe ColorMe"
ctrl3.Tag = "ColorMe"

For each ctrl etc
If InStr(ctrl.Tag, "ColorMe") Then
yadah yadah

[fumei]

Also good, although I am not sure about using it with MultiPage_Change.

Say you have an OptionButton = True on Page3. Just the act of looking at Page2 - NOT changing any values, just switching to Page2 - will clear your value for that OptionButton. In other words, if Page3 optYadda = True, and you look at Page2, all buttons (assuming the "ClearMe" Tag), including the optYadda on Page3 become clear.

Maybe that is fine, maybe not.

[SamT]

Ah, well, the "MultiPage_Change" part was for the OP.

[fumei]

Yes, but does the OP want to make everything clear for ANY change? I think not.

Hmmmm, I stand corrected. Technically, yup, that seems to be what the OP is asking for, and yup, that will work. BUT...it is dependent on making another selection of an optionbutton. If you change anything (even looking at another page) everything is cleared. I still think this is not what the OP really wants, although yes, it seems to be what the OP is asking for.

[SamT]

Clear all other option buttons only when an option button is clicked

Lemmee dream a bit... ... ... ... ...


For every optionbutton

Sub OptionButton1_Change()
UserForm_Intialize
Me.Tag = "Not ME"
Reset_OptButs
End Sub

in the Form CodePage

Sub Reset_OptButs()
Dim ctrl As Control
For Each ctrl In frmEquationsGallery.Controls
If ctrl.Tag = "ClearMe" Then
ctrl.Value = False
End If
Next ctrl
End Sub

Private Sub UserForm_Initialize()
Dim ctrl As Control
For Each ctrl In frmEquationsGallery.Controls
If TypeName(ctrl) = "OptionButton" Then
ctrl.Tag = "ClearMe"
End If
Next ctrl
End Sub


I don't have excel open, so I probably misstated some property or method.