Excel : Changing Cell Dropdown Source via VBA

I’ve just had to work out how to change the cell drop down source of an Excel cell based on the value of another cell. In this case I had one drop down with the Australian states in it and I wanted this to drive a second dropdown that allows users to select the Federal Electorate. It turned out to be pretty easy (and quite nifty). My spreadsheet is setup with a series of named ranges containing the state electorates. So, for example, there’s a named range called ElectoratesNSW containing all the electorates for NSW. Just add the following to the sheet where you want the data validation magic to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sState As String
  Dim rngTmp As Range
  '
  ' Cell in row 7 and column 3 is the State dropdown
  '
  If Target.Row = 7 And Target.Column = 3 Then
    '
    'Cell in row 7, column 4 is the electorate dropdown
    '
    Set rngTmp = Me.Cells(7, 4)
    If Len(Target.Value) > 1 Then
      sState = Target.Value
      rngTmp.Formula = ""
      With rngTmp.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & "Electorates" & UCase(sState)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    Else
      rngTmp.Validation.Delete
      rngTmp.Formula = ""
    End If
  End If

End Sub
This entry was posted in Software on by .

About markn

Mark is the owner and founder of Timesheets MTS Software, an mISV that develops and markets employee timesheet and time clock software. He's also a mechanical engineer, father of four, and a lifelong lover of gadgets.