CODEWITHSUNDEEP
vbarange
Helen
Helen

Reputation: 607

VBA: Protection.AllowEditRanges with dynamic range

Yesterday I asked a question (Marking an area with a double 'For' loop) and @Excel Hero gave me a nice response. I have used his code to store a range in a variable 'k':

Sub ErosRam()
    Dim i&, j&, area$, k As Range, r As Range

    Const COL_PERIOD = 3
    Const ROW_PERIOD = 3
    Const REPS_HORIZONTAL = 3
    Const REPS_VERTICAL = 2

    Set r = [f7:g8]
    Set k = r

    For i = 0 To REPS_VERTICAL - 1
        For j = 0 To REPS_HORIZONTAL - 1
            Set k = Union(k, r.Offset(i * ROW_PERIOD, j * COL_PERIOD))
        Next
    Next

    area = k.Address(0, 0)
    MsgBox area   
End Sub

Now, I wish to keep the area given by 'k' unlocked, and lock the rest. So I wish to use something like:

     ActiveSheet.Protection.AllowEditRanges.Add Title:="test", Range:=Range(k)

But this does not work. How can I use a variable range inside of protection.alloweditranges?

Upvotes: 1

Views: 1723

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

It is already a range, so you do not want to wrap k with a Range() method:

ActiveSheet.Protection.AllowEditRanges.Add Title:="test", Range:=k

Upvotes: 1

Related Questions