For Each Fuction???
 

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 :confused:


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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" wrote in
message ...

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 :confused:


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile:
http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

Hi Dave

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

--
ve_2nd_at. Randburg, Gauteng, South Africa


"Dave_2k5" wrote:


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


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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!!

Dave


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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!!

Dave


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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
'<<=========================

---
Regards,
Norman



"Dave_2k5" wrote in
message ...

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


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile:
http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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" wrote in
message ...

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


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile:
http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

Norman, Bob

The Option Buttons and Text boxes are from the Toolbox. I have created
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 the
Object property of the OLEObject class.

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

Is there any more sujesstions out there...

Please HELP!!!

Dave


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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)


---
Regards,
Norman



"Dave_2k5" wrote in
message ...

Norman, Bob

The Option Buttons and Text boxes are from the Toolbox. I have created
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 the
Object property of the OLEObject class.

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

Is there any more sujesstions out there...

Please HELP!!!

Dave


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile:
http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835

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, it
didn't.

I started to copy parts for my document into the working test, until I
received the error message. When, I copied the title and company logo
images over, it threw up the message. (The Title is just text in a
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 side
of the Title and tried the button and it worked perfectly. I then
re-inserted the pictures and it still works.

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


Thank you for helping me with this,

Dave


--
Dave_2k5
------------------------------------------------------------------------
Dave_2k5's Profile: http://www.excelforum.com/member.php...o&userid=19667
View this thread: http://www.excelforum.com/showthread...hreadid=389835