Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- 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 |
#2
|
|||
|
|||
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 -- 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 |
#3
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Fuction | Excel Worksheet Functions | |||
IF fuction and format ... | Excel Worksheet Functions | |||
Page Number Fuction | Excel Discussion (Misc queries) | |||
how can I exceed the nested if fuction limit | Excel Discussion (Misc queries) | |||
how can I exceed the nested if fuction limit | Excel Discussion (Misc queries) |