Hide text in certain styles based on value in Excel

[calinanix]

Hello, it's me the VBA beginner again.




Right now I'm trying to build a macro that will hide text with certain styles based on value in Excel. The style names are stored in column D, while the value to show/hide the style are stored in column H (IF cell value = TRUE then don't hide style; IF cell value = FALSE then hide style).


Here's what I've come up with so far:

Sub HideStyles()

Application.ScreenUpdating = False
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim iDataRow As Long
Dim StyleList As String, HideList As String, StyleToStyle As String, HideStyle As Boolean
StrWkBkNm = ThisWorkbook.Path & "\Input Template Tester.xlsm"
StrWkSht = "Data Input"

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.Sheets(StrWkSht)
    ' Find the last-used row in column A.
        iDataRow = .Cells(.Rows.Count, "D").End(xlUp).Row ' -4162 = xlUp
        ' Capture the style name and hide value.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("D" & i)) <> vbNullString And Trim(.Range("E" & i)) = "TF" Then
            StyleList = StyleList & "|" & Trim(.Range("D" & i))
            HideList = HideList & "|" & Trim(.Range("H" & i))
          End If
        Next
    End With
End With

Dim wdDoc As Document

Set wdDoc = Documents.Open(ThisDocument.Path & "\Template_Full.docx")
With wdDoc
    For i = 1 To UBound(Split(StyleList, "|"))
            On Error Resume Next
            StyleToHide = Split(StyleList, "|")(i)
            HideStyle = Split(HideList, "|")(i)
        If Not .Styles(StyleToHide) Is Nothing Then
            .Styles(StyleToHide).Font.Hidden = Not CBool(HideStyle)
        End If
    Next i
End With
Set wdDoc = Nothing
Application.ScreenUpdating = True

End Sub

When I run the code, none of the style seems to get hidden although the CBool(HideStyle) appears to correctly return the boolean value in the list. I wonder if the problem arises from storing cell values as string? Does the CBool function only work on converting integers to boolean?


I appreciate any help! I'm learning as I go as you could probably tell