CODEWITHSUNDEEP
vbaexcel
Peter Nguyen
Peter Nguyen

Reputation: 101

Multiple unprotect sheet VBA code

'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.

Upvotes: 1

Views: 716

Answers (2)

Tavious
Tavious

Reputation: 51

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

Upvotes: 0

0m3r
0m3r

Reputation: 12507

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"

Upvotes: 1

Related Questions