Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

All Frames and Optionbuttons in a UserForm

  Asked By: Lydia    Date: Oct 03    Category: MS Office    Views: 1419
  

I would like to loop thru all Frames in a UserForm and all of the OptionButton
be set to a False value.
What is incorrect with this VBA code ?

Dim opt As MSForms.OptionButton
Dim fra As MSForms.Frame, UFrm As Controls

Set UFrm = UserForm10.Controls
For Each fra In UFrm
For Each opt In fra
opt.Value = False
Next opt
Next fra

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Adalard Fischer     Answered On: Oct 03

I came up with a solution and it works!
Here is the VBA code  for anybody interested.
=======================================

Dim ctl As Control
Dim myOpt As MSForms.OptionButton
Dim myForm As MSForms.UserForm
Dim myTxt As MSForms.TextBox
Set myForm = UserForm10
For Each ctl In myForm.Controls
If InStr(1, ctl.Name, "Frame", vbTextCompare) Then
'MsgBox ("FrameName: " & ctl.Name)
'leave frame  asis
ElseIf InStr(1, ctl.Name, "TextBox", vbTextCompare) Then
'MsgBox ("TextBoxName: " & ctl.Name)
Set myTxt = ctl
myTxt.Text = ""
ElseIf InStr(1, ctl.Name, "Label", vbTextCompare) Then
'MsgBox ("LabelName: " & ctl.Name)
'leave label asis
ElseIf InStr(1, ctl.Name, "OptionButton", vbTextCompare) Then
'MsgBox ("OptionName: " & ctl.Name)
Set myOpt = ctl
myOpt.Value = False
End If
Next ctl

 
Answer #2    Answered By: Paulette Matthews     Answered On: Oct 03

However, there is a slight problem with it. You must name your
controls with a name beginning by "OptionButton" or "TextBox", etc...
If you change your naming convention let's say "Opt1" or "txtName"
for example, your code  is not going to work.
So, I would advise you to use the following:

For Each ctl In UserForm1.Controls
If TypeName(ctl) = "OptionButton" Then
ctl.Value = false
End If
Next ctl

 
Answer #3    Answered By: Wanda Patterson     Answered On: Oct 03

>However, there is a slight problem with it. You must name your
controls with a name beginning by "OptionButton" or "TextBox", etc...
If you change your naming convention let's say "Opt1" or "txtName"
for example, your code  is not going to work.<

HUH? Maybe I missed something, but that's not true. Heck, you can name
things DogPoop for all VBA cares...naming conventions are just a convenience
and I always use txtName, optName, etc and it works fine.

Is there a part of this I missed? Are we talking commands?

 
Answer #4    Answered By: Joel Brown     Answered On: Oct 03

I guess I was not clear enough.
Of course you can call your controls whatever you like and naming convention
are not a must. The problem is that Chuck was using the name property in order
to check the control type (i.e. if it's a label, a frame  or an optionBox,
etc...).
Chuck uses the following
If InStr(1, ctl.Name, "Frame", vbTextCompare) Then

In this case, his code  will work only if he calls his controls Frame1, Frame2
or FrameX, TextBox1or myTextBox, etc...

I suggested that he uses the typename function instead.

 
Answer #5    Answered By: Noel Peterson     Answered On: Oct 03

I was worried there for a minute that I'd been doing this wrong
for all these years!

 
Answer #6    Answered By: Iqbal Bashara     Answered On: Oct 03


(1)..I would rarely ever rename an object's name( i.e., Frame1, Frame2, ...,
Textbox1, Textbox2..., Label1, Label2,..., Optionbutton1, Optionbutton2... )

Within every Userform( .i.e., UserForm1, Userform2, ... ) , all of the above
object names are in sequential order and unique for each Userform.
However, would of course have a "Caption" name for each of these named objects.

I did try the sample code  from Geneviene and it didn't work too good.
I have a userform  initially with frames  ( i.e, Frame1, Frame2, Frame3, Frame4
and Frame5 )
Inside each of these frames contain the above named objects listed in (1),
including the option buttons.
The sample code provided by Genevieve wouldn't identify any of the option
buttons for each of the frames on the Userform...

 
Answer #7    Answered By: Bryson Smith     Answered On: Oct 03

You are making your life a LOT MORE DIFFICULT by not providing more
descriptive names that you can easily code  without having to worry that the
wrong item is being referenced!

 
Answer #8    Answered By: Minnie Romero     Answered On: Oct 03

I have tried my code  again and it works.

I created a user form with optionbuttons  (some inside a frame  and other
outside)
At design time, I changed the value of some of the optionbuttons to true (of
course only one optionbutton could be set  to true in each frame)
.
I run the code and all option buttons value was set to false  (this is what I
believed you requested).
I run the code and added a line with debug.print ctl.name
the loop  run through all the option buttons and the name of each one of them
appeared in the immediate window. which is the proof that each one of the
optionbuttons was indeed identified.

So, I guess I don't reallly understand what you are trying to do.

Of course, I did not mean that you renamed your controls "Frame1", etc... that
is exactly what I am trying to avoid.
The following is your code:
If InStr(1, ctl.Name, "Frame", vbTextCompare) Then
'MsgBox ("FrameName: " & ctl.Name)
Which means that you are checking the name of the control in order to know its
type. if your control is not named Frame but something like "myControl", you
will never be able to discover its type with this code.

I just meant to help you but if your code runs -as you said in one of your
last messages- so forget about mine.

 
Didn't find what you were looking for? Find more on All Frames and Optionbuttons in a UserForm Or get search suggestion and latest updates.




Tagged: