Excel Forum

Code: Select all

Sub MyValidation(ByVal KeyCode As Long)
    Dim strText As String, strList As String
    If Not TypeOf [a1] Is Range Then Exit Sub

    strText = [a1].Value & Chr(KeyCode)
    strList = MakeArr(strText)
    [a1].Value = strText
    If strList = "False" Then
        [a1].Validation.Delete
    Else
        With [a1].Validation
            .Delete
            .Add 3, 1, 1, Formula1:=strList
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
        End With
    End If
End Sub

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
    KeyEventOn
Else
    KeyEventOff
End If
End Sub

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then
     Worksheets(Target.Value).Select
     Range("A1").DropDown
 End If
 Application.EnableEvents = True
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then
    MyValidation
 End If
 Application.EnableEvents = True
End Sub

Code: Select all

Sub MyValidation()
On Error Resume Next
    With [A1].Validation
        .Modify Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=ValidateVals"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = False
    End With
[A1].Select
Application.SendKeys "%{DOWN}"
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
....
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" And Len(Target) < 3 Then
    MyValidation
 End If
 Application.EnableEvents = True
End Sub