VBA Code to change cell texts based on dropdown list selection

[Brendon Donald]

Hi,

Looking for a code that will change a number of individual cells based on a dropdown list selection.

Example,
Cell A1 dropdown selection is either "miles/chains" or "miles/yard" and I need cells C3, C9, C16 & C30 to change to "ch" if "miles/chains" is selected or to "yds" if "miles/yards" is selected?

Thanks in advance.

 

[Jeffrey Mahoney]

Help us to help you. What kind of pulldown is it? A validation list or an actual pulldown control? Are you storing the current selection in a cell? It is best to give that cell a name for easier reference. You should highlight all those cells you want to change and give those a name also. Do you want to trigger the macro when you make a change to the dropdown, or a button?

I named the cell A1 "DistanceChoice" and the 4 cells you want to change "DistanceCells". Add this code to the SHEET module. This code works only if the cell A1 or "DistanceChoice" has changed. The picture below shows the code in Sheet3 because that's where the pulldown is.

Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim i As Range
  
  Set i = Intersect(Range("DistanceChoice"), Target)
  If Not i Is Nothing Then
    If i.Value = "" Then Exit Sub
    Application.EnableEvents = False
    
    Select Case i.Value
      Case "Miles/Chains"
        Range("DistanceCells").Value = "ch"
      Case "Miles/Yards"
        Range("DistanceCells").Value = "yds"
    End Select
    
    Application.EnableEvents = True
  End If
  
    
End Sub

 

[Brendon Donald]

Thanks Jeffrey, that works, it was a validation list. Also thanks for pointing me in the direction of naming cells, that will help me out in the future. Thanks,

 

[Jeffrey Mahoney]

You're welcome. Please mark post #2 as the solution.

 

[bebo021999]

to make rangechoice dynamic (could be A1, A2 or other cell), and result range could be anywhere in worksheet
in C3, type UDF (User Defined Formula)
=unit($A$1)
Copy C3 to any other result cells.
with source as following:

Option Explicit
Function Unit(ByVal cell As Range)
    If InStr(6, cell, "ch") Then
        Unit = "ch"
    ElseIf InStr(6, cell, "yard") Then
        Unit = "yds"
    End If
End Function