Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
In the following code, I'm attempting to make all my checkboxes clear t a .false state, using a for next loop, Instead of typing each box in. I'm new to vb and am unsure how I use a string variable with th checkbox objects. Have a look and see if you can help. Thanks Dennis Private Sub Cm1_Click() Dim x As String For c = 1 To 55 'Dim x As String x = "a" + c x.Value = False next c 'instead of below 'a1.Value = False 'a2.Value = False 'a3, a4, a5,... 't1.Text = "" End Su -- terrysoper197 ----------------------------------------------------------------------- terrysoper1973's Profile: http://www.excelforum.com/member.php...fo&userid=3016 View this thread: http://www.excelforum.com/showthread.php?threadid=49892 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
Are these checkboxes in a userform or checkboxes on a worksheet?
If they're on a worksheet, are they from the Forms toolbar or from the Control toolbox toolbar. On a Userform: Option Explicit Private Sub CommandButton1_Click() Dim ctrl As Control For Each ctrl In Me.Controls If TypeOf ctrl Is msforms.CheckBox Then ctrl.Object.Value = False End If Next ctrl End Sub On a worksheet from the Forms toolbar: Option Explicit Sub testme() Worksheets("Sheet1").CheckBoxes.Value = False End Sub One a worksheet from the Control Toolbox Toolbar: Option Explicit Private Sub CommandButton1_Click() Dim OLEObj As OLEObject For Each OLEObj In Me.OLEObjects If TypeOf OLEObj.Object Is msforms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub The me. keyword refers to the thing that owns the code--either the userform or the worksheet with the button and checkboxes. terrysoper1973 wrote: In the following code, I'm attempting to make all my checkboxes clear to a .false state, using a for next loop, Instead of typing each box in. I'm new to vb and am unsure how I use a string variable with the checkbox objects. Have a look and see if you can help. Thanks Dennis Private Sub Cm1_Click() Dim x As String For c = 1 To 55 'Dim x As String x = "a" + c x.Value = False next c 'instead of below 'a1.Value = False 'a2.Value = False 'a3, a4, a5,... 't1.Text = "" End Sub -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
The checkboxes are in a worksheet, not sure about the other. I've uploaded a sample for you. +-------------------------------------------------------------------+ |Filename: fool balance.zip | |Download: http://www.excelforum.com/attachment.php?postid=4191 | +-------------------------------------------------------------------+ -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
I don't use excelforum so I can't see the attachment.
But I wouldn't open an attachment anyway. (Lots of people won't.) Better to post in plain text. terrysoper1973 wrote: The checkboxes are in a worksheet, not sure about the other. I've uploaded a sample for you. +-------------------------------------------------------------------+ |Filename: fool balance.zip | |Download: http://www.excelforum.com/attachment.php?postid=4191 | +-------------------------------------------------------------------+ -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
To clear all the checkboxes can be done by putting the statment a1.value = false for each checkbox, however, there would be a lot of code if I need 100-200 checkboxes; so I'm asking if it is possible set up a for next loop that replaces the a1.value = false, a2.value = false, a3.value = false... with a simple sting that replaces the a# part of the object with an a(string).value = false, but I'm not sure about the syntax, or if it is even possible. Please help, and sorry about the file upload, we all have to be careful about what we open, won't happen again. The code below is used to simulate my excel sheet. Private Sub a1_Click() 'first check box test Worksheets("sheet1").t1.Text = "fool" End Sub Private Sub a2_Click() 'second check box in sheet Worksheets("sheet1").t1.Text = "fool2" End Sub Private Sub Cm1_Click() 'command button to make all checkboxes false 'works until x.value = false line, stops at the x 'For c = 1 To 2 'Dim x As String 'x = "a" + c 'x.Value = False 'next c 'works 'a2.Value = False 'a3, a4, a5,... 't1.Text = "" End Sub Private Sub Worksheet_Activate() 'works a1.Value = False a2.Value = False End Sub -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
You can do something like the following:
Dim Ndx As Long For Ndx = 1 To 2 UserForm1.Controls("A" & Ndx).Value = False Next Ndx Or, to clear all checkboxes, Dim Ctrl As MSForms.Control For Each Ctrl In UserForm1.Controls If TypeOf Ctrl Is MSForms.CheckBox Then Ctrl.Value = False End If Next Ctrl -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "terrysoper1973" <terrysoper1973.21a5pm_1136663701.3341@excelforu m-nospam.com wrote in message news:terrysoper1973.21a5pm_1136663701.3341@excelfo rum-nospam.com... To clear all the checkboxes can be done by putting the statment a1.value = false for each checkbox, however, there would be a lot of code if I need 100-200 checkboxes; so I'm asking if it is possible set up a for next loop that replaces the a1.value = false, a2.value = false, a3.value = false... with a simple sting that replaces the a# part of the object with an a(string).value = false, but I'm not sure about the syntax, or if it is even possible. Please help, and sorry about the file upload, we all have to be careful about what we open, won't happen again. The code below is used to simulate my excel sheet. Private Sub a1_Click() 'first check box test Worksheets("sheet1").t1.Text = "fool" End Sub Private Sub a2_Click() 'second check box in sheet Worksheets("sheet1").t1.Text = "fool2" End Sub Private Sub Cm1_Click() 'command button to make all checkboxes false 'works until x.value = false line, stops at the x 'For c = 1 To 2 'Dim x As String 'x = "a" + c 'x.Value = False 'next c 'works 'a2.Value = False 'a3, a4, a5,... 't1.Text = "" End Sub Private Sub Worksheet_Activate() 'works a1.Value = False a2.Value = False End Sub -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
Dim Ndx As Long For Ndx = 1 To 2 UserForm1.Controls("A" & Ndx).Value = False Next Ndx My checkboxes are in the workbook and not on a form. I'm just not understanding the code. I tried to type the code in but got an error message "type missmatch" So I tried the following Dim Ndx As Long For Ndx = 1 To 2 worksheets("sheet1").Controls("A" & Ndx).Value = False Next Ndx Well maybe I get marks for creativity I may be in over my head. So if you think your waisting your time, I won't be offended, but I do welcome your help. Thanks Dennis -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
Did you use the checkbox from the control toolbox toolbar or did you use the
checkboxes from the Forms toolbar? terrysoper1973 wrote: Dim Ndx As Long For Ndx = 1 To 2 UserForm1.Controls("A" & Ndx).Value = False Next Ndx My checkboxes are in the workbook and not on a form. I'm just not understanding the code. I tried to type the code in but got an error message "type missmatch" So I tried the following Dim Ndx As Long For Ndx = 1 To 2 worksheets("sheet1").Controls("A" & Ndx).Value = False Next Ndx Well maybe I get marks for creativity I may be in over my head. So if you think your waisting your time, I won't be offended, but I do welcome your help. Thanks Dennis -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
for each obj in ActiveSheet.OleObjects
if typeof Obj.Object is MSForms.CheckBox then obj.Object.Value = False end if Next or if the names of the checkboxes are of the form A# then for i = 1 to 10 Activesheet.OleObjects("A" & i).Object.Value = False Next The above is for checkboxes from the control toolbox toolbar. If from the forms toolbar for each cbox in Activesheet.CheckBoxes cbox.Value = xlOff Next or for i = 1 to 10 activesheet.Checkboxes("A" & i).Value = xlOff Next or if only 10 checkboxes for i = 1 to 10 activesheet.Checkboxes(i).Value = xloff Next -- Regards, Tom Ogilvy "terrysoper1973" <terrysoper1973.21ahza_1136679602.1953@excelforu m-nospam.com wrote in message news:terrysoper1973.21ahza_1136679602.1953@excelfo rum-nospam.com... Dim Ndx As Long For Ndx = 1 To 2 UserForm1.Controls("A" & Ndx).Value = False Next Ndx My checkboxes are in the workbook and not on a form. I'm just not understanding the code. I tried to type the code in but got an error message "type missmatch" So I tried the following Dim Ndx As Long For Ndx = 1 To 2 worksheets("sheet1").Controls("A" & Ndx).Value = False Next Ndx Well maybe I get marks for creativity I may be in over my head. So if you think your waisting your time, I won't be offended, but I do welcome your help. Thanks Dennis -- terrysoper1973 ------------------------------------------------------------------------ terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161 View this thread: http://www.excelforum.com/showthread...hreadid=498925 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
string
Thanks for your help everyone. Your all in my prayers. Thanks Denni -- terrysoper197 ----------------------------------------------------------------------- terrysoper1973's Profile: http://www.excelforum.com/member.php...fo&userid=3016 View this thread: http://www.excelforum.com/showthread.php?threadid=49892 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
counting the number of instances of a string within another string | Excel Worksheet Functions | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Create a formula into a String then assign string to a cell | Excel Programming |