[RESOLVED] Question regarding Controls

[Sacto95827]

Presently, I have a number of checkbox controls appropriately named:

CheckBox1
CheckBox2
CheckBox3
CheckBox4
etc.

Is there a way to use and array and loop to assign the name of the control instead of continually put a bunch of if's and else's. For example, this is what I have now:

If ActiveCell.Value = "NC" Then
     CheckBox1.Value = False
Else
     CheckBox1.Value = True
End If
 
If ActiveCell.Value = "NC" Then
     CheckBox2.Value = False
Else
     CheckBox2.Value = True
End If

I can put the rest of the If's and Else's...but that's just bad programming. Any suggestions would be greatly appreciated!

[iPrank]

Hi Sacto95827 ! Welcome to the forums.

Consider using a control array instead of multiple separate checkboxes.
If you use control array, you can use the Index property to set values very easily.

These two tutorials may help you:
1. VBExplorer Array Tutorial

2. Control Arrays In VB6

After reading them read this very interesting tutorial : Creating Controls At Runtime.

Also you may want to use Select-Case instead of nested Ifs.

[RobDog888]

Welcome to the Forums.

Loop through the OLEObjects colection for the checkboxes on your Excel Sheet.

Dim i As Integer
For i = 1 To Sheet1.OLEObjects.Count
    If Left$(Sheet1.OLEObjects(i).Name, 8) = "CheckBox" Then
        If Sheet1.OLEObjects(i).Object.Value = False Then
            Debug.Print Sheet1.OLEObjects(i).Name
        End If
    End If
Next

[iPrank]

Is it an Excel question ?

Sorry.

[RobDog888]

I think so as hes using the "CheckBox" control which is an Office control name vs. "Check" which is VB 6 control name. Plus, hes refering to the "ActiveCell" as in an Excel spreadsheets ActiveCell property. I could be wrong though.

[SAGremlin]

Presently, I have a number of checkbox controls appropriately named:
Is there a way to use and array and loop to assign the name of the control instead of continually put a bunch of if's and else's. For example, this is what I have now:

Any suggestions would be greatly appreciated!

Rename the first toCheckBox Make it's Index Property to '0'..
Now rename each of your checkboxes to CheckBox , The index will automaticaly increment on each change..

Now you have an array of Checkbox..

check and change with

Dim Loop_1 as long
For Loop_1 = 0 To Ubound(CheckBox)
If ActiveCell.Value = "NC" Then
     CheckBox(Loop_1).Value = False
Else
     CheckBox(Loop_1).Value = True
End If
Next Loop_1

Hope this helps..

Gremmy.....

[RobDog888]

Hey Gremmy, wont that cause a Loop without Do error having Loop as a variable name?

[SAGremlin]

I think so as hes using the "CheckBox" control which is an Office control name vs. "Check" which is VB 6 control name. Plus, hes refering to the "ActiveCell" as in an Excel spreadsheets ActiveCell property. I could be wrong though.

No Its CheckBoxn In VB 6 too...

Gremmy..

[RobDog888]

Did you verify that? "Check1" for me when adding a new check box to my form.

[SAGremlin]

Ahh thanks... Fixed it in original post....

[RobDog888]

No prob.

Hey, I thought I recognized you. Your GremlinSA the Invader!

Sup!

[SAGremlin]

Did you verify that? "Check1" for me when adding a new check box to my form.

Yes.. In VB 5 and VB 6.. (Both Pro Editions...)

100% sure...

Gremmy...

[SAGremlin]

No prob.

Hey, I thought I recognized you. Your GremlinSA the Invader!

Sup!

Now what gave you that idea ????? The Avatar, The signature, or the Nick ...

It took 4 Days before someone recognised me....

Gremmy...

[Sacto95827]

RobDog888 is right, I am using Excel. Come to think of it...I slightly recall using control arrays now when I went to school...ouch...that was a long time ago. But I don't think Excel gives you the ability to implement a control array...

[RobDog888]

No, I thought you had registered as the same user name as at CG. GremlinSA. Guess your were a bit dyslexic when you registered.

[RobDog888]

RobDog888 is right, I am using Excel. Come to think of it...I slightly recall using control arrays now when I went to school...ouch...that was a long time ago. But I don't think Excel gives you the ability to implement a control array...

No, you cant do it the VB6 way of Copy the control and paste it to invoke an array but as Gremmy posted you can use its .Index property for the same effect.


w00t w00t! I was right.

[SAGremlin]

No, I thought you had registered as the same user name as at CG. GremlinSA. Guess your were a bit dyslexic when you registered.

No.. Just under cover... (Let's move this To Chit Chat...)

Gremmy...

[Sacto95827]

No, you cant do it the VB6 way of Copy the control and paste it to invoke an array but as Gremmy posted you can use its .Index property for the same effect.


w00t w00t! I was right.

I checked all properties...no Index properties...and when I manually attempt to assign the Index property in VB...it doesn't allow me to do it...am I missing something?

[SAGremlin]

I checked all properties...no Index properties...and when I manually attempt to assign the Index property in VB...it doesn't allow me to do it...am I missing something?

Lets try to clear this up..

Please post what software your using.. (VB, Execel VBA.. Windows) and if posible the Versions...

Gremmy..

------ Edit -----

Ok ignore this post...

I just saw your using Excel VBA .. Still what ver??

------ End Edit ------

[Sacto95827]

Lets try to clear this up..

Please post what software your using.. (VB, Execel VBA.. Windows) and if posible the Versions...

Gremmy..

------ Edit -----

Ok ignore this post...

I just saw your using Excel VBA .. Still what ver??

------ End Edit ------

Excel 2003 (11.6355.6568) SP1
VB 6.3 Version 9972

[SAGremlin]

Excel 2003 (11.6355.6568) SP1
VB 6.3 Version 9972

I just double checked this in Excel 2000.. Definitely no index.. Some others are not there either.. ( I didn't know that VBA was that far stripped down) so you will have to use RobDogs OleObjects method..

Gremmy.

[RobDog888]

Ok, are these checkboxes from the Forms toolbar or from the Control Toolbox toolbar?

[Sacto95827]

Ok, are these checkboxes from the Forms toolbar or from the Control Toolbox toolbar?

Control Toolbox Toolbar...

[RobDog888]

Then code from post #3 or #6 should work behind the sheet with the checkboxes on it.

[Sacto95827]

Thanks all!