Multiple unprotect sheet VBA code

1
votes
'unprotect sheets for CDRF (1)
 Sheets("CDRF File (1)").Unprotect Password:="hammer"

 Set wsCopyFrom = ActiveWorkbook.Worksheets("STP_Data")
 Set wsCopyTo = ActiveWorkbook.Worksheets("CDRF File (1)")

 Entity Name
 wsCopyTo.Range("B11") = wsCopyFrom.Range("E30")

 'unprotect sheets for CDRF (1)
  Sheets("CDRF File (1)").Unprotect Password:="hammer"

  CIF
  wsCopyTo.Range("K11") = wsCopyFrom.Range("F30")

  'unprotect sheets for CDRF (1)
    Sheets("CDRF File (1)").Unprotect Password:="hammer"

  'TIN
  wsCopyTo.Range("M11") = wsCopyFrom.Range("G30")

Hi. So my question is why do I need to unprotect each line of copy and paste. Before I would only need one line to get the desire result and for this set of code I have to unprotect every time I need to paste.

If anyone has any insight please let me know.

2
vbaexcel
Use With...End With Statement - 0m3r
That's only a section of my code I have start sub and end within the code. - Peter Nguyen
Can show your Complete code? - 0m3r
Are you re-protecting the worksheet using the Worksheet_Change event? - TnTinMn
The full code is quite long. There's multiple things happening in the worksheet such as files are being pulled from an access database. I limited the error to this particular section by making sure all the other pieces works. I am reprotecting the Worksheet but I dont understand what you mean by Worksheet_Change event. - Peter Nguyen

2 Answers

1
votes

Try ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="password"

your code here with out Sheets("CDRF File (1)").Unprotect Password:="hammer" and

Save the Workbook then

ThisWorkbook.Worksheets("Sheet1").Protect Password:="password"

0
votes

As mentioned in Comments by @TnTinMn, the repeated need to call Unprotect would be an action occurring in a Workbook Event. If possible, you can update the code within the necessary events to prevent the Protect command from executing. This may not be desirable as the code was potentially added to ensure the sheets remain protected.

Some suggests on cleaning the original code snippet up a bit to allow for easier maintenance.

Each of the two worksheets you are referencing are assigned to variables. Despite having the variable setup, all the Unprotect calls spell out the full name instead of using the variable.

This:

Sheets("CDRF File (1)").Unprotect Password:="hammer"

could be:

wsCopyTo.Unprotect Password:="hammer"

The password is specified in multiple location. Save it to a variable so there is only one update necessary on a password change.

'add to declaration section
dim pwStr as string
pwStr = "hammer"
'...
wsCopyTo.Unprotect Password:=pwStr

The initial Unprotect should be executed after the assignment, which allows use of the worksheet variable.

Set wsCopyFrom = ActiveWorkbook.Worksheets("STP_Data")
Set wsCopyTo = ActiveWorkbook.Worksheets("CDRF File (1)")

'unprotect sheets for CDRF (1)
wsCopyTo.Unprotect Password:=pwStr

Comments should be commented. Maybe these were added for clarity after copying to the post, but as is it won't compile.


'Entity Name

'CIF

The suggestion for With...End With statement was a suggestion to further clean up the appearance. Instead of continually stating wsCopyTo. , wrap the block in With wsCopyTo Then instead of:

wsCopyTo.Range(

The line can start with .

.Range(

With wsCopyTo
  'unprotect sheets for CDRF (1)
  .Unprotect Password:="hammer" 

  'Entity Name
  .Range("B11") = wsCopyFrom.Range("E30")
end with

Updated code for the snippet you provided

  Set wsCopyFrom = ActiveWorkbook.Worksheets("STP_Data")
  Set wsCopyTo = ActiveWorkbook.Worksheets("CDRF File (1)")

  With wsCopyTo
    'unprotect sheets for CDRF (1)
    .Unprotect Password:=pwStr
  
    'Entity Name
    .Range("B11") = wsCopyFrom.Range("E30")
  
    'unprotect sheets for CDRF (1)
    .Unprotect Password:=pwStr
  
    'CIF
    .Range("K11") = wsCopyFrom.Range("F30")
  
    'unprotect sheets for CDRF (1)
    .Unprotect Password:=pwStr
  
    'TIN
    .Range("M11") = wsCopyFrom.Range("G30")
  End With