Macro does not work when sheet is renamed

Question

I am using a macro on a worksheet that needs to be protected. This is the macro:

wksPartsDataEntry.Unprotect
Sheet11.Unprotect
Application.ScreenUpdating = False

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myCopy As Range
Dim myTest As Range

Dim lRsp As Long

Set inputWks = wksPartsDataEntry
Set historyWks = Sheet11

'check for duplicate order ID in database
If inputWks.Range("CheckID2") = True Then
  lRsp = MsgBox("Clinic ID already in database. Update record?", vbQuestion + vbYesNo, "Duplicate ID")
  If lRsp = vbYes Then
    UpdateLogRecord
  Else
    MsgBox "Please change Clinic ID to a unique number."
  End If

Else

  'cells to copy from Input sheet - some contain formulas
  Set myCopy = inputWks.Range("OrderEntry2")

  With historyWks
      nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
  End With

  With inputWks
      Set myTest = myCopy.Offset(0, 2)

      If Application.Count(myTest) > 0 Then
          MsgBox "Please fill in all the cells!"
          Exit Sub
      End If
  End With

  With historyWks
      With .Cells(nextRow, "A")
          .Value = Now
          .NumberFormat = "mm/dd/yyyy hh:mm:ss"
      End With
      .Cells(nextRow, "B").Value = Application.UserName
      oCol = 3
      myCopy.Copy
      .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
      Application.CutCopyMode = False
  End With

  'clear input cells that contain constants
  With inputWks
    On Error Resume Next
       With myCopy.Cells.SpecialCells(xlCellTypeConstants)
            .ClearContents
            Application.GoTo .Cells(1) ', Scroll:=True
       End With
    On Error GoTo 0
  End With
End If

Application.ScreenUpdating = True
wksPartsDataEntry.Protect
Sheet11.Protect
End Sub

The macro works fine. However I will distribute the file to other users that will want to use passwords to protect their sheets. Every user will want to use a different password. Adding a password in the code is not an option, since that password would be unique, and I want other users to be able to add their own password when protecting. Does a code exist that could do something like:

Sub Macro1()
wksPartsDataEntry.Unprotect Password: (anything a user might choose as a password)
Sheet11.Unprotect: (anything a user might choose as a password)

So in the end my macro would unprotect and reprotect based on the password that the user chooses himself, without the user having to change any code whatsoever.

Hope I have been clear enough, thanks for any answer!

Jook Jook · Accepted Answer · 2012-10-19T11:18:00

Yes there is - you should just lookup unprotect in the excel-help:

wksPartsDataEntry.Unprotect(password) is possible, whereby password is an optional parameter, so you can leave it out.

In order to do as you need to, try this:

Public Sub MyUnprotect()
  wksPartsDataEntry.Unprotect InputBox( _
                prompt:="Please type your password to unprotect:", _
                Title:="Unprotect")

End Sub

You can extend this for protect too, i.e. by saving the given password as a variable.

If you want to have stars to hide the password input, you will have to create your own UserForm, which you would call instead of InputBox. There you can setup an input field with hidden input.

Macro does not work when sheet is renamed

1 Answers