Hello, this is my first post. I'm trying to build an Excel workbook which will generate a Word document using find and replace as well as checkbox-controlled conditional texts. I've figured out the F&R part, and now I'm struggling with the checkbox-controlled conditional text part.
The checkbox I'm using is the form controls checkbox, as the final product will be used by a lot of people, I thought it would be more user-friendly than to use ActiveX. However, the VBA code I tried can't seem to read the value of the checkboxes correctly. It keeps returning TRUE no matter the state of the checkboxes.
I'm a beginner in VBA so I'm not sure if I'm even employing the correct code to retrieve the checkboxes' values. I've tried using .Sheets(StrWkSht).Shapes("CheckAnjing").OLEFormat. Object.Value and .Sheets(StrWkSht).Shapes("CheckAnjing").ControlFor mat.Value, both with no avail.
isAnjing and isKucing true.png
Code provided below:
Code:
Sub HideStyles()
Application.ScreenUpdating = False
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
StrWkBkNm = ThisWorkbook.Path & "\Macro Testing Ground.xlsm"
StrWkSht = "Sheet1"
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
On Error GoTo 0
With xlApp
Set xlWkBk = .Workbooks.Open(StrWkBkNm, False, True)
With xlWkBk
Dim isAnjing As Boolean, isKucing As Boolean
isAnjing = Not .Sheets(StrWkSht).Shapes("CheckAnjing").ControlFormat.Value
isKucing = Not .Sheets(StrWkSht).Shapes("CheckKucing").ControlFormat.Value
End With
End With
Dim wdDoc As Document
Set wdDoc = Documents.Open(ThisWorkbook.Path & "\Macro Testing Ground.docm")
With wdDoc
.Styles("Strong").Font.Hidden = isAnjing
.Styles("Emphasis").Font.Hidden = isKucing
End With
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
I would appreciate any help!