excel vba and checkboxes 2

[taupirho]

Say I have an EXCEL 97 spreadsheet set up with numerous checkbox
controls, cb_1, cb_2 etc ... I'd like to be able to write a procedure
which takes the name of the checkbox and sets it value to 1. The
checkboxes are on the sheet not a userform so I don't appear to be
able to access a controls collection to do what I want. Any help is
appreciated.

e.g what I want is something like:-

' calling procedure
private sub setallchecks()
for i ....
setcheck "cb_" & str(i)
next
end sub

'called procedure
private sub setcheck(cb as string)
'here I want to set the value of the checkbox identified by cb to 1
end sub

 

[xlbo]

Are the checkboxes from the "Forms" toolbar or the "Controls Toolbox" toolbar ?? - different syntax for each.....

forms:
For Each shp In ActiveSheet.DrawingObjects
If TypeName(shp) = "CheckBox" Then
shp.Value = 1
Else
End If
Next

Controls:
for each olo in activesheet.oleobjects
if left(olo.name,2) = "cb" then
olo.value = 1
else
end if
next Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[taupirho]

Geoff, close but no cigar.

I'm using "Controls Toolbox" checkboxes and the code you've shown doesn't work. It seems that you can't set the value of an oleobject. Any other suggestions?

 

[Nikita6003]

Hiya,

I've had the same problem as you, tomreid. Problem iseems to be the way in which you use these boxes.

Excel gives you several options for referring to them:
by name
using the SHAPES object
using the OLOEOBJECT object

Both SHAPES and OLEOBJECTS have NO Value property
Only thing to try is to refer to the checkboxes by name - but you need to specify the sheet the boxes are on as well.
That means there's no looping thru' the collection of textboxes - you'll need a separate line per box (icky!)

If there's anyone out there with a suggestion (Geoff???? You usually manage to come up with very useful snippets of code ;-)) I'd be pleased to hear. I've changed my checkboxes back to the form type coz' I don't need events, but it'd be nice to have a solution
(and it's probably staring me RIGHT in the face, too - just hidden behind Micorsoft's LOGICAL naming conventions ... )

Cheers
Nikki

 

[xlbo]

Thanx for the compliment Nikki - I believe I have come up with a workaround
To start with, all the checkboxes MUST have a linked cell - this can be set in the "properties" box

Then, providing you have a naming convention (in this case cb_Number), this works:

For Each OLEObj In ActiveSheet.OLEObjects
If Left(OLEObj.Name, 2) = "cb" Then
Range(OLEObj.LinkedCell).Value = 1
Else
End If
Next

Took some playing with tho - I tried the BoundValue as well as help seems to suggest that this CAN be set thru code but......naaah. The above method is the only way I have found that works....anyone else care to try ?? Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[taupirho]

The following code kind of worked for me. Trouble is it only did it for about 80% of the checkboxes, the rest were left unchecked. Any ideas why??
Also noticed that although I've called my checkboxes cb_1, cb_2 etc... In the code below the name of them always equated to CheckBox1, CheckBox2 etc ... Is this a bug?
Anyway here goes with the code, any improvements welcome.

for i = 1 to oleobjects.count
if left(oleobjects.item(i).name,8) = "CheckBox" then
oleobjects.item(i).object.value = true
end if
next

 

[taupirho]

Doh! Spot the deliberate mistake.
I guess by now you guys realise I'm fairly
new to VBA.
Take the i=i+1 from my previous code snippet and all works as expected.

 

[taupirho]

Doh again !!. There was no i = i + 1 in my previous snippet!!

I copied down the correct code from an incorrect listing - arghhh!!!!

So....... the original code snippet should be correct

 

[Nikita6003]

brilliant!

thanks Geoff - worth a star
& thanks tom for posting ...

 

[taupirho]

Still looking for answer as to why the name defaults to CheckBox1,2,3... instead of the given name??

 

[combo]

If you have a control with certain name - you can't assign this name to another control on the sheet. A name means also cell address (cb1, a1 etc.) or named ranges of cells. If you try to use the name twice, you will jump to already existing object with this name (cell, control) without changing the default name.

As for your code - I think that the shortest is based on xlbo's first suggestion:
[tt]for each olo in activesheet.oleobjects
if left(olo.name,2) = "cb" then
olo.object.value = true
end if
next[/tt]

Generally, ActiveSheet is not a good reference to sheet object unless you are 110% sure the your macro starts when the sheet is active. (I prefer to use sheet's codename (a (Name) in brackets in Properties window when sheet is selected in Project Explorer).) May be this is why the macro ommits some controls. (To check it, I would add [tt] msgbox olo.name[/tt] before If statement.)

 

[combo]

I work with excel XP and here is only one Name property. However, in the help file (probably obsolete) I found subject 'Using ActiveX Controls on Sheets'. MS distinguish here code name (set in properties window, as (Name)) and name, set in sheet's name window or Name property. By default they are the same, but changing one do not change the other.
This can take place in your excel 97 and may be 2000.

 

[Nikita6003]

Yup. I've gotten into the habit of naming an object both in the property window and in the name box on the formula bar - this ensures you and your code are using the names consistently

And tomreid - based on xlbo's suggestion I came up with the following: I've set up a little routine which'll set the LinkedCell property of an ActiveX checkbox to its TopLeftCell (i.e the top-and-leftmost cell where the checkbox has been put)
The routine then sets the numberformat to ;;; - i.e. invisible

Then I added 2 commandbuttons to my sheet, which'll set the value of the LInkedCells of all my checkboxes to TRUE or FALSE respectively. Works a treat

Private Sub SetAllCheckBoxLinkedCells()
    Dim l_wksCheckBoxSheet As Worksheet
    Dim l_chkCheckBox As OLEObject
    
    Set l_wksCheckBoxSheet = ThisWorkbook.Sheets("Control")
    
    'Loop through ActiveX collection
    For Each l_chkCheckBox In l_wksCheckBoxSheet.OLEObjects
        'If we've found a checkbox ...
        If l_chkCheckBox.ProgId = "Forms.CheckBox.1" Then
            ' ... set its linkedcell property to the TopLeft Cell where the checkbox = placed
            l_chkCheckBox.LinkedCell = l_chkCheckBox.TopLeftCell.Address(False, False)
            'and set number formatting for that cell to INVISIBLE
            '(For some reason I use this rather than TextColor = White)
            'Can't remember why, though ...
            'Use the LinkedCell property - this returns a string - as cell address for the range object
            l_wksCheckBoxSheet.Range(l_chkCheckBox.LinkedCell).NumberFormat = ";;;"
        End If
    Next l_chkCheckBox
    
    Set l_wksCheckBoxSheet = Nothing
    
End Sub

Private Sub cmdCheckChkBoxes_Click()
    Dim l_wksCheckBoxSheet As Worksheet
    Dim l_chkCheckBox As OLEObject
    
    Set l_wksCheckBoxSheet = ThisWorkbook.Sheets("Control")
    
    'Loop through ActiveX collection
    For Each l_chkCheckBox In l_wksCheckBoxSheet.OLEObjects
        'If we've found a checkbox set its value to CHECKED = TRUE = -1
        If l_chkCheckBox.ProgId = "Forms.CheckBox.1" Then l_wksCheckBoxSheet.Range(l_chkCheckBox.LinkedCell).Value = -1
    Next l_chkCheckBox
    
    Set l_wksCheckBoxSheet = Nothing
End Sub

Private Sub cmdClearChkBoxes_Click()
    Dim l_wksCheckBoxSheet As Worksheet
    Dim l_chkCheckBox As OLEObject
    
    Set l_wksCheckBoxSheet = ThisWorkbook.Sheets("Control")
    
    'Loop through ActiveX collection
    For Each l_chkCheckBox In l_wksCheckBoxSheet.OLEObjects
        'If we've found a checkbox set its value to UNCHECKED = FALSE = 0
        If l_chkCheckBox.ProgId = "Forms.CheckBox.1" Then l_wksCheckBoxSheet.Range(l_chkCheckBox.LinkedCell).Value = 0
    Next l_chkCheckBox
    
    Set l_wksCheckBoxSheet = Nothing
    
End Sub

Cheers
Nikki

 

[combo]

Nikki,
your code work fine, but it is not necessary to have linked cell to change value of toolbox's ActiveX object on the sheet.
As tomired pointed above, when you add "Object" to the reference all works fine.
I used this to set all checkboxes on sheet to 'checked' when selection changes, and all works (code in sheet's module):

[tt]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Olo As OLEObject
For Each Olo In Me.OLEObjects
If Olo.progID = "Forms.CheckBox.1" Then
Olbject.Value = True
End If
Next
End Sub[/tt]

Combo

 

[Nikita6003]

Yup, I know (& did notice, actually) .. but there's more than 1 way to skin a cat ;-)
As I've loads of old tools that already use the LinkedCell extensively in the code, it's just as easy to set up the two routines above - the advantage for me = it makes it easier to understand for the ppl using the tools. They'll take over ownership/maintenance of the code once I leave the project

Cheers
Nikki

 

[xlbo]

Reckon Combo gets a star - pretty much the shortest code solution for the answer to the thread and a nice easy way to loop thru a collection of activeX objects Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[combo]

Thanks Geoff, however the piece of code is composed of yours, Nikki's and Toni's ideas.
A synergy effect .

 

[xlbo]

Yeh - but you put it together and that's what I'm keeping in my personal.xls - therefore a star (I'd give Nikki a star as well but she's got too many as it is ;-) ) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

heck i don't deserve any stars anyway
just gimme a beer a star - that'll keep me 'appy

 

[Nikita6003]

Cheers!


Nikki