Vba for Getting the control type....Please help!

[pedie]

Hi, i have seen this kinda code in the past however i cant locate it anymore...

If userform control.type = "Optionsbutton" then
rename it with some thing...
looping through all controls..


Thanks for helping!

 

[dave3009]

Use TypeName, like this

http://www.ozgrid.com/VBA/control-loop.htm

 

[meldoc]

Hi Pedie

Do you mean this?

http://www.mrexcel.com/forum/showthread.php?t=427103

 

[pedie]

Dave, perfect thanks alot...

I have done small modification to the code....


this leads to one more question....

[/FONT]
[FONT=Courier New]Option Explicit
Sub renamecaptions()
Dim i As Long
Dim cCont As Control
i = 1
For Each cCont In Me.Controls
        If TypeName(cCont) = "OptionButton" Then
          cCont.Caption = Sheets("Sheet4").Range("A" & i).Text
        End If
        i = i + 1
Next cCont
End Sub[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New][/FONT] 
[FONT=Courier New]Is there anything as activecontrol.caption..[/FONT]
[FONT=Courier New]Instead writing different line of code for all the optionbuttons?[/FONT]
 
[FONT=Courier New]Private Sub OptionButton1_Click()
Range("A1").Value = Me.OptionButton1.Caption
End Sub[/FONT]
[FONT=Courier New]Private Sub OptionButton2_Click()
Range("A1").Value = Me.OptionButton2.Caption
End Sub[/FONT]
[FONT=Courier New]Private Sub OptionButton3_Click()
Range("A1").Value = Me.OptionButton3.Caption
End Sub[/FONT]
[FONT=Courier New]Private Sub OptionButton4_Click()
Range("A1").Value = Me.OptionButton4.Caption
End Sub

 

[pedie]

Hi Pedie

Do you mean this?

http://www.mrexcel.com/forum/showthread.php?t=427103

Hi Brian, great..yes...thanks for showing me the link...
I didnt know which keyword to use to search now there are alot of result in google

Please check my second last post

 

[pedie]

Thanks again for the link Brian and Dave!!!
got it working....

Thanks again
Kind Regards
Pedie

[/FONT]
[FONT=Courier New]Sub loopandCheckvalues()
Dim cCont As Control
For Each cCont In Me.Controls
If TypeName(cCont) = "OptionButton" Then
 If cCont.Value = True Then
  Sheets("Sheet4").Range("B1").Value = cCont.Caption
 End If
End If
Next cCont
End Sub[/FONT]
[FONT=Courier New]Private Sub CommandButton1_Click()
 loopandCheckvalues
End Sub
Private Sub OptionButton1_Click()
loopandCheckvalues
End Sub
Private Sub OptionButton2_Click()
loopandCheckvalues
End Sub
Private Sub OptionButton3_Click()
loopandCheckvalues
End Sub
Private Sub OptionButton4_Click()
loopandCheckvalues
End Sub

 

[dave3009]

Or you could group them or put them in a frame

See here

http://support.microsoft.com/kb/213549

 

[pedie]

Thanks again Dave!
Really hepls

Or you could group them or put them in a frame

See here

http://support.microsoft.com/kb/213549