Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 28

Thread: Macro Help

  1. #1
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location

    Macro Help

    Hi all, We have a change request process that is automated in our Solidworks PDM vault. I have (2) drop downs, with multiple options that, when selected write to custom properties in the Word document. Example wouild be Addition and Class A. I am trying to make a macro will take these custom properties, and based on them, unlock checkboxes for editing, check the corresponding checkboxes in my word document, and lock checkboxes for editing. I have a simple version that works where i specify a property and it finds it and checks the box. I cannot seem to make work where i specify an "array" of the available options and it checks document and selects two boxes. I've attached the code i have so far. Thanks in advance for any guidance!







    Sub CheckBoxesByTag()
        Dim propertyName As String
        Dim propertyValue As String
        Dim cb As ContentControl
        Dim tagToFind As String
        ' Define the name of the custom property to check propertyName = "DCR Type"
        ' Define the tag to find tagToFind = "Deviation"  ' Make sure to enclose string values in double quotes
        ' Get the value of the custom property
        On Error Resume Next
        propertyValue = ActiveDocument.CustomDocumentProperties(propertyName).value
        On Error GoTo 0
        ' Check if the custom property exists and has a specific value
        If propertyValue = tagToFind Then
            ' Loop through content controls to find checkboxes and unlock them
            For Each cb In ActiveDocument.ContentControls
                If cb.Type = wdContentControlCheckBox Then
                    ' Unlock the content control
                    cb.LockContentControl = False
                    ' Allow editing of the content control
                    cb.LockContents = False
                End If
            Next cb
            ' Loop through content controls to find checkboxes and check them
            For Each cb In ActiveDocument.ContentControls
                If cb.Type = wdContentControlCheckBox And cb.tag = tagToFind Then
                    cb.Checked = True
                End If
            Next cb
            ' Loop through content controls to find checkboxes and lock them
            For Each cb In ActiveDocument.ContentControls
                If cb.Type = wdContentControlCheckBox Then
                    ' Lock the content control
                    cb.LockContents = True
                End If
            Next cb
        End If
    End Sub

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,185
    Location
    Welcome to VBAX Tony1974. Please note I've edited your code and added Code tags (see the first line in my signature) to make it easier to read.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,383
    Location
    I don't really understand what you are trying to do. You have never set a value for tagToFind. However, I see no reason to loop through all of the CCs three times to set a value:

    Sub CheckBoxesByTag()
    Dim propertyName As String
    Dim propertyValue As String
    Dim cb As ContentControl
    Dim oCCs As ContentControls
    Dim tagToFind As String
      'Get the value of the custom property
      On Error Resume Next
      propertyValue = ActiveDocument.CustomDocumentProperties(propertyName).Value
      On Error GoTo 0
      'Check if the custom property exists and has a specific value
      If propertyValue = tagToFind Then
        Set oCCs = ActiveDocument.SelectContentControlsByTag(tagToFind)
        For Each cb In oCCs
          With cb
            .LockContents = False
            cb.Checked = True
            .LockContents = True
          End With
        Next
      End If
    End Sub
    .
    Greg

    Visit my website: http://gregmaxey.com

  4. #4
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    Thank you guys for the replies. I am very green at macros and coding so my apologies for not being able to explain as good as i should. The selecting an entry from each drop down shown is part of another process that currently works properly. Upon automatic file creation it writes those selected custom properties to the word document. I then want to have the values that are in the custom properties used to select the corresponding checkboxes so the user doesn't have to check them manually. I'll try to make a screen cast and post, maybe that might help. Thanks again

  5. #5
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    I was using ChatGPT and this is where it was taking me

    Edit: I cant seem to get the code to show up like you did...LOL

  6. #6
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    Sub CheckBoxesByTag()
        Dim propertyNames As Variant
        Dim propertyValue As Variant
        Dim cb As ContentControl
        Dim tagToFind As Variant
        Dim propertyName As Variant
        ' Define the names of the custom properties to check
        propertyNames = Array("DCR Type", "Classification")
        ' Loop through each property name in the array
        For Each propertyName In propertyNames
            ' Get the value of the custom property
            'On Error Resume Next
            propertyValue = ActiveDocument.CustomDocumentProperties(propertyName).Value 
            'On Error GoTo 0
            ' Define the tag to find
            tagToFind = Array("Addition", "Deviation", "Change", "Removal", "Class A", "Class B", "Class C", "Class D")
            ' Make sure to enclose string values in double quotes
            ' Check if the custom property exists and has a specific value 
            If Not IsEmpty(propertyValue) And propertyValue = tagToFind Then 
                ' Loop through content controls to find checkboxes with the specified tag
                For Each cb In ActiveDocument.ContentControls 
                    If cb.Type = wdContentControlCheckBox And cb.tag = tagToFind Then 
                        ' Unlock the content control
                        cb.LockContentControl = False
                        ' Allow editing of the content control 
                        cb.LockContents = False 
                        ' Check the checkbox 
                        cb.Checked = True 
                        ' Lock the content control 
                        cb.LockContents = True
                    End If 
                Next cb
            End If 
        Next propertyName
    End Sub


    error 13 type mismatch
    Last edited by Aussiebear; 05-07-2024 at 03:32 PM. Reason: Tidy up code presentation

  7. #7
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,185
    Location
    One of the major issues with using ChatGP and others like it, is that you need to ask very specific questions, otherwise you will get vague answers that you hope might be correct.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    yeah i'm finding that out. Thoughts on the code i posted

  9. #9
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,185
    Location
    Did you try Greg's code?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    Oops. Totally missed it. I’ll try tomorrow at work. Thx!

  11. #11
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,383
    Location
    This should get you close. If all your CCs "tagged" "Addition" "Deviation" etc. are checkboxes then there is no need to check if they are checkboxes

    Sub CheckBoxesByTag()
    Dim lngTag As Long
    Dim arrTags() As String
    Dim lngProp As Long
    Dim arrProperties() As String
    Dim varValue As Variant
    Dim oCCs As ContentControls
    Dim oCC As ContentControl
      'Define the names of the custom properties to check
      arrProperties = Split("DCR Type|Classification", "|")
      'Loop through each property name in the array
      For lngProp = 0 To UBound(arrProperties)
        'Get the value of the custom property
        varValue = ActiveDocument.CustomDocumentProperties(arrProperties(lngProp)).Value
        'Define the tagd to find
        arrTags = Split("Addition|Deviation|Change|Removal|Class A|Class B|Class C|Class D", "|")
        For lngTag = 0 To UBound(arrTags)
          'Make sure to enclose string values in double quotes
          'Check if the custom property exists and has a specific value
          If Not IsEmpty(varValue) And varValue = arrTags(lngTag) Then
            Set oCCs = ActiveDocument.SelectContentControlsByTag(arrTags(lngTag))
            For Each oCC In oCCs
              If oCC.Type = wdContentControlCheckBox Then
                oCC.LockContents = False
                oCC.Checked = True
                oCC.LockContents = True
              End If
            Next oCC
          End If
        Next lngTag
      Next lngProp
    lbl_Exit:
      Exit Sub
    End Sub
    P.S. nothing wrong with using variant arrays, I just like string arrays best for this sort of thing
    Greg

    Visit my website: http://gregmaxey.com

  12. #12
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    Greg,

    This seems to work perfectly! Thank you so much. Now i need to go through and try to understand what it all means!

  13. #13
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    So I have fields in this word document and there is a macro that updates fields upon document open. How do i get this new macro to auto run when i open the file as well? Thanks!

  14. #14
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,383
    Location
    That is a Document_Open or AutoOpen macro (or really whatever it is), just add a call to the
    CheckBoxesByTag

    e.g.,

    Sub Document_Open()
        'Your existing code that updates fields
        CheckBoxesByTag
        lbl_Exit:
        Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  15. #15
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,383
    Location
    btw, while I still don't know the exact layout of your document, but if there is only one checkbox CC associated with each property value then you are spinning a lot of wheels looping through CCs:

    Sub CheckBoxesByTag()
        Dim lngProp As Long
        Dim arrProperties() As String
        Dim varValue As Variant
        Dim oCC As ContentControl
        'Define the names of the custom properties to check
        arrProperties = Split("DCR Type|Classification", "|")
        'Loop through each property name in the array
        For lngProp = 0 To UBound(arrProperties)
             'Get the value of the custom property
            varValue = ActiveDocument.CustomDocumentProperties(arrProperties(lngProp)).Value
            If Not IsEmpty(varValue) Then
                'Get the specific oCC associated with that value e.g., tag = value
                Set oCC = ActiveDocument.SelectContentControlsByTag(varValue).Item(1)
                oCC.LockContents = False
                oCC.Checked = True
                oCC.LockContents = True
            End If
        Next lngProp
       lbl_Exit:
      Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  16. #16
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    Doesnt seem to be working. The fields update withthe auto open code, but the checkboxes dont check. Probably screw it up somewhere.
    Sub AutoOpen()
        ' AutoOpen Macro
        Dim aStory As Range
        Dim aField As Field
        For Each aStory In ActiveDocument.StoryRanges
            For Each aField In aStory.Fields
                aField.Update
            Next aField
        Next aStory
    End Sub
    
    
    Sub Document_Open()
        Dim lngTag As Long
        Dim arrTags() As String
        Dim lngProp As Long
        Dim arrProperties() As String
        Dim varValue As Variant
        Dim oCCs As ContentControls
        Dim oCC As ContentControl
        ' AutoOpen Macro
        Dim aStory As Range
        Dim aField As Field
        For Each aStory In ActiveDocument.StoryRanges
            For Each aField In aStory.Fields
                aField.UpdateNext aField
            Next aStory
            'Define the names of the custom properties to check
            arrProperties = Split("DCR Type|Change Classification", "|")
            'Loop through each property name in the array
            For lngProp = 0 To UBound(arrProperties)
                'Get the value of the custom property
                varValue = ActiveDocument.CustomDocumentProperties(arrProperties(lngProp)).Value
                'Define the tags to find
                arrTags = Split("Addition|Deviation|Change|Removal|Class A|Class B|Class C|Class D", "|")
                For lngTag = 0 To UBound(arrTags)
                    'Make sure to enclose string values in double quotes
                    'Check if the custom property exists and has a specific value
                    If Not IsEmpty(varValue) And varValue = arrTags(lngTag) Then
                        Set oCCs = ActiveDocument.SelectContentControlsByTag(arrTags(lngTag))
                        For Each oCC In oCCs
                            If oCC.Type = wdContentControlCheckBox Then
                                oCC.LockContents = False
                                oCC.Checked = True
                                oCC.LockContents = True
                            End If
                        Next oCC
                    End If
                Next lngTag
            Next lngProp
            lbl_Exit:
            Exit Sub
    End Sub

  17. #17
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    do i need to delete the Sub auto open module and have it all in one code block?

  18. #18
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    So i came up with this, however it does not auto update the checkboxes. If i then hit the run button in the VBA code window the boxes check themselves.

    Sub Document_Open()    ' Update all fields in the document
        Dim aStory As Range
        Dim aField As Field
        For Each aStory In ActiveDocument.StoryRanges
            For Each aField In aStory.Fields
                aField.Update
            Next aField
        Next aStory
        
        ' Process checkboxes based on custom properties
        CheckBoxesByTag
    End Sub
    
    
    Sub CheckBoxesByTag()
        Dim lngProp As Long
        Dim arrProperties() As String
        Dim varValue As Variant
        Dim oCC As ContentControl
        
        ' Define the names of the custom properties to check
        arrProperties = Split("DCR Type|Change Classification", "|")
        
        ' Loop through each property name in the array
        For lngProp = 0 To UBound(arrProperties)
            ' Get the value of the custom property
            varValue = ActiveDocument.CustomDocumentProperties(arrProperties(lngProp)).Value
            
            ' If the value is not empty, find the corresponding content control by tag and check it
            If Not IsEmpty(varValue) Then
                ' Get the specific content control associated with that value e.g., tag = value
                Set oCC = ActiveDocument.SelectContentControlsByTag(varValue).Item(1)
                oCC.LockContents = False
                oCC.Checked = True
                oCC.LockContents = True
            End If
        Next lngProp
    End Sub

  19. #19
    VBAX Regular
    Joined
    May 2024
    Posts
    15
    Location
    I got it. I just deleted the autoOpen module that was updaing fields and renamed my main code to AutoOpen and it works beautifully!! Not sure if thats the proper way to do it. Thanks so much for all your heklp guys!!

  20. #20
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,383
    Location
    You didn't post your final code but glad you have it working.
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •