How to create loops in Vba

[smittal]

Hi all,


can anyone suggest the VBA code to create a loop depend on check box value. what exactly i want...


There are 50 check boxes on a sheet.. for true value of each check box its corresponding cell text get uploaded in db..as like


If .CheckBox1.Value = True Then


rs("Task_Desc") = Sheet4.Range("B37")

rs("Delivery_dt") = Sheet4.Range("G37")

rs.Update

rs.AddNew

End If


If .CheckBox2.Value = True Then

rs("Task_Desc") = Sheet4.Range("B38")

rs("Delivery_dt") = Sheet4.Range("G38")

rs.Update

rs.AddNew

End If


If .CheckBox3.Value = True Then

rs("Task_Desc") = Sheet4.Range("B39")

rs("Delivery_dt") = Sheet4.Range("G39")

rs.Update

rs.AddNew

End If.......


i don't want to repeat the same code at n times for n number of check box value


Many thanks in advance for same....

 

[ThrottleWorks]

Hi Smittal,


meanwhile someone helps you with the solution, could you please check the below links.


Might be usedful.


http://chandoo.org/wp/2011/08/30/variables-conditions-loops-in-vba/


http://chandoo.org/forums/topic/do-loop-with-cells


http://chandoo.org/forums/topic/do-loop

 

[SirJB7]

Hi, smittal!


Unfortunately VBA as difference with VB doesn't have controls array like CheckBox(i), so the workaround is this:

-----

[pre]

Option Explicit

Sub Looping()
' constants
Const kiFrom = 10
Const kiTo = 12
' declarations
Dim ctl(3) As Object
Dim I As Integer
' start
With ActiveSheet
Set ctl(1) = .CheckBox1
Set ctl(2) = .CheckBox2
Set ctl(3) = .CheckBox3
End With
' process
With ActiveSheet
For I = kiFrom To kiTo
If ctl(I - kiFrom + 1).Value Then
Debug.Print .Range("B" & I)
End If
Next I
End With
' end
End Sub

[/pre]
-----


If the controls were on a userform you should have used the collection Controls, testing each element Controls(i).Name against your checkbox pattern naming CheckBoxXXX.


Just advise if any issue.


Regards!

 

[NARAYANK991]

Hi Mittal ,


You can use a construct such as :

[pre]

For num = 1 to 50
If Activesheet.OLEObjects("Checkbox"&num).Object.Value then
' Processing code
Endif
Next

[/pre]
You can use the num counter to offset the range references.


Narayan

 

[SirJB7]

@NARAYANK991

Hi!

I didn't know that. Thanks for the tip.

Regards!