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

 

[Excelbuddy_7]

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.

Thanks once again Fishboy for your help

 

[Peter_SSs]

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.

Perhaps your 'Events' have become disabled. In a standard module put this short macro & run it, then try again.

Sub EE()
  Application.EnableEvents = True
End Sub

 

[Excelbuddy_7]

Perhaps your 'Events' have become disabled. In a standard module put this short macro & run it, then try again.

Sub EE()
  Application.EnableEvents = True
End Sub

Thanks a lot Peter_SSs. I tried it on a new excel workbook, and it worked fine. I will remember this code as well, in case it decides to play up again. Thanks for all your help

 

[Peter_SSs]

Glad you got operational again.

 

[Excelbuddy_7]

Also, Peter_SSs (or anyone else). If I wanted to learn coding and macros from scratch in Excel, where would be a good place to start. Can you please suggest any websites or anything for that matter?

Thanks

 

[Peter_SSs]

Also, Peter_SSs (or anyone else). If I wanted to learn coding and macros from scratch in Excel, where would be a good place to start. Can you please suggest any websites or anything for that matter?

Thanks

I started in this forum. It is why I joined in the first place.

 

[Excelbuddy_7]

I started in this forum. It is why I joined in the first place.

Ok thanks.

 

[Excelbuddy_7]

Hi Peter_SSs. Can you please take a look at "http://www.mrexcel.com/forum/excel-questions/900853-auto-adjust-cell-height-row-height-adjacent-merged-cells.html". I have used your code, and was having another issue (also the second bit of code in that forum slows down the code for the dropdown lists).

Thanks

 

[Excelbuddy_7]

I started in this forum. It is why I joined in the first place.

Hi Peter_SSs. The code is working fine. However, I just have one more question. I want to run a macro (to adjust column width) when I select a particular cell. I have a code for this (also uses your code for the original issue)

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Not Application.Intersect(Target, Range("BD11:BD34")) Is Nothing Then
  Call columnwidth
  End If
  
  
  '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, Range("BD11:BP34"))
  
  '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 some reason, the macro doesn't run until I select a cell and then press 'Delete' on the keyboard. When I try to use just this code:

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Not Application.Intersect(Target, Range("BD11:BD34")) Is Nothing Then
  Call columnwidth
  End If
  End Sub

...on another sheet, it works fine. As soon as I click the desired cell, the macro is run. But for the first code I need to select the cell and press 'Delete' for the macro to activate.

Hope you can help.

 

[Excelbuddy_7]

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Not Application.Intersect(Target, Range("BD11:BD34")) Is Nothing Then
  Call columnwidth
  End If
  End Sub

Actually, just realised that even this code doesn't do the job until I actually press delete or type something (change something in the worksheet). I guess this is because this is a worksheet_change event. What event can I call it, if I just want it to activate the macro when I select the cell?