Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
Hi All,
Is it possible to have the dropdown list box to show values of 2 columns (G and H) from the table range.
The code that I currently use only shown column G. How to modify it ?
Is it possible to have the dropdown list box to show values of 2 columns (G and H) from the table range.
The code that I currently use only shown column G. How to modify it ?
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 lr1 As Long, lrg As Long
'Range to apply Data Validation
lr1 = Range("A3").End(xlDown).Row
Set rngG = Range("C3:AL" & lr1)
'List in Sheet("Data")
Dim strG As String, strGH As String
lrg = Sheets("Data").Range("G1").End(xlDown).Row
strG = "='Data'!$G$2:$G" & lrg
'On Error Resume Next
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, rngG) Is Nothing Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strG
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub