Replace text selected from drop down list with a number (same cell)

[Excelbuddy_7]

Hopefully someone can help me on this one. Have tried looking online for a solution, but to no avail. Haven't done any macros in excel before.

I have a dropdown list. Example:

1 - Very Good
2 - Good
3 - Neutral
4 - Poor
5 - Very Poor

Once someone chooses an option from the drop down list, I want the cell to only show the numbers, rather than the whole phrase. It should also work if someone goes back and changes their mind and chooses a different option from the dropdown list.

For example; Say this dropdown list is in cell D3. From this drop down list, "2 - Good" is chosen. Now I want cell D3 to only show "2", rather than "2 - Good". This is so, I can use those numbers to find an average later on. Also I my list has long phrases, and I have space constraints on my excel sheet, and it will need to be printed later on

Any help will be deeply appreciated? Let me know if you need more information.

Thanks

 

[Fishboy]

Hopefully someone can help me on this one. Have tried looking online for a solution, but to no avail. Haven't done any macros in excel before.

I have a dropdown list. Example:

1 - Very Good
2 - Good
3 - Neutral
4 - Poor
5 - Very Poor

Once someone chooses an option from the drop down list, I want the cell to only show the numbers, rather than the whole phrase. It should also work if someone goes back and changes their mind and chooses a different option from the dropdown list.

For example; Say this dropdown list is in cell D3. From this drop down list, "2 - Good" is chosen. Now I want cell D3 to only show "2", rather than "2 - Good". This is so, I can use those numbers to find an average later on. Also I my list has long phrases, and I have space constraints on my excel sheet, and it will need to be printed later on

Any help will be deeply appreciated? Let me know if you need more information.

Thanks

Hi Excelbuddy_7, welcome to the boards.

What you are describing can be achieved using VBA and a Worksheet_Change event macro. It basically looks for changes to your data and then applied specific code relevant to the change. To try this out first make a COPY of your workbook as a backup. Next, right-click on the tab name of the sheet you want this to be applied to. In the VBA window that opens up simply copy and paste in the following code. You will need to save your document as a macro enabled workbook (.xlsm format).

Private Sub Worksheet_Change(ByVal Target As Range)
' If more than one cell is updated at once then exit sub
    If Target.Cells.Count > 1 Then Exit Sub
' If target column is A and target value is not blank then...
        If Target.Column = 1 And Target.Value <> "" Then
' Disable screen updating to reduce screen flicker
            Application.ScreenUpdating = False
' Disable events to prevent infinite loop
                Application.EnableEvents = False
' If target value is Very Good then change target cell to 1
                    If Target.Value = "1 - Very Good" Then
                        Target.Value = 1
' Else if target value is Good then change target cell to 2
                    ElseIf Target.Value = "2 - Good" Then
                        Target.Value = 2
' Else if target value is Neutral then change target cell to 3
                    ElseIf Target.Value = "3 - Neutral" Then
                        Target.Value = 3
' Else if target value is Poor then change target cell to 4
                    ElseIf Target.Value = "4 - Poor" Then
                        Target.Value = 4
' Else if target value is Very Poor then change target cell to 5
                    ElseIf Target.Value = "5 - Very Poor" Then
                        Target.Value = 5
                    End If
' Re-enable events
                Application.EnableEvents = True
' Re-enable screen updating
            Application.ScreenUpdating = True
        End If
End Sub

 

[Excelbuddy_7]

Hi Excelbuddy_7, welcome to the boards.

What you are describing can be achieved using VBA and a Worksheet_Change event macro. It basically looks for changes to your data and then applied specific code relevant to the change. To try this out first make a COPY of your workbook as a backup. Next, right-click on the tab name of the sheet you want this to be applied to. In the VBA window that opens up simply copy and paste in the following code. You will need to save your document as a macro enabled workbook (.xlsm format).

Private Sub Worksheet_Change(ByVal Target As Range)
' If more than one cell is updated at once then exit sub
    If Target.Cells.Count > 1 Then Exit Sub
' If target column is A and target value is not blank then...
        If Target.Column = 1 And Target.Value <> "" Then
' Disable screen updating to reduce screen flicker
            Application.ScreenUpdating = False
' Disable events to prevent infinite loop
                Application.EnableEvents = False
' If target value is Very Good then change target cell to 1
                    If Target.Value = "1 - Very Good" Then
                        Target.Value = 1
' Else if target value is Good then change target cell to 2
                    ElseIf Target.Value = "2 - Good" Then
                        Target.Value = 2
' Else if target value is Neutral then change target cell to 3
                    ElseIf Target.Value = "3 - Neutral" Then
                        Target.Value = 3
' Else if target value is Poor then change target cell to 4
                    ElseIf Target.Value = "4 - Poor" Then
                        Target.Value = 4
' Else if target value is Very Poor then change target cell to 5
                    ElseIf Target.Value = "5 - Very Poor" Then
                        Target.Value = 5
                    End If
' Re-enable events
                Application.EnableEvents = True
' Re-enable screen updating
            Application.ScreenUpdating = True
        End If
End Sub

Thanks a lot Fishboy. I will try this out and let you know

 

[Peter_SSs]

Welcome to the MrExcel board!

This should do it too

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3")) Is Nothing Then
    Application.EnableEvents = False
    Range("D3").Value = Trim(Split(Range("D3").Value & "-", "-")(0))
    Application.EnableEvents = True
  End If
End Sub

 

[Excelbuddy_7]

Welcome to the MrExcel board!

This should do it too

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3")) Is Nothing Then
    Application.EnableEvents = False
    Range("D3").Value = Trim(Split(Range("D3").Value & "-", "-")(0))
    Application.EnableEvents = True
  End If
End Sub

Thanks Peter_SSs. Will try it out

 

[Excelbuddy_7]

Fishboy and Peter_SSs. Thanks a lot to both of you. Both your methods work. The second method is quicker, as you don't have to manually enter the list data in the code. However, thanks once again.

 

[Excelbuddy_7]

Welcome to the MrExcel board!

This should do it too

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3")) Is Nothing Then
    Application.EnableEvents = False
    Range("D3").Value = Trim(Split(Range("D3").Value & "-", "-")(0))
    Application.EnableEvents = True
  End If
End Sub

Thanks once again. Also can you please tell me how do I edit the code for it to work for more than 1 cell; or for an entire column.

If possible, can you please explain what the code does? It works perfectly, though I would like to know how it works. Thanks

 

[Peter_SSs]

Also can you please tell me how do I edit the code for it to work for more than 1 cell; or for an entire column.

For an entire column:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rChanged As Range, c As Range
  
  'Set a range equal to all the changed cells in column D (there could be more than one at a time)
  Set rChanged = Intersect(Target, Columns("D"))
  
  'If something did change in columnD then
  If Not rChanged Is Nothing Then
    
    'Disable events so when/if we change acell on the sheet this whole code doesn't get called again
    Application.EnableEvents = False
    
    'Work through each of the column D changed cells
    For Each c In rChanged
      
      'Add a "-" at the end of the cell value then split that result into bits by dividing it up at each "-"
      'This creates an array of 'bits', the first of which is referenced by zero (0)
      'Take that first 'bit', trim off any leading/trailing spaces & put that value in the cell
      c.Value = Trim(Split(c.Value & "-", "-")(0))
      
    'Repeat for other changed cells in the column
    Next c
    
    'Re-enable events ready for the next change to the worksheet
    Application.EnableEvents = True
  End If
End Sub

For a range:

Set rChanged = Intersect(Target, Range("H5:J10"))

 

[Fishboy]

Fishboy and Peter_SSs. Thanks a lot to both of you. Both your methods work. The second method is quicker, as you don't have to manually enter the list data in the code. However, thanks once again.

No problem. Was happy to help but as always Peter has shown a far more elegant way to achieve the same goal

I can definitely see the benefit of using his code as you won't have to include the list in the code itself which as well as saving time also means it is future proof should your list get longer.

 

[Excelbuddy_7]

For an entire column:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rChanged As Range, c As Range
  
  'Set a range equal to all the changed cells in column D (there could be more than one at a time)
  Set rChanged = Intersect(Target, Columns("D"))
  
  'If something did change in columnD then
  If Not rChanged Is Nothing Then
    
    'Disable events so when/if we change acell on the sheet this whole code doesn't get called again
    Application.EnableEvents = False
    
    'Work through each of the column D changed cells
    For Each c In rChanged
      
      'Add a "-" at the end of the cell value then split that result into bits by dividing it up at each "-"
      'This creates an array of 'bits', the first of which is referenced by zero (0)
      'Take that first 'bit', trim off any leading/trailing spaces & put that value in the cell
      c.Value = Trim(Split(c.Value & "-", "-")(0))
      
    'Repeat for other changed cells in the column
    Next c
    
    'Re-enable events ready for the next change to the worksheet
    Application.EnableEvents = True
  End If
End Sub

For a range:

Set rChanged = Intersect(Target, Range("H5:J10"))

Thanks Peter_SSs for your reply. However, this code doesn't seem to be working, unless I have done something wrong. I tried both the column one and the range one.