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

 

[6StringJazzer]

strikethrough that tab name

Where exactly do you want to strike through the tab name? On the tab? Cannot be done. In the checkbox caption? Cannot be done. In a cell?

 

[EtienneD]

The actual tab name at the bottom.

 

[Fluff]

As has been said you cannot do that, however you could change the colour of the tab instead.

 

[EtienneD]

Ok so can I change the text instead?

 

[Fluff]

Yes that can be done.

 

[Jaafar Tribak]

Actually, if you use combined unicode text (ChrW(&H336)), you could add a stroke though the sheets tab names as requested.

Here is a routine that does that:

In a Standard Module:

Option Explicit


Sub StrikeThrough_Worksheet_Tab(ByVal WS As Worksheet)

    Const MAX_CHARS = 15
    Dim i As Long, sTmp As String
 
    With WS
        If AscW(Mid(.Name, 1, 1)) = &H336 Then Exit Sub
        If Len(.Name) > MAX_CHARS Then Exit Sub
        For i = 1 To Len(.Name)
            sTmp = sTmp & ChrW(&H336) & Mid(.Name, i, 1)
        Next i
        .Name = sTmp & ChrW(&H336)
    End With

End Sub

The following should add a strikethrough to sheets(1) tab:

Sub Test()
    Call StrikeThrough_Worksheet_Tab(WS:=Sheets(1))
End Sub

Because, the text is now combined it is double the size of the original tab text and therefore the above will only work if the original tab text has a maximum of 15 characters. (By default, sheet tab names can't exceed 31 chars)

 

[Jaafar Tribak]

 

[EtienneD]

Actually, if you use combined unicode text (ChrW(&H336)), you could add a stroke though the sheets tab names as requested.

Here is a routine that does that:

In a Standard Module:

Option Explicit


Sub StrikeThrough_Worksheet_Tab(ByVal WS As Worksheet)

    Const MAX_CHARS = 15
    Dim i As Long, sTmp As String
 
    With WS
        If AscW(Mid(.Name, 1, 1)) = &H336 Then Exit Sub
        If Len(.Name) > MAX_CHARS Then Exit Sub
        For i = 1 To Len(.Name)
            sTmp = sTmp & ChrW(&H336) & Mid(.Name, i, 1)
        Next i
        .Name = sTmp & ChrW(&H336)
    End With

End Sub

The following should add a strikethrough to sheets(1) tab:

Sub Test()
    Call StrikeThrough_Worksheet_Tab(WS:=Sheets(1))
End Sub

Because, the text is now combined it is double the size of the original tab text and therefore the above will only work if the original tab text has a maximum of 15 characters. (By default, sheet tab names can't exceed 31 chars)

Thanks for your help! Can the strike appear when a checkbox is checked?

 

[6StringJazzer]

Actually, if you use combined unicode text

Neat trick. I did not know those characters were valid in tab names.