so simple, yet so difficult (checkbox selection)

[gotskill10]

Hey guys,

Im pretty decent with vba. I'm having a problem.

Im trying to run a FOR loop and go through my checkboxes which are named
"graph1" ,"graph2","graph3"...

basically I need to be able to call the checkbox from the worksheet, they arent in a form.

I've been trying activesheet.shapes("graph1").selection and thats not working
Ive also tried sheet1.checkboxes and sheet1.checkbox. Its really stumped me.

I know I need to be able to take "graph" & i and put that into the checkbox call. What should I do?

[Bruce Fox]

G'Day gotskill10, Welcome to the Forum

Here is a simple NON ROBUST way - get you going at least (for Excel).

Dim objShp As Shape
 
    For Each objShp In ActiveSheet.Shapes
        If InStr(objShp.Name, "graph") <> 0 Then
            'CheckBox
            MsgBox objShp.Name
        End If
    Next

Or

Dim objShp As Shape
 
    For Each objShp In ActiveSheet.Shapes
        If Left$(LCase(objShp.Name), 5) = "graph" Then
            'CheckBox
            MsgBox objShp.Name
        End If
    Next

I will post back a better solution soon

[gotskill10]

thanks for the help. I tried out your solution. I was able to get it to cycle through those checkboxes, however, how can i reference the checkboxes value (either true or false)?

i tried sheet1.objshp
or sheet1.checkbox(objshp.name)

How else can I do this?

[bushmobile]

use objShp.Value

[gotskill10]

i tried that as well, it doesnt work, there is no value to the shapes library

[Bruce Fox]

Here you go... (note: I changed the Variable Name to better reflect Hungarian Notation - now shpObj):

Dim shpObj As Shape
 
    For Each shpObj In ActiveSheet.Shapes
        If InStr(shpObj.Name, "graph") <> 0 Then
            'CheckBox
            MsgBox shpObj.Name
            [b]MsgBox OLEObjects(shpObj.Name).Object.Value[/b]
        End If
    Next

Or

Dim shpObj As Shape
 
    For Each shpObj In ActiveSheet.Shapes
        If Left$(LCase(shpObj.Name), 5) = "graph" Then
            'CheckBox
            MsgBox shpObj.Name
            [b]MsgBox OLEObjects(shpObj.Name).Object.Value[/b]
        End If
    Next

And using the OLEObjects Collectio (opposed to Shapes):

Dim oleObj As OLEObject
 
    For Each oleObj In OLEObjects
        If Left$(LCase(oleObj.Name), 5) = "graph" Then
            'CheckBox
            MsgBox oleObj.Name
            MsgBox OLEObjects(oleObj.Name).Object.Value
        End If
    Next

[gotskill10]

Here you go... (note: I changed the Variable Name to better reflect Hungarian Notation - now shpObj):

Dim shpObj As Shape
 
    For Each shpObj In ActiveSheet.Shapes
        If InStr(shpObj.Name, "graph") <> 0 Then
            'CheckBox
            MsgBox shpObj.Name
            [b]MsgBox OLEObjects(shpObj.Name).Object.Value[/b]
        End If
    Next

Or

Dim shpObj As Shape
 
    For Each shpObj In ActiveSheet.Shapes
        If Left$(LCase(shpObj.Name), 5) = "graph" Then
            'CheckBox
            MsgBox shpObj.Name
            [b]MsgBox OLEObjects(shpObj.Name).Object.Value[/b]
        End If
    Next

And using the OLEObjects Collectio (opposed to Shapes):

Dim oleObj As OLEObject
 
    For Each oleObj In OLEObjects
        If Left$(LCase(oleObj.Name), 5) = "graph" Then
            'CheckBox
            MsgBox oleObj.Name
            MsgBox OLEObjects(oleObj.Name).Object.Value
        End If
    Next

I tried this, none of them work, for MsgBox OLEObjects(shpObj.Name).Object.Value, there is no object in the oleobjects, and i looked at the oleobjects library, there is no way to get a value.

With your last code:

For Each oleObj In OLEObjects

It gives me an error here, even with the dim oleobj as oleobject.

Are there any other solutions?

[Hack]

What error did it give you?

[gotskill10]

for MsgBox OLEObjects(shpObj.Name).Object.Value

it says "sub or function not defined," but thats because of the .object.value, if you put OLEObjects(shpObj.Name).select, it works, but doesn't let me do selection.value.


on

Dim oleObj As OLEObject

For Each oleObj In OLEObjects
If Left$(LCase(oleObj.Name), 5) = "graph" Then
'CheckBox
MsgBox oleObj.Name
MsgBox OLEObjects(oleObj.Name).Object.Value
End If
Next

I get an object required error on For Each oleObj In OLEObjects, I have the Dim oleObj As OLEObject in there.

[Bruce Fox]

What version of Excel are you running? I successfully tested on Office 2003.

[gotskill10]

im using excel 2003, sp2 which exact code did you try?

[Bruce Fox]

All.....

I re test.

EDIT: Works....
Do you get an intellisense of OLEObjects when you Dim x As [[[OLEO.....]]]