Add to recorded macro "auto complete"

[Tim Hanson]

Hello,

Is there a way to add auto complete to this Macro so that when a user starts typing in a cell in column A it auto completes

Thank you

Sub CreateDV()
Dim rng As Range
Dim cell As Range
Dim lRow As Long
Dim ws As Worksheet

Sheets("New").Select
Set ws = Sheets("New")

  lRow = ws.Range("A" & ws.Rows.count).End(xlUp).Row

  Set rng = Range("A4:A" & lRow)

With rng.Validation
  For Each cell In rng
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  Next cell
End With
End Sub

 

[SirJB7]

Hi, Tim Hanson!

This macro is manually run or called from another code. Autocomplete takes place when the user is typing. So how would this macro be triggered in that moment?

Regards!

 

[Tim Hanson]

Hello SirJB7, your right, the macro could not be triggered I was hoping the macro could set a auto complete condition in excel if excel had one.

The question I guess is rooted in a certain amount of ignore of excel and vba.

 

[SirJB7]

Hi, Tim Hanson!
Glad you help you even if you didn't solve it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Don't forget this Albert Einstein's quote: "We are all very ignorant, what happens is that not all ignore the same things". And I firmly believe it.