Dear forum,
I´m trying to code a in cell dropdown list.
First I recorded a macro, and after that, I´m trying to ustomize the code for VBA
Sub inCell()
Dim ws2 As Worksheet, ws3 As Worksheet
Dim rngList As Range, lr2 As Long
Set ws2 = Sheet2
Set ws3 = Sheet3
lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
Set rngList = ws2.Range(ws2.Cells(2, 5), ws2.Cells(lr2, 5))
'E2 on Sheet 2 I want insert the drop down list
With ws3.Cells(2, 5).Validation
.Delete
'replace "=A1:A6" with the range the data is in.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=rngList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
when I run debug (F8) this code
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=rngList
gives
Run-time error 1004
Application-defined or object defined error
I would appreciate your help solving this problem
Thanks in advance
Regards
Peter
Bookmarks