CODEWITHSUNDEEP
vbaexcelpassword-protection
PyVas
PyVas

Reputation: 49

Unprotected sheet cannot be modified

I have a sheet that I protected with password. When I change anything on that sheet with VBA I unprotect like this:

Private Sub Worksheet_Change(ByVal target As Range)
    ActiveSheet.Unprotect Password:="pass"

    'do stuffs

    ActiveSheet.Protect Password:="pass"

End Sub

The problem is that I still get this error message in this line:

 For j = 13 To 12 + numberofsomething
     With Range("H" & j & ":L" & j).Borders
         .LineStyle = xlContinuous  '<---Here
         .Weight = xlMedium
         .Color = RGB(255, 0, 0)
     End With
 Next j

Run-time error: 1004 Unable to set the LineStyle proprety of the Borders class

What could be the problem?

Upvotes: 2

Views: 150

Answers (1)

Moosli
Moosli

Reputation: 3230

It's not efficient to Protect and unprotect your Worksheets every Time.

You can Protect all of your Sheets Like that: Sometimes Excel get a Problem if you have allready a Border, so just set the B order to None Before you Formating it.

Private Sub Workbook_Open()
  Dim wks As Worksheet

  'Loop over all Sheets
  For Each wks In ActiveWorkbook.Worksheets

    wks.Protect Password:="Secret", _
     UserInterFaceOnly:=True
  Next wks

End Sup

The UserInterFaceOnly is an optional argument of the Protect Method that we can set to True, the default is False. By setting this argument to True Excel will allow all Excel VBA macros to run on the Worksheet that protected with or without a password. So you don't need to Unprotect your Worksheets all the Time.

And to your Other Problem, Excel sometimes gets Problem When the Borders are allready set. So try to set the Borders to None before Change them.

For j = 13 To 20
    With Range("H" & j & ":L" & j).Borders
         .LineStyle = xlNone
     End With
     With Range("H" & j & ":L" & j).Borders
         .LineStyle = xlContinuous  '<---Here
         .Weight = xlMedium
         .Color = RGB(255, 0, 0)
     End With
 Next j

Upvotes: 1

Related Questions