Count Chars/Words from Comments and TextBox VBA

7absinth · May 17th 2016

I have the following code that works separately perfect, but when I combined both code parts (comments and text box char/words counting parts) together it pops out the following error WHEN I ADD ANY COMMENT:
445: Object doesn't support this action
Line: If TypeName(shp) <> "GroupObject" And shp.TextFrame2.TextRange.Characters.Text <> "" Then

I cant figure out WHY? I cant imagine any other method than this. Below I have added the combined code. I hope somebody have the right answer to this issue.

Code

Sub CountCharWorBOXCMT()
'Counts Comments and Text Box Chars and Words
Dim wks As Worksheet, cmt As Comment, lCommentch, lCommentwords, lTxtBoxChar, lTxtBoxCharWords As Long
Dim shp As Shape, sMsg, lTxtBoxStr As String




Application.ScreenUpdating = False




lCommentch = 0
lCommentwords = 0
lTxtBoxChar = 0
lTxtBoxCharWords = 0




For Each wks In ActiveWorkbook.Worksheets




    'Count Char/Words from txtbox
    For Each shp In wks.Shapes
    'Count Chars from Text Box
            If TypeName(shp) <> "GroupObject" Then
                lTxtBoxChar = lTxtBoxChar + shp.TextFrame.Characters.Count
            End If


    'Count Words from Text Box
        If TypeName(shp) <> "GroupObject" And shp.TextFrame2.TextRange.Characters.Text <> "" Then
        lTxtBoxStr = shp.TextFrame2.TextRange.Characters.Text
        lTxtBoxCharWords = lTxtBoxCharWords + Len(Trim(lTxtBoxCharStr)) - Len(Replace(Trim(lTxtBoxCharStr), " ", "")) + 1
            End If




     'Count Char/Words from comments
    For Each cmt In wks.Comments
        lCommentch = lCommentch + Len(cmt.Text)
        lCommentwords = lCommentwords + Len(Trim(cmt.Text)) - Len(Replace(Trim(cmt.Text), " ", "")) + 1
    Next cmt




    Next shp
Next wks




    sMsg = Format(lTxtBoxChar, "### ### ##0") & " chars from Text Box" & vbCrLf
    sMsg = sMsg & Format(lTxtBoxCharWords, "### ### ##0") & " words from Text Box" & vbCrLf & vbCrLf
    
    sMsg = sMsg & Format(lCommentch, "### ### ##0") & " chars from Comments" & vbCrLf
    sMsg = sMsg & Format(lCommentwords, "### ### ##0") & " words from Comments" & vbCrLf & vbCrLf
    
    MsgBox Prompt:=sMsg, Title:="Grand Total"
End Sub

Display More

I assume that shp.TextFrame2.TextRange.Characters.Text part made this issue, but why if it works separately? Any suggestions?

**pike** · May 18th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Hi,
Would be easier if you can attach the workbook or sample workbook
consider ...

Code

For Each objShape In ActiveSheet.Shapes
     
     Debug.Print "A~ " & TypeName(objShape.OLEFormat.Object)


        If TypeName(objShape.OLEFormat.Object) Like "OLEObject" Then
           
           Debug.Print "B~ " & TypeName(objShape.OLEFormat.Object.Object)
            
            If TypeName(objShape.OLEFormat.Object.Object) Like "TextBox" Then


            End If
        End If
    Next

Display More

7absinth · May 18th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Quote from pike;770953

Hi, Pike.

I dont understand this part anymore. How it will work with my code? Where to put it?

**pike** · May 18th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Without the workbook I cannot tell what controls you have maybe a mix of both active x and form

Code

For Each objShape In ActiveSheet.Shapes
     Debug.Print "A~ " & objShape.Name
     Debug.Print "B~ " & TypeName(objShape)
     Debug.Print "C~ " & TypeName(objShape.OLEFormat.Object)
    Next

7absinth · May 18th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Quote from pike;770961
Without the workbook I cannot tell what controls you have maybe a mix of both active x and form
Code
For Each objShape In ActiveSheet.Shapes
     Debug.Print "A~ " & objShape.Name
     Debug.Print "B~ " & TypeName(objShape)
     Debug.Print "C~ " & TypeName(objShape.OLEFormat.Object)
    Next

I have default workbook VBA settings (attached). There is no need to send any workbook - just Insert any 1 comment and 1 TextBox with some added text and then check how many words or chars are there. This part you sent me - I dont know what to do with it to show me msgbox with char/words for comments and textbox's.

[ATTACH=CONFIG]69165[/ATTACH]

**pike** · May 18th 2016

Re: Count Chars/Words from Comments and TextBox VBA

No need to look in the frame the text box has unique id or name

7absinth · May 19th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Quote from pike;771014

No need to look in the frame the text box has unique id or name

In this case I dont need this unique id/name method, because my goal is to count char/words from ANY TextBox that named with whatever name.

**pike** · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

all depend what controls are used make a dummy example reflecting the question and attach

7absinth · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Quote from pike;771084

all depend what controls are used make a dummy example reflecting the question and attach

I have added the code on post #1 and I just need the modification that counts char/words from TxtBox and Comments. If I launch code separately (for example only for Comments) its works fine without any controls added. Same situation is with TextBox. I dont add any additional controls. Only that are provided by Default.

**pike** · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

all I need is an example workbook to help and you wont post workbook .. good luck

**pike** · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

best guess

Code

Sub CountCharWorBOXCMT() 
     'Counts Comments and Text Box Chars and Words
    Dim wks As Worksheet, cmt As Comment, lCommentch, lCommentwords, lTxtBoxChar, lTxtBoxCharWords As Long 
    Dim shp As Shape, sMsg, lTxtBoxStr As String 
     
     
    Application.ScreenUpdating = False 
     
     
    lCommentch = 0 
    lCommentwords = 0 
    lTxtBoxChar = 0 
    lTxtBoxCharWords = 0 
     
     
    For Each wks In ActiveWorkbook.Worksheets 
         
         
         'Count Char/Words from txtbox
        For Each shp In wks.Shapes 
             'Count Chars from Text Box
            If TypeName(shp) <> "GroupObject" Then 
                lTxtBoxChar = lTxtBoxChar + shp.TextFrame.Characters.Count 
                sMsg = Format(lTxtBoxChar, "### ### ##0") & " chars from Text Box" & vbCrLf 
    sMsg = sMsg & Format(lTxtBoxCharWords, "### ### ##0") & " words from Text Box" & vbCrLf & vbCrLf 




 End If 
             
             'Count Words from Text Box
            If TypeName(shp) <> "GroupObject" And shp.TextFrame2.TextRange.Characters.Text <> "" Then 
                lTxtBoxStr = shp.TextFrame2.TextRange.Characters.Text 
                lTxtBoxCharWords = lTxtBoxCharWords + Len(Trim(lTxtBoxCharStr)) - Len(Replace(Trim(lTxtBoxCharStr), " ", "")) + 1 
     sMsg = Format(lTxtBoxChar, "### ### ##0") & " chars from Text Box" & vbCrLf 
    sMsg = sMsg & Format(lTxtBoxCharWords, "### ### ##0") & " words from Text Box" & vbCrLf & vbCrLf 


            End If 
       


    Next shp  
             
             'Count Char/Words from comments
            For Each cmt In wks.Comments 
                lCommentch = lCommentch + Len(cmt.Text) 
                lCommentwords = lCommentwords + Len(Trim(cmt.Text)) - Len(Replace(Trim(cmt.Text), " ", "")) + 1 
              sMsg = sMsg & Format(lCommentch, "### ### ##0") & " chars from Comments" & vbCrLf 
              sMsg = sMsg & Format(lCommentwords, "### ### ##0") & " words from Comments" & vbCrLf & vbCrLf 
               Next cmt 
             


         
    Next wks 
     
        
    


     
    MsgBox Prompt:=sMsg, Title:="Grand Total" 
End Sub

Display More

7absinth · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Quote from pike;771101

all I need is an example workbook to help and you wont post workbook .. good luck

I have added the file. All comments are included in the code.

7absinth · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

From the post #11. Same error on the line:
If TypeName(shp) <> "GroupObject" And shp.TextFrame2.TextRange.Characters.Text <> "" Then

**pike** · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

Hello,
Both are shape textboxes but you can determine which type of shape it is by type enumeration
https://msdn.microsoft.com/en-…rary/office/ff860759.aspx

Code

Sub CountCharWorBOXCMT()


' THE GOAL is count char/words from TxtBox that are named with any name and comments
' If comments are removed from all sheet cells and all parts of code are uncommented then code will work and count only
'  TxtBox char/words. If I add one comment in the cell it will pop out an error. Currently comments are added.


' If I uncomment comment code part and comment txtBox code part (as present state below) - Comment code will work. Why?




    Dim wks As Worksheet, cmt As Comment, lCommentch, lCommentwords, lTxtBoxChar, lTxtBoxCharWords As Long
    Dim objShp As Shape, sMsg, lTxtBoxStr As String




    Application.ScreenUpdating = False




    lCommentch = 0
    lCommentwords = 0
    lTxtBoxChar = 0
    lTxtBoxCharWords = 0




    For Each wks In ActiveWorkbook.Worksheets


        'Uncomment this part (including next shp expression below)


        'Count Char/Words from txtbox
        For Each objShp In wks.Shapes
            'Count Chars from Text Box
            If objShp.Type = 17 Then
                lTxtBoxChar = lTxtBoxChar + objShp.TextFrame.Characters.Count
            End If


            'Count Words from Text Box
            If objShp.Type = 17 Then
            If Len(objShp.TextFrame2.TextRange.Characters.Text) <> 0 Then
                lTxtBoxStr = objShp.TextFrame2.TextRange.Characters.Text
                lTxtBoxCharWords = lTxtBoxCharWords + Len(Trim(lTxtBoxCharStr)) - Len(Replace(Trim(lTxtBoxCharStr), " ", "")) + 1
            End If
            End If


        Next objShp
        For Each cmt In wks.Comments
            lCommentch = lCommentch + Len(cmt.Text)
            lCommentwords = lCommentwords + Len(Trim(cmt.Text)) - Len(Replace(Trim(cmt.Text), " ", "")) + 1
        Next cmt


    Next wks




    sMsg = Format(lTxtBoxChar, "### ### ##0") & " chars from Text Box" & vbCrLf
    sMsg = sMsg & Format(lTxtBoxCharWords, "### ### ##0") & " words from Text Box" & vbCrLf & vbCrLf


    sMsg = sMsg & Format(lCommentch, "### ### ##0") & " chars from Comments" & vbCrLf
    sMsg = sMsg & Format(lCommentwords, "### ### ##0") & " words from Comments" & vbCrLf & vbCrLf


    MsgBox Prompt:=sMsg, Title:="Grand Total"
End Sub

Display More

https://msdn.microsoft.com/en-…rary/office/ff860759.aspx

**pike** · May 20th 2016

Re: Count Chars/Words from Comments and TextBox VBA

and shortened to

Code

Sub CountCharWorBOXCMT()
    Dim wks As Worksheet, cmt As Comment, lCommentch, lCommentwords, lTxtBoxChar, lTxtBoxCharWords As Long
    Dim objShp As Shape, sMsg, lTxtBoxStr As String


    Application.ScreenUpdating = False


    lCommentch = 0
    lCommentwords = 0
    lTxtBoxChar = 0
    lTxtBoxCharWords = 0




    For Each wks In ActiveWorkbook.Worksheets
        'Count Char/Words from txtbox
        For Each objShp In wks.Shapes


            'Count Words from Text Box
            If objShp.Type = 17 Then
                If Len(objShp.TextFrame2.TextRange.Characters.Text) <> 0 Then
                    lTxtBoxChar = lTxtBoxChar + objShp.TextFrame.Characters.Count
                    lTxtBoxStr = objShp.TextFrame2.TextRange.Characters.Text
                    lTxtBoxCharWords = lTxtBoxCharWords + Len(Trim(lTxtBoxCharStr)) - Len(Replace(Trim(lTxtBoxCharStr), " ", "")) + 1
                End If
            End If


            If objShp.Type = 4 Then
                With objShp.TextFrame.Characters
                    lCommentch = lCommentch + Len(.Text)
                    lCommentwords = lCommentwords + Len(Trim(.Text)) - Len(Replace(Trim(.Text), " ", "")) + 1
                End With
            End If


        Next objShp


    Next wks


    sMsg = Format(lTxtBoxChar, "### ### ##0") & " chars from Text Box" & vbCrLf
    sMsg = sMsg & Format(lTxtBoxCharWords, "### ### ##0") & " words from Text Box" & vbCrLf & vbCrLf


    sMsg = sMsg & Format(lCommentch, "### ### ##0") & " chars from Comments" & vbCrLf
    sMsg = sMsg & Format(lCommentwords, "### ### ##0") & " words from Comments" & vbCrLf & vbCrLf


    MsgBox Prompt:=sMsg, Title:="Grand Total"


    Application.ScreenUpdating = True
End Sub

Display More

7absinth · May 21st 2016

Re: Count Chars/Words from Comments and TextBox VBA

Quote from pike;771152

and shortened [..]
End Sub[/CODE]

I didnt think of about type for this part. All looks cool till I encountered it doesnt count TxtBox Words correctly. Now its counts TxtBox as Object. I added 1 text box and it counts as 1 word. No matter how many words are written inside.

**pike** · May 21st 2016

Re: Count Chars/Words from Comments and TextBox VBA

Hello,
if you want to count the words don't use replace space with nothing it just makes it one word . count the spaces like

Code

Option Explicit
Sub CountCharWorBOXCMT()
    Dim wks As Worksheet, lCommentch As Long, lCommentwords As Long, lTxtBoxChar As Long, lTxtBoxCharWords As Long
    Dim objShp As Shape, sMsg As String, vbaArray


    Application.ScreenUpdating = False


    lCommentch = 0
    lCommentwords = 0
    lTxtBoxChar = 0
    lTxtBoxCharWords = 0


    For Each wks In ActiveWorkbook.Worksheets
        For Each objShp In wks.Shapes
            If objShp.Type = 17 Then
                If Len(objShp.TextFrame2.TextRange.Characters.Text) <> 0 Then
                    lTxtBoxChar = lTxtBoxChar + objShp.TextFrame.Characters.Count
                     vbaArray = VBA.Split(objShp.TextFrame2.TextRange.Characters.Text, " ")
                    lTxtBoxCharWords = lTxtBoxCharWords + UBound(vbaArray) + 1
                End If
            End If
            If objShp.Type = 4 Then
                With objShp.TextFrame.Characters
                    lCommentch = lCommentch + Len(.Text)
                    vbaArray = VBA.Split(.Text, " ")
                    lCommentwords = lCommentwords + UBound(vbaArray) + 1
                End With
            End If


        Next objShp


    Next wks


    sMsg = Format(lTxtBoxChar, "### ### ##0") & " chars from Text Box" & vbCrLf
    sMsg = sMsg & Format(lTxtBoxCharWords, "### ### ##0") & " words from Text Box" & vbCrLf & vbCrLf


    sMsg = sMsg & Format(lCommentch, "### ### ##0") & " chars from Comments" & vbCrLf
    sMsg = sMsg & Format(lCommentwords, "### ### ##0") & " words from Comments" & vbCrLf & vbCrLf


    MsgBox Prompt:=sMsg, Title:="Grand Total"


    Application.ScreenUpdating = True
End Sub

Display More

7absinth · May 21st 2016

Re: Count Chars/Words from Comments and TextBox VBA

Quote from pike;771171

[/CODE]

WOW. Really WOW. I am impressed. You are like macro magician :wowee:. Now its works as my wall clock.:congrats:

Count Chars/Words from Comments and TextBox VBA

Participate now!

Share