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
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.
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?
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
is applied
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 | |||||
---|---|---|---|---|---|
B | C | D | |||
1 | Incoming Stock | ||||
2 | Date | Item | Cost | ||
3 | Friday, 13 November 2020 | BIB | GHC 200.00 | ||
4 | Saturday, 14 November 2020 | 24 Pieces Cupcake | GHC 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