VBA Strikethrough sheet tab name with checkbox

[EtienneD]

Hi,

I have 5 worksheets that represent 5 different lists of tasks. When all the tasks are done in one sheet, I want to be able to to see it quickly. So I would like to have a checkbox that either strikethrough that tab name so we know it's all done. I know how to add the checkbox, name it with the tab name I want to modify , and assign the macro to the checkbox. I just needhelp with the formula please.

Thanks to you all

 

[Jaafar Tribak]

Hi EtienneD

Check out this workbook example : StrikeThroughTabs.xlsm




The following function should toggle the worksheet tab strikethrough on and off .via a checkbox control embedded in the worksheet as requested.


In a Standard Module:

Option Explicit

Function StrikeThrough_Worksheet_Tab( _
    ByVal WS As Worksheet, _
    Optional ByVal bStrike As Boolean = True _
    ) As Boolean

    Const MAX_CHARS = 15
    Dim i As Long, sTmp As String
    
    With WS
        Select Case bStrike
            Case True
                If AscW(Mid(.Name, 1, 1)) <> &H335 And Len(.Name) <= MAX_CHARS Then
                    For i = 1 To Len(.Name)
                        sTmp = sTmp & ChrW(&H335) & Mid(.Name, i, 1)
                    Next i
                    .Name = sTmp & ChrW(&H335)
                    StrikeThrough_Worksheet_Tab = True
                End If
            Case Else
                If AscW(Mid(.Name, 1, 1)) = &H335 Then
                    .Name = Replace(.Name, ChrW(&H335), "")
                End If
                StrikeThrough_Worksheet_Tab = True
        End Select
    End With

End Function


Sub CheckBox_Macro()
    
    If StrikeThrough_Worksheet_Tab( _
        WS:=ActiveSheet, _
        bStrike:=CBool(ActiveSheet.Shapes(Application.Caller).ControlFormat.Value And &H1) _
    ) = False Then
        
      MsgBox "Failed to striketrough the current worksheet tab!" & vbNewLine & vbNewLine & _
            "Tab names must not exceed 15 characters.", vbCritical, "ERROR"
   End If
        
End Sub

 

[EtienneD]

Hi EtienneD

Check out this workbook example : StrikeThroughTabs.xlsm




The following function should toggle the worksheet tab strikethrough on and off .via a checkbox control embedded in the worksheet as requested.


In a Standard Module:

Option Explicit

Function StrikeThrough_Worksheet_Tab( _
    ByVal WS As Worksheet, _
    Optional ByVal bStrike As Boolean = True _
    ) As Boolean

    Const MAX_CHARS = 15
    Dim i As Long, sTmp As String
   
    With WS
        Select Case bStrike
            Case True
                If AscW(Mid(.Name, 1, 1)) <> &H335 And Len(.Name) <= MAX_CHARS Then
                    For i = 1 To Len(.Name)
                        sTmp = sTmp & ChrW(&H335) & Mid(.Name, i, 1)
                    Next i
                    .Name = sTmp & ChrW(&H335)
                    StrikeThrough_Worksheet_Tab = True
                End If
            Case Else
                If AscW(Mid(.Name, 1, 1)) = &H335 Then
                    .Name = Replace(.Name, ChrW(&H335), "")
                End If
                StrikeThrough_Worksheet_Tab = True
        End Select
    End With

End Function


Sub CheckBox_Macro()
   
    If StrikeThrough_Worksheet_Tab( _
        WS:=ActiveSheet, _
        bStrike:=CBool(ActiveSheet.Shapes(Application.Caller).ControlFormat.Value And &H1) _
    ) = False Then
       
      MsgBox "Failed to striketrough the current worksheet tab!" & vbNewLine & vbNewLine & _
            "Tab names must not exceed 15 characters.", vbCritical, "ERROR"
   End If
       
End Sub

It works like a charm. Thank very much!