CODEWITHSUNDEEP
excelvba
James42
James42

Reputation: 1

vba code that runs fine in Excel 2007 will not run in Excel 2013

I have some vba code that adds user names and passwords to set user edit ranges.
The code works beautifully in Excel 2007 but when I try to run it in Excel 2013 I keep getting a Runtime error 1004 Application defined or object defined error. The code stops at the line set erTimeInputs. I believe it is because the variable erTimeInputs has a value of nothing but I cannot get it to accept a value. I have tried to define it as various data types but nothing seems to work.

Here is my code:

Sub AddUserEditRanges()

'Update username and password before running this macro

Dim UserNameArr() As Variant
Dim UserRangeArr()  As Variant
Dim UserPsswdArr() As Variant
Dim v As Variant
Dim LastUserRow As Integer
Dim i As Integer
Dim ws As Worksheet, rng As Range, UserRange As Range, aer As AllowEditRange
Dim UserName As String
Dim Psswd As String
Dim erTimeInputs as Variable

'Find number of users
LastUserRow = Range("A170").End(xlDown).Row

'set arrays
UserNameArr = Range("B168:B" & LastUserRow).Value
UserPsswdArr = Range("C168:C" & LastUserRow).Value
UserRangeArr = Range("D168:D" & LastUserRow).Value

    For v = LBound(UserNameArr) To UBound(UserNameArr)

    UserName = UserNameArr(v, 1)
    UserRange = UserRangeArr(v, 1)
    Psswd = UserPsswdArr(v, 1)

        'Add edit ranges to the worksheets for all users.
       Set erTimeInputs = _
              Worksheets("Week1").Protection.AllowEditRanges.Add(Title:= _
              UserName, Range:=Worksheets("Week1") _
              .Range(UserRange), Password:=Psswd)

        Set erTimeInputs = _
              Worksheets("Week2").Protection.AllowEditRanges.Add(Title:= _
              UserName, Range:=Worksheets("Week2") _
              .Range(UserRange), Password:=Psswd)
    Next


End Sub

Upvotes: 0

Views: 523

Answers (1)

L42
L42

Reputation: 19737

As commented you can declare your variable as AllowEditRange variable type explicitly.
Now, there should be no problem as long as the Title you assign to it does not exist, else it will throw the run-time error.
I don't know why you need to set or assign it to a variable object, but if what you want is just to add username and password, you can do it without assigning it to a variable.

For v = LBound(UserNameArr) To UBound(UserNameArr)
    UserName = UserNameArr(v, 1)
    UserRange = UserRangeArr(v, 1)
    Psswd = UserPsswdArr(v, 1)
    'Check if worksheet is unprotected and ready for editing
    If Worksheet("Week1").ProtecContents = True Then Msgbox "Unprotect Sheet first", _
        vbCritical: Exit Sub

    'Add edit ranges to the worksheets for all users.
    If AllowEditExist(UserName, Worksheets("Week1")) Then
        'Delete Existing
        Worksheets("Week1").Protection.AllowEditRanges(UserName).Delete
    End If
        'Add New 
    Worksheets("Week1").Protection.AllowEditRanges.Add Ttle:= _
        UserName, Range:=Worksheets("Week1") _
        .Range(UserRange), Password:=Psswd
Next

Edit1: Below routine will check if the AllowEditRange already exist.

Function AllowEditExist(mytitle As String, sh As Worksheet) As Boolean
    Dim a As AllowEditRange: AllowEditExist = False
    For Each a In sh.Protection.AllowEditRanges
        If a.Title = mytitle Then
            AllowEditExist = True
            Exit For
        End If
    Next
End Function

Important: You should unprotect the worksheets before you add, delete AllowEditRanges, it will generate error otherwise.

Upvotes: 2

Related Questions