For Each Fuction???

[Dave_2k5]

I am buliding a questionnaire using Excel and I am using option buttons
and text boxs. I want to have a reset button which changes all the
option button values to false and the text boxes to nothing ("").

I don't want to have a really long code, One line for each option
button, as there are over 50 option buttons.

I have had a look at trying to do a For Each Statement, but couldn't
get it to work...Please Help!!!

Cheers,

Dave

 

[Bob Phillips]

Dim btn As OptionButton
Dim txt As TextBox
For Each btn In ActiveSheet.OptionButtons
btn.Value = False
Next btn

For Each txt In ActiveSheet.TextBoxes
txt.Text = ""
Next txt

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave_2k5]

Bob,

I have tried what you said, but when I press the button it doesn't do
anything, not even an error message...What I'm I doing wrong??? I am
using Excel 2000 would this affect anything??

Dave

 

[Guest]

Hi Dave

You were supposed to copy this code in between the Button_click() and the
End Sub lines of the button's code?

 

[Dave_2k5]

This is as it is written exactly in my code:

Private Sub reset_btn_Click()

Dim btn As optionbutton
Dim txt As TextBox
For Each btn In ActiveSheet.OptionButtons
btn.Value = False
Next btn

For Each txt In ActiveSheet.TextBoxes
txt.text = ""
Next txt


Range("C123,C127,C131").Select
Selection.Value = "Choose..."
Activewindow.ScrollRow = 1

End Sub

I put it in like this from the start and it still does nothing!!

Dav

 

[Dave_2k5]

This is as it is written exactly in my code:

Private Sub reset_btn_Click()

Dim btn As optionbutton
Dim txt As TextBox
For Each btn In ActiveSheet.OptionButtons
btn.Value = False
Next btn

For Each txt In ActiveSheet.TextBoxes
txt.text = ""
Next txt


Range("C123,C127,C131").Select
Selection.Value = "Choose..."
Activewindow.ScrollRow = 1

End Sub

I put it in like this from the start and it still does nothing!!

Dav

 

[Dave_2k5]

This is as it is written exactly in my code:

Private Sub reset_btn_Click()

Dim btn As optionbutton
Dim txt As TextBox
For Each btn In ActiveSheet.OptionButtons
btn.Value = False
Next btn

For Each txt In ActiveSheet.TextBoxes
txt.text = ""
Next txt


Range("C123,C127,C131").Select
Selection.Value = "Choose..."
Activewindow.ScrollRow = 1

End Sub

I put it in like this from the start and it does nothing!!

Dave

 

[Norman Jones]

Hi Dave,

If the OptionButtons and TextBoxes are from the Control Toolbox. try:

'=========================>>
Private Sub reset_btn_Click()

Dim oleObj As OLEObject

For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSforms.OptionButton Then
oleObj.Object.Value = False
ElseIf TypeOf oleObj.Object Is MSforms.TextBox Then
oleObj.Object.Value = ""
End If
Next

End Sub
'<<=========================

 

[Bob Phillips]

Is it a userform

Private Sub reset_btn_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.OptionButton Then
ctl.Value = False
ElseIf TypeOf ctl Is MSForms.TextBox Then
ctl.Text = ""
End If
Next ctl


Range("C123,C127,C131").Select
Selection.Value = "Choose..."
ActiveWindow.ScrollRow = 1

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave_2k5]

Norman, Bob

The Option Buttons and Text boxes are from the Toolbox. I have create
them directly on the first sheet in the workbook.

I have tried both of the codes you have sent:

Norman, the one you suggested has a runtime error of: Unable to get th
Object property of the OLEObject class.

Bob, the one you suggested has a Compiler Error: Method or data membe
not found.

Is there any more sujesstions out there...

Please HELP!!!

Dav

 

[Norman Jones]

Hi Dave,

To Retest, I added some Option buttons and 2 text boxes, together with a
command button, (from the Control Toolbox) to the first sheet of a new
workbook. I added some text to each text box and clicked an option button.

I inserted the body of my posted macro into the button's click event (in the
sheet module behind the first sheet).

Then, running the code from the button (or directly from the module),
cleared the selected option button and the text from each textbox without
problem.

I was unable to replicate your error.


You say that you had a problem with Bob's code but, as Bob indicated, his
code was designed for controls on a userform and this is not applicable to
the situation you describe.

BTW, I wouuld advocate changing my code line:

For Each oleObj In ActiveSheet.OLEObjects
to
For Each oleObj In Me.OLEObjects

Try running the test oulined above on a new workbook.

If, then, you are still experiencing the same problem, you may, if you wish,
send me a copy of your book. Remove any confidential data.


nXorman_jXones@btXconnectDOTcom

(replace dot and remove each X)

 

[Dave_2k5]

Norman,

I did exactly what you said to test it, and it worked perfectly.

When I used the exactly same code in the document I am working on, i
didn't.

I started to copy parts for my document into the working test, until
received the error message. When, I copied the title and company log
images over, it threw up the message. (The Title is just text in
merged cell, and the image was just a image inserted from a file.

I went back to my original file and deleted the images from either sid
of the Title and tried the button and it worked perfectly. I the
re-inserted the pictures and it still works.

I have know idea why the pictures would have made any difference, bu
it's working now thanks to your help.


Thank you for helping me with this,

Dav