CODEWITHSUNDEEP
excelvba
Jade
Jade

Reputation: 77

Unable to set the text property of the characters class

This code protects all sheets except for one sheet. This works if the worksheets are protected, but gives me an error message when they are not protected.

ws is the worksheet on which the button is on.

wsheet is to protect all the sheets except that one sheet. Essentially, when the user clicks the button the worksheet ws, I want all worksheets (including the one the button is on) to be protected, except the worksheet named "Overtime". When they click on it again, it unprotects all worksheeets.

Dim wSheet As Worksheet
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)


        Application.ScreenUpdating = False

            For Each wSheet In Worksheets
                If wSheet.Name = "Overtime" Then
                    wSheet.Unprotect Password:="12345"
                    ws.Shapes("Rectangle_LOCK").TextFrame.Characters.Text = "Vérouiller" 'THIS IS WHERE IT GIVES ME THE ERROR
                ElseIf wSheet.ProtectContents = True Then
                    wSheet.Unprotect Password:="12345"
                    ws.Shapes("Rectangle_LOCK").TextFrame.Characters.Text = "Vérouiller"
                Else

                    wSheet.Unprotect Password:="12345"
                    ws.Shapes("Rectangle_LOCK").TextFrame.Characters.Text = "Déverouiller"
                    wSheet.Protect Password:="12345"


                End If
            Next wSheet

 Application.ScreenUpdating = True

Upvotes: 0

Views: 1318

Answers (1)

Domenic
Domenic

Reputation: 8104

Try the following code...

Dim wSheet As Worksheet
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)


Application.ScreenUpdating = False

For Each wSheet In wb.Worksheets
    If wSheet.Name <> "Overtime" Then
        If wSheet.ProtectContents Then
            wSheet.Unprotect Password:="12345"
            If wSheet.Name = ws.Name Then
                wSheet.Shapes("Rectangle_LOCK").TextFrame.Characters.Text = "Vérouiller"
            End If
        Else
            If wSheet.Name = ws.Name Then
                wSheet.Shapes("Rectangle_LOCK").TextFrame.Characters.Text = "Déverouiller"
            End If
            wSheet.Protect Password:="12345"
        End If
    End If
Next wSheet

Application.ScreenUpdating = True

Upvotes: 1

Related Questions