need cells so that they are NOT protected in named ranges, but everywhere else locked...

[kbishop94]

I'm reluctant to even post this and ask... as its NOT a big deal if I cant figure it out. I can live without having this worksheet protected in the way that I'd like it to be. But, since its kind of an odd-ball request, I'd thought I would post this and ask. By googling and searching this site, I was able find numerous examples of code that is close to what I am wanting it to do, but it doesnt quite work the way that i'd like it to.

Below is the code that I found that i changed around and got it to partially work the way I want it to... but... well, its doing something really strange and its got me curious as to why its doing this so maybe someone here has an answer.

Ok, so here is what I have:

The Worksheet has 3 named ranges: "RAWS", "PACKAGING" and "OTHER". What I am trying to do is lock ALL the cells on the worksheet except those that are part of one of the named ranges. (basically all the BLANK cells are going to be locked. But doing that wont quite work the way that I need it to because I have a userform that when it inserts new data it increases the named range, plus there are blank cells that might be added in by the form.)

Like I said, I have something that partially works, but it behaves... weird. (see picture below) It also only works with only 1 of the ranges (cant figure out how to add the other 2 ranges to the code.)

Ok, so here is the 'weird' part is if I click somewhere off to the right in the worksheet (away from one of the named ranges), and in the area that is PROTECTED, it wont let me type in that particular cell (which is good, thats what its supposed to do), but instead in that same row, all the over to the left, and IN that named range, what i typed shows up over there. (see picture below.)

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


Worksheets("VendorList").Unprotect Password:="seatex"


Const RAWS As String = "RangeToBeLock"


    With ActiveWorkbook.Worksheets("VendorList").Range("RAWS")


        .Locked = False
        .Parent.Protect
        .Parent.EnableSelection = xlUnlockedCells
        .Parent.EnableSelection = xlNoRestrictions


    End With


End Sub

Here is a picture that (hopefully) does a better job describing what the "weird" thing its doing than my description above...

So thanks for reading this and FREE CAKE to whoever can tell me why its doing that weird thing when I click in a protected area and type something it shows up over in the non-protected area in that same row.

(ok, disclaimer... it wont be real cake as it wouldnt stay fresh shipping it to wherever I'd need to so I will just show you a picture of somebody's tasty cake.)

 

[Yongle]

Just curious. In which sheet's module is this code?

try this

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet, a As Variant
    Set ws = ActiveWorkbook.Sheets("VendorList")

    ws.Unprotect Password:="seatex"
    
    ws.Cells.Locked = True
    [RAWS].Locked = False
    [PACKAGING].Locked = False
    [OTHERS].Locked = False

    ws.protect Password:="seatex"

End Sub

 

[Yongle]

, a As Variant can be deleted from code in post#2
(uncleared testing debris!)

 

[kbishop94]

Just curious. In which sheet's module is this code?

try this

Thank you! That worked perfect.

It is worksheet code. here is the complete code, if youre still curious. (pls feel free to recommend any changes I might want to make to improve functionality or if you see something that just looks funky... I'm a totally green and still learning VBA as best as I can.) Thank you again.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet, a As Variant
    Set ws = ActiveWorkbook.Sheets("VendorList")

    ws.Unprotect Password:="seatex"
    
    ws.Cells.Locked = True
    [RAWS].Locked = False
    [PACKAGING].Locked = False
    [OTHER].Locked = False

    ws.Protect Password:="seatex"

End Sub

Private Sub cmdRAW_NAME_Click()

Worksheets("VendorList").Unprotect Password:="seatex"
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
With ActiveWorkbook.Worksheets("VendorList").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("RAWS").Columns(2), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("RAWS")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply

End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("VendorList").Protect Password:="seatex"

End Sub

Private Sub cmdRAW_ID_Click()

Worksheets("VendorList").Unprotect Password:="seatex"
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
With ActiveWorkbook.Worksheets("VendorList").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("RAWS").Columns(1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("RAWS")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("VendorList").Protect Password:="seatex"

End Sub

Private Sub cmdRAW_DATE_Click()

Worksheets("VendorList").Unprotect Password:="seatex"
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
With ActiveWorkbook.Worksheets("VendorList").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("RAWS").Columns(3), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("RAWS")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("VendorList").Protect Password:="seatex"

End Sub

Private Sub cmdPACK_ID_Click()

Worksheets("VendorList").Unprotect Password:="seatex"
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
With ActiveWorkbook.Worksheets("VendorList").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("PACKAGING").Columns(1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("PACKAGING")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("VendorList").Protect Password:="seatex"

End Sub

Private Sub cmdPACK_NAME_Click()

Worksheets("VendorList").Unprotect Password:="seatex"
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
With ActiveWorkbook.Worksheets("VendorList").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("PACKAGING").Columns(2), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("PACKAGING")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("VendorList").Protect Password:="seatex"

End Sub

Private Sub cmdPACK_DATE_Click()

Worksheets("VendorList").Unprotect Password:="seatex"
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
With ActiveWorkbook.Worksheets("VendorList").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("PACKAGING").Columns(3), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("PACKAGING")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("VendorList").Protect Password:="seatex"

End Sub

Private Sub CommandButton1_Click()

Worksheets("VendorList").Unprotect Password:="seatex"
UserForm1.Show
Worksheets("VendorList").Protect Password:="seatex"
End Sub

 

[kbishop94]

and here is the UserForm code... with this part i strugggled alot and it took awhile to get it all working the way it I wanted it to.

Private Sub chkYes_Change()
If chkYes = True Then chkNo = False
End Sub
Private Sub chkNo_Change()
If chkNo = True Then chkYes = False
End Sub
Private Sub chkRaw_Change()
If chkRaw = True Then chkPack = False
If chkRaw = True Then chkOther = False
End Sub
Private Sub chkPack_Change()
If chkPack = True Then chkRaw = False
If chkPack = True Then chkOther = False
End Sub
Private Sub chkOther_Change()
If chkOther = True Then chkRaw = False
If chkOther = True Then chkPack = False
End Sub

Private Sub cmdAdd_Click()

    Dim llRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Additional Vendor Information")
    llRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    

[COLOR=#006400]' RAW MATERIALS[/COLOR]

If chkRaw = True Then
    Dim J As Long
    Dim c As Long

    With Sheets("VendorList")
        J = .Range("RAWS").Row
        c = .Range("RAWS").Column

        Do While Not IsEmpty(.Cells(J, c))
            J = J + 1
        Loop

        .Rows(J).EntireRow.Insert

[COLOR=#006400]        'Copy values, formulae, and formats from the previous row[/COLOR]
        .Rows(J).EntireRow.FillDown

Me.txtVendorCode.Value = UCase(Me.txtVendorCode.Value)
Me.txtVendorName.Value = UCase(Me.txtVendorName.Value)

[COLOR=#006400]        'Replace values with values from the form[/COLOR]
        .Cells(J, c + 0).Value = Me.txtVendorCode.Value
        .Cells(J, c + 1).Value = Me.txtVendorName.Value
        .Cells(J, c + 2).Value = Me.txtDate.Value
    
Range("RAWS").Resize(Range("RAWS").Rows.Count + 1, Range("RAWS").Columns.Count).Resize.Name = "RAWS"
ActiveWorkbook.Worksheets("Additional Vendor Information").Activate
With ws
    .Cells(llRow, 1).Value = Me.txtDate.Value
    .Cells(llRow, 2).Value = Me.txtVendorName.Value
    .Cells(llRow, 3).Value = Me.txtAddedBy.Value
    .Cells(llRow, 4).Value = Me.txtRequestedBy.Value
    .Cells(llRow, 7).Value = Me.txtVendorCode.Value
    .Cells(llRow, 8).Value = Me.txtOther.Value
    .Cells(llRow, 9).Value = Me.txtNotes.Value
    If chkYes = True Then Cells(llRow, 6).Value = "YES"
    If chkNo = True Then Cells(llRow, 6).Value = "NO"
    If chkRaw = True Then Cells(llRow, 5).Value = "Raw Material"
    If chkPack = True Then Cells(llRow, 5).Value = "Packaging Material"
    If chkOther = True Then Cells(llRow, 5).Value = "OTHER"

End With

With Range(Cells(4, 1), Cells(llRow, 9)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = 8210719
End With

ActiveWorkbook.Worksheets("VendorList").Activate
End With

[COLOR=#006400]' OTHER MATERIALS[/COLOR]

ElseIf chkOther = True Then

    Dim K As Long
    Dim d As Long

    With Sheets("VendorList")
        K = .Range("OTHER").Row
        d = .Range("OTHER").Column

        Do While Not IsEmpty(.Cells(K, d))
            K = K + 1
        Loop

        .Rows(K).EntireRow.Insert

[COLOR=#006400]        'Copy values, formulae, and formats from the previous row[/COLOR]
        .Rows(K).EntireRow.FillDown

Me.txtVendorCode.Value = UCase(Me.txtVendorCode.Value)
Me.txtVendorName.Value = UCase(Me.txtVendorName.Value)
[COLOR=#006400]
        'Replace values with values from the form[/COLOR]
        .Cells(K, d + 0).Value = Me.txtVendorCode.Value
        .Cells(K, d + 1).Value = Me.txtVendorName.Value
        .Cells(K, d + 2).Value = Me.txtDate.Value

Range("OTHER").Resize(Range("OTHER").Rows.Count + 1, Range("OTHER").Columns.Count).Resize.Name = "OTHER"

ActiveWorkbook.Worksheets("Additional Vendor Information").Activate

With ws
    .Cells(llRow, 1).Value = Me.txtDate.Value
    .Cells(llRow, 2).Value = Me.txtVendorName.Value
    .Cells(llRow, 3).Value = Me.txtAddedBy.Value
    .Cells(llRow, 4).Value = Me.txtRequestedBy.Value
    .Cells(llRow, 7).Value = Me.txtVendorCode.Value
    .Cells(llRow, 8).Value = Me.txtOther.Value
    .Cells(llRow, 9).Value = Me.txtNotes.Value
    If chkYes = True Then Cells(llRow, 6).Value = "YES"
    If chkNo = True Then Cells(llRow, 6).Value = "NO"
    If chkRaw = True Then Cells(llRow, 5).Value = "Raw Material"
    If chkPack = True Then Cells(llRow, 5).Value = "Packaging Material"
    If chkOther = True Then Cells(llRow, 5).Value = "OTHER"

End With

With Range(Cells(4, 1), Cells(llRow, 9)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = 8210719
End With

ActiveWorkbook.Worksheets("VendorList").Activate
End With

[COLOR=#006400]' PACKAGING MATERIALS[/COLOR]

ElseIf chkPack = True Then

    Dim L As Long
    Dim e As Long
    
    With Sheets("VendorList")
        L = .Range("PACKAGING").Row
        e = .Range("PACKAGING").Column

        Do While Not IsEmpty(.Cells(L, e))
            L = L + 1
        Loop

        .Rows(L).EntireRow.Insert

[COLOR=#006400]        'Copy values, formulae, and formats from the previous row[/COLOR]
        .Rows(L).EntireRow.FillDown

Me.txtVendorCode.Value = UCase(Me.txtVendorCode.Value)
Me.txtVendorName.Value = UCase(Me.txtVendorName.Value)

        .Cells(L, e + 0).Value = Me.txtVendorCode.Value
        .Cells(L, e + 1).Value = Me.txtVendorName.Value
        .Cells(L, e + 2).Value = Me.txtDate.Value

Range("PACKAGING").Resize(Range("PACKAGING").Rows.Count + 1, Range("PACKAGING").Columns.Count).Resize.Name = "PACKAGING"
ActiveWorkbook.Worksheets("Additional Vendor Information").Activate
With ws
    .Cells(llRow, 1).Value = Me.txtDate.Value
    .Cells(llRow, 2).Value = Me.txtVendorName.Value
    .Cells(llRow, 3).Value = Me.txtAddedBy.Value
    .Cells(llRow, 4).Value = Me.txtRequestedBy.Value
    .Cells(llRow, 7).Value = Me.txtVendorCode.Value
    .Cells(llRow, 8).Value = Me.txtOther.Value
    .Cells(llRow, 9).Value = Me.txtNotes.Value
    If chkYes = True Then Cells(llRow, 6).Value = "YES"
    If chkNo = True Then Cells(llRow, 6).Value = "NO"
    If chkRaw = True Then Cells(llRow, 5).Value = "Raw Material"
    If chkPack = True Then Cells(llRow, 5).Value = "Packaging Material"
    If chkOther = True Then Cells(llRow, 5).Value = "OTHER"

End With

ActiveWorkbook.Worksheets("Additional Vendor Information").Activate

With Range(Cells(4, 1), Cells(llRow, 9)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = 8210719
End With
ActiveWorkbook.Worksheets("VendorList").Activate
End With

[COLOR=#006400]'***********************************************************************************************************[/COLOR]

With ws
    .Cells(llRow, 1).Value = Me.txtDate.Value
    .Cells(llRow, 2).Value = Me.txtVendorName.Value
    .Cells(llRow, 3).Value = Me.txtAddedBy.Value
    .Cells(llRow, 4).Value = Me.txtRequestedBy.Value
    .Cells(llRow, 7).Value = Me.txtVendorCode.Value
    .Cells(llRow, 8).Value = Me.txtOther.Value
    .Cells(llRow, 9).Value = Me.txtNotes.Value
    If chkYes = True Then Cells(llRow, 6).Value = "YES"
    If chkNo = True Then Cells(llRow, 6).Value = "NO"
    If chkRaw = True Then Cells(llRow, 5).Value = "Raw Material"
    If chkPack = True Then Cells(llRow, 5).Value = "Packaging Material"
    If chkOther = True Then Cells(llRow, 5).Value = "OTHER"

End With

ActiveWorkbook.Worksheets("Additional Vendor Information").Activate

With Range(Cells(4, 1), Cells(llRow, 9)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = 8210719
End With

End If

[COLOR=#006400]' **************************************************[/COLOR]

ActiveWorkbook.Worksheets("VendorList").Activate
Unload Me
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

 

[Yongle]

"In which sheet's module is this code?"

The only reason I asked... if (and ONLY IF!) the code is sitting in the SHEET module for sheet "VendorList" then that sheet can be referred to using Me
(if it is in the sheet module of another sheet then IGNORE this comment!!)

and would then look like this

 Private Sub Worksheet_Change(ByVal Target As Range)
    [B]Me[/B].Unprotect Password:="seatex"
    [B]Me[/B].Cells.Locked = True
    [RAWS].Locked = False
    [PACKAGING].Locked = False
    [OTHER].Locked = False
    [B]Me[/B].Protect Password:="seatex"
End Sub

Me means different things to VBA - what determines it is where the code is placed
if code is a sheet module Me = that sheet
if code is in a userform module then Me = that userform
etc

 

[kbishop94]

The only reason I asked... if (and ONLY IF!) the code is sitting in the SHEET module for sheet "VendorList" then that sheet can be referred to using Me
(if it is in the sheet module of another sheet then IGNORE this comment!!)

and would then look like this

 Private Sub Worksheet_Change(ByVal Target As Range)
    [B]Me[/B].Unprotect Password:="seatex"
    [B]Me[/B].Cells.Locked = True
    [RAWS].Locked = False
    [PACKAGING].Locked = False
    [OTHER].Locked = False
    [B]Me[/B].Protect Password:="seatex"
End Sub

Me means different things to VBA - what determines it is where the code is placed
if code is a sheet module Me = that sheet
if code is in a userform module then Me = that userform
etc

Thank you for that clarification. I (kinda) understood the Me keyword... like when I am on the code page for a userform, I knew that Me refers to that form. Your explanation of it referencing whatever module it is in makes total sense now. (I think I interpreted what you said correctly(?) ) Thank you, sir!

 

[kbishop94]

Well, unfortunately I did run into another issue with the code now, however.

So before, (and I mean either in this workbook or one of the other ones I use and have other employees here use) whenever something was being executed (like clicking on a commandbutton that performed a change on the worksheet) on a protected sheet or protected workbook, I always prefaced that code with this:

Worksheets("VendorList").Unprotect Password:="seatex"

And then 're-protected' the sheet by having this at the end of that procedure:

Worksheets("VendorList").Protect Password:="seatex"

But, with the procedure im using now for only protecting the cells that lay outside any of those previously mentioned 3 ranges (RAWS, PACKAGING and OTHER), and that procedure being in a "worksheet change" event, I cannot use that "Unprotect-Procedure-Protect" method. Is there a quick way to address this or get around it? Or... will it require a totally different approach for protecting cells outside of the 3 ranges? I dont want to invest too much time (mine or yours) so if its going to be something that is going to be alot more complicated to accomplish this, then I think we should just call it a day... lol

I tried using this code hoping it would unprotect the needed cells while the code was being executed (that code is inserting the data previsouly entered on the form into the range for 'RAWS' on the worksheet and increasing that range by 1 row. but... I still get an error. Its in red where I tried to unprotect it for the event where its attempting to copy the data over and insert a new row into the range. (btw, when i keep saying 'row'... its not an entire new row... its only 3 collumns wide within the RAWS range.)
The code below is not the complete userform code (the whole code is listed above in one of my previous posts) this is just a snippet for the specific 'if' part to cover if its supposed to insert the data within the 'RAWS' range.

If chkRaw = True Then
    Dim J As Long
    Dim c As Long
    
[B][COLOR=#ff0000]Worksheets("VendorList").Unprotect Password:="seatex"[/COLOR][/B]


    With Sheets("VendorList")
        J = .Range("RAWS").Row
        c = .Range("RAWS").Column


        Do While Not IsEmpty(.Cells(J, c))
            J = J + 1
        Loop


        .Rows(J).EntireRow.Insert
        
[B][COLOR=#ff0000]Worksheets("VendorList").Unprotect Password:="seatex"[/COLOR][/B]


        'Copy values, formulae, and formats from the previous row
        .Rows(J).EntireRow.FillDown


Me.txtVendorCode.Value = UCase(Me.txtVendorCode.Value)
Me.txtVendorName.Value = UCase(Me.txtVendorName.Value)


        'Replace values with values from the form
        .Cells(J, c + 0).Value = Me.txtVendorCode.Value
        .Cells(J, c + 1).Value = Me.txtVendorName.Value
        .Cells(J, c + 2).Value = Me.txtDate.Value
    
Range("RAWS").Resize(Range("RAWS").Rows.Count + 1, Range("RAWS").Columns.Count).Resize.Name = "RAWS"
ActiveWorkbook.Worksheets("Additional Vendor Information").Activate
With ws
    .Cells(llRow, 1).Value = Me.txtDate.Value
    .Cells(llRow, 2).Value = Me.txtVendorName.Value
    .Cells(llRow, 3).Value = Me.txtAddedBy.Value
    .Cells(llRow, 4).Value = Me.txtRequestedBy.Value
    .Cells(llRow, 7).Value = Me.txtVendorCode.Value
    .Cells(llRow, 8).Value = Me.txtOther.Value
    .Cells(llRow, 9).Value = Me.txtNotes.Value
    If chkYes = True Then Cells(llRow, 6).Value = "YES"
    If chkNo = True Then Cells(llRow, 6).Value = "NO"
    If chkRaw = True Then Cells(llRow, 5).Value = "Raw Material"
    If chkPack = True Then Cells(llRow, 5).Value = "Packaging Material"
    If chkOther = True Then Cells(llRow, 5).Value = "OTHER"


End With

Thanks for any additional help or suggestions you might want to assist me with. (and if not, thats perfectly fine. I know this is tunring into something probably requiring more time than you want to invest in.)

 

[kbishop94]

Here is the error that occurs when the 'cmdAdd' button is clicked. Depending on where i put:

Worksheets("VendorList").Unprotect Password:="seatex"

I get a different error. The other error was telling me its protected and cannot be performed.

 

[Yongle]

Busy for next 48 hours. So will not look at thread in that time.

But I would suggest you quickly "prove" that sheet protection is the issue by removing it and commenting out those lines of code referring to protect or unprotect.
Run the code to establish that it runs error free.

Then put the protection back in a staggered fashion one element at a time until the code fails again
- start by unlocking ALL cells
- perhaps the protection is over-protecting (does the code perhaps need a line to allow all cells to be capable of being selected even if they cannot be edited?)