Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
Hi All, How to assign a range of cells to the dropdown list ? FORMULA1 seems not recognize the range of cells !
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Data" Then Exit Sub
Dim rngg As Range
Dim lr As Long
lr = Range("A2").End(xlDown).Row
Set rngg = Range("C3:AL" & lr)
On Error Resume Next
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, rngg) Is Nothing Then
With rngg.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Sheets("Data").Range("G1:G" & Range("G1").End(xlDown).Row)
.IgnoreBlank = True
.InCellDropdown = Truem
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub