Macro Help

[Tony1974]

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!

https://r1132100503382-eu1-3dswym.3dexperience.3ds.com/api/media/streammedia/id/dO3NN6vUSdGOaw9vaqt0WQ/type/picture/key/o1/update/4dcb4478e72064f77c6baee89e3d13dc
https://r1132100503382-eu1-3dswym.3dexperience.3ds.com/api/media/streammedia/id/9fyP9BntSWSbZ-JOw-DueA/type/picture/key/o1/update/bfdbf6bd37cdf663a1ee71fc1bcf2efe
https://r1132100503382-eu1-3dswym.3dexperience.3ds.com/api/media/streammedia/id/VHsY62IOTM2My-kajyGX_g/type/picture/key/o1/update/486829daf8474f481fc607dde23e93eb
https://r1132100503382-eu1-3dswym.3dexperience.3ds.com/api/media/streammedia/id/QlnawuSVSQqJgjGGxTnCxQ/type/picture/key/o1/update/988e67a3e167240a60e411b853ae31fa
https://r1132100503382-eu1-3dswym.3dexperience.3ds.com/api/media/streammedia/id/pCcWNH-aQwSpwppxC-cj9g/type/picture/key/o1/update/4fa7e49738580c6c475ce43b7fefa082


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

[Aussiebear]

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.

[gmaxey]

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

.

[Tony1974]

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

[Tony1974]

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

[Tony1974]

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

[Aussiebear]

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.

[Tony1974]

yeah i'm finding that out. Thoughts on the code i posted

[Aussiebear]

Did you try Greg's code?

[Tony1974]

Oops. Totally missed it. I’ll try tomorrow at work. Thx!

[gmaxey]

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

[Tony1974]

Greg,

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

[Tony1974]

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!

[gmaxey]

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

[gmaxey]

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

[Tony1974]

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

[Tony1974]

do i need to delete the Sub auto open module and have it all in one code block?

[Tony1974]

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

[Tony1974]

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!! :)

[gmaxey]

You didn't post your final code but glad you have it working.

[Tony1974]

i knew i forgot something! :)


Sub AutoOpen() ' 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
lbl_Exit:
Exit Sub
End Sub

[gmaxey]

Tony,

Just for the sake of completeness, while your AutoOpen() macros may be updating all of the fields you care about, it is not a comprehensive update. You might consider:


Sub AutoOpen()
UpdateAllFields
CheckBoxesByTag
lbl_Exit:
Exit Sub
End Sub

Sub UpdateAllFields()
Dim oRngStory As Word.Range
Dim lngJunk As Long
Dim oShp As Shape, oCanShp As Shape
Dim oToc As TableOfContents, oTOA As TableOfAuthorities, oTOF As TableOfFigures
lngJunk = ActiveDocument.Sections(1).Headers(1).Range.StoryType
For Each oRngStory In ActiveDocument.StoryRanges
'Iterate through all linked stories
Do
On Error Resume Next
oRngStory.Fields.Update
Select Case oRngStory.StoryType
Case 6, 7, 8, 9, 10, 11
If oRngStory.ShapeRange.Count > 0 Then
For Each oShp In oRngStory.ShapeRange
If oShp.TextFrame.HasText Then
oShp.TextFrame.TextRange.Fields.Update
End If
If oShp.Type = msoCanvas Then
For Each oCanShp In oShp.CanvasItems
If oCanShp.TextFrame.HasText Then
oCanShp.TextFrame.TextRange.Fields.Update
End If
Next oCanShp
End If
Next
End If
Case Else
'Do Nothing
End Select
On Error GoTo 0
'Get next linked story (if any)
Set oRngStory = oRngStory.NextStoryRange
Loop Until oRngStory Is Nothing
Next oRngStory
'Special Cases
For Each oToc In ActiveDocument.TablesOfContents
oToc.Update
Next oToc
For Each oTOA In ActiveDocument.TablesOfAuthorities
oTOA.Update
Next
For Each oTOF In ActiveDocument.TablesOfFigures
oTOF.Update
Next
lbl_Exit:
Exit Sub
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)
With oCC
.LockContents = False
.Checked = True
.LockContents = True
End With
End If
Next lngProp
lbl_Exit:
Exit Sub
End Sub

[Tony1974]

Greg,

Thanks for the latest update! :)

Of course now that i have this working, management has decided that the line here:


' Define the names of the custom properties to check
arrProperties = Split("DCR Type|Change Classification", "|")

doesnt need an array check. there is now just Change Classification property to check. could you modify so this is the case? Thanks in advance

[gmaxey]

You can just modify that line to:

arrProperties = Split("Change Classification", "|")

arrProperties will then just contain one element "Change Classification"

If your management changes their mind later you can revise that line to suit e.g.,:
arrProperties = Split("Change Classification|PropB|PropC|Prop whatever", "|")

[Tony1974]

Perfect! thank you very much.

Also could you suggest a good resource for self learning this VBA stuff. I have a feeling that we'll be using this more in the future.

[gmaxey]

Well for starters, I would avoid the AI idiots. In my opinion they will give you half-baked solutions at best. My website has a lot of code examples as does Graham Mayor, Suzanne Barnhill and Charles Kenyon. Reading and trying to answer posts in these forums with the position that "a solution is there, I just have to find it" is where I learned most of what little I know.

[Aussiebear]

....is where I learned most of what little I know.. Even for us Aussies, that's far too modest!.

[gmaxey]

"We are all apprentices in a craft where no one ever becomes a master." ... Hemingway (I think). Thanks Ted!