Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Finding Text in Groupitems

0

Hello Guys,

I was wondering what i do wrong here?

Because it don't count all the 'marked' groupitems

It just count them all :(

'bPic is a Label
Sub ClickCheckBoxen()

    Dim iCheckCount        As Long
    iCheckCount = 0
    Dim cardid             As Long
    
    cardid = Replace(Application.Caller, "bPic", "")

If StartsWith("Card", "Card") = True Then
If StartsWith("bPic", "bPic") = True Then

If Shapes("Card" & cardid).GroupItems("bPic" & cardid).TextFrame2.TextRange.Text = "þ" Then
    Shapes("Card" & cardid).GroupItems("bPic" & cardid).TextFrame2.TextRange.Text = "¨"
Else
    Shapes("Card" & cardid).GroupItems("bPic" & cardid).TextFrame2.TextRange.Text = "þ"
End If

End If
End If
    
End Sub

Thank you.

Answer
Discuss

Answers

0
Selected Answer

Ghost

Your With statement below works if the named shape is that of a group (not an item within that group). I created some shapes and named the textbox shape like "bPic1" then grouped them into 3 groups. I modified your code section as follows:

On Error Resume Next
With Sheet1.Shapes("Group " & Numb)
        If .GroupItems("bPic" & Numb).TextFrame2.TextRange.Text = "þ" Then 'Has been checked/marked like a checkbox
              iCheckCount = iCheckCount + 1
        End If
 End With
and it correctly counted 1 (since only one of the three groups had þ as text). 

If however I had a group item shape name in the With statement (e.g. "Rectangle"&Numb) then 3 were counted i.e. all shapes (like you found).

Further to the file attached to your question, please see the revised code below, key changes in bold and commented to show an approach of looping through shapes*:

Sub countselected()
    Dim Numb                    As Long
    Dim iCheckCount, lastrow, cardid      As Long
    Dim FoundMemb               As Range
    Dim Shp As Shape
    iCheckCount = 0
    lastrow = Tasks.Range("A99999").End(xlUp).Row

If lastrow < 3 Then Exit Sub

For Numb = 3 To lastrow ' For 3 to last row in Tasks...

    On Error Resume Next
    For Each Shp In Sheet1.Shapes ' loop through shapes to see if there's a name match
        If Shp.Name = "Card" & Numb Then 'if so..
            With Shapes("Card" & Numb) 'check ...
                    If .GroupItems("bPic" & Numb).TextFrame2.TextRange.Text = "þ" Then '..it has been checked/marked like a checkbox
                          iCheckCount = iCheckCount + 1 ' if so, increase the count
                    End If
            End With
        End If
    Next Shp
Next Numb

MsgBox iCheckCount ' report ticks

End Sub
* I wasn't sure if your real code will check against numbered tasks in your worksheet Tasks (it doesn't now).

Hope this helps.

Discuss

Discussion

Hello John_Ru,

Thanks for your help
But it didn't work
I have added my demo workbook
Hope you can find what i do wrong

i tried to use Card didn't work and i also used instead CardBack but also
didn't work.. makes me a little confused about it.

Thank you
GhostofWanted (rep: 46) Aug 25, '21 at 12:57 pm
Ghost- try the code in my revised Answer. If there's no link between the cell contents in Tasks and your shapes, just loop through the shapes once and count the ticks!
John_Ru (rep: 6102) Aug 25, '21 at 1:35 pm
Oh thanks John_Ru
It works like a charm now :D
Also updated the code with workbook code.

You are amazing John_Ru ;)
GhostofWanted (rep: 46) Aug 25, '21 at 2:38 pm
Thanks Ghost, I've editted my answer to remove the reference to the discrepancy but just realised that your question has only the code for the other macro, not the sub countselected
John_Ru (rep: 6102) Aug 25, '21 at 2:57 pm
Yes i know John_Ru :) . Seems the countselected seems working ok
For what i needed to do. But the biggest problem has been solved ;)
Thanks again.
GhostofWanted (rep: 46) Aug 25, '21 at 3:12 pm
I'm confused now- is there also a problem with the ClickCheckBoxen macro?
John_Ru (rep: 6102) Aug 25, '21 at 4:53 pm
Hello John_Ru,
No, i'm sure the code can do better in the context but
It works without troubles so it's fine ;)
Sorry for making you confused about it.
GhostofWanted (rep: 46) Aug 25, '21 at 10:18 pm
Ok
John_Ru (rep: 6102) Aug 26, '21 at 1:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login