Lock cells after data entry without worksheet code inflicting

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
Hi.
So i've been using this VBA code i found on the internet for searchable dropdown list.....
for every sheet that i want to have a searchable dropdown list, i copy and paste the code in the worksheet then create an Active X combo box for it and any part of the sheet where a data validation is present, the combo box is applied on. the code is below

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
    
End Sub

But i needed to lock every cell that data has been entered and data can only be changed by an admin or supervisor by first unprotecting sheet.
i used this code below for it and it works just fine when the combo box code above is not present in the worksheet code.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
Target.Locked = True
ActiveSheet.Protect Password:="password"
End Sub

Now, i need to have a searchable dropdown list and also let the cells lock after data goes in.
so i added all the subs in the worksheet code. but i realized that the cells that the combo box is in effect is always locked and cant let any data be entered
but the other cells does work - data is entered and locked within cells that the combo box is not in effect

so i'd want to ask, how can i structure these codes for it to do what i want , or how can i structure it to only lock the combo box cells after the data is entered?

POSv2-Shops.xlsm
BCD
1Incoming Stock
2DateItemCost
3Friday, 13 November 2020BIBGHC 200.00
4Saturday, 14 November 202024 Pieces CupcakeGHC 100.00
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Incoming Stock


in the above table, the middle column is where data validation is applied which the combo box uses.
P.S all cells in the table protection is unlocked before the code
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
Target.Locked = True
ActiveSheet.Protect Password:="password"
End Sub
is applied
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,214,943
Messages
6,122,376
Members
449,080
Latest member
Armadillos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top