drop down list displaying number and text but only storing number

D_Miller

New Member
Joined
Dec 17, 2019
Messages
15
Office Version
  1. 2016
Hi Excel Gurus,

I want to create a drop down list with a set of values in a range from 1 to 5 but I also want the user to have an explanation of what the range means. E.g"
1 - Very Low
2. - Low
3 - Medium
4 - High
5 - Very High.

I want to show the above in the drop down list. However after the user selects and option I would like the number (1-5) only to be stored in the cell. The reasons for this is I would like to some analysis on the enter numeric scores.

Any assistance will be greatly appreciated.
 
When I make the columns smaller the writing in the list also shrinks so that the persons who needs to select the options cannot fully see the text. Is there a way to have the entire list data shown irregardless of the column size?
Could you use a work-around like this? Suppose you have these Data validations in range B1:B10 and column B has a width of 10 but really needs to have a width of 20 for the user to see the list well, then you could try this Worksheet_SelectionChange event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Intersect(ActiveCell, Range("B1:B10")) Is Nothing Then
    Columns("B").ColumnWidth = 10
  Else
    Columns("B").ColumnWidth = 20
  End If
End Sub
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Oh wow Peter, your code is perfect! It gives me exactly what I need in a clean and workable way.

I just played with it a bit and made it easier (Dynamic) for someone who doesn't know VBA to use and update the columns as their needs arises.

VBA Code:
Dim strRge As String
Dim LowerRowNo, UpperRowNo As Integer
Dim i As Integer
Dim colList As Variant
  
' names of the columns which the code should be applied to. ie. columns to widen
' if you want to include a new column then add the letter ot the array below
colList = Array("D", "F", "H", "N", "O", "P")

LowerRowNo = 1 ' build the rows
UpperRowNo = 100

For i = 0 To UBound(colList)
    strRge = colList(i) & LowerRowNo & ":" & colList(i) & UpperRowNo ' build the range to to work with

    If Intersect(ActiveCell, Range(strRge)) Is Nothing Then
      Columns(colList(i)).ColumnWidth = 4.5
    Else
      Columns(colList(i)).ColumnWidth = 10
    End If
Next i

Again thanks to everyone who contributed to helping me resolve this problem. I hope someone in the future finds this entire thread helpful. :)

Dane
 
Upvote 0
I hope someone in the future finds this entire thread helpful.
That is one of the underlying purposes of the forum. By far the majority of forum traffic is unregistered just search for answers.

I am hoping though that this thread might be even more useful as I I have a few comments about your code. I am not suggesting that you must change anything if it is doing what you want, but you may consider these points/suggestions.

  1. You have used the type 'Integer' in some of your variable declarations. VBA converts Integer values to Long to work with them so you might as well declare them as long in the first place. (In any case it is shorter to type. ;))

  2. You have used the code
    Dim LowerRowNo, UpperRowNo As Integer
    Be aware that this only declares UpperRow No as Integer and LowerRowNo will be type Variant. You must specify every individual variable if you want a particular type. So if you wanted these both to be Integer type you would have needed
    Dim LowerRowNo as Integer, UpperRowNo As Integer

  3. I think cycling through the listed columns 1 at a time is inefficient so my suggested alternative code below eliminates that cycling.

Anyway, see what you think.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  ' names of the columns which the code should be applied to. ie. columns to widen
  ' if you want to include a new column then add the letter to the list below
  Const colList As String = "D,F,H,N,O,P"
  
  'rows with Data Validation where columns should be widened.
  Const rowList As String = "1:100"
  
  With Range(Replace(colList, ",", "1,") & 1)
    If Intersect(ActiveCell, .EntireColumn, Rows(rowList)) Is Nothing Then
      .EntireColumn.ColumnWidth = 4.5
    Else
      ActiveCell.EntireColumn.ColumnWidth = 10
    End If
  End With
  
End Sub
 
Upvote 0
Hi Peter.

Once again I appreciate your invaluable input. I am responding below in alignment with you numbered responses.

1. I did not know VBA converts integers to long. That's interesting, I must research that further.
2. Yes you are correct it is declared as variant. I missed that one. :)
3. I knew using the loop would not have been efficient, in terms of code efficiency. But before I posted my last set of code I tested the speed performance with the naked eye and users could not have noticed any difference in the code performance. So it was acceptable and I placed the code in production.

Needless to day, I always like better and more efficient ways of doing things so I tried your new suggested code. It does the job in question except for one issue. It keeps the cells open and does not return them to 4.5 when i come out and move to another cell. While the code with the loop (build from your initial code) and your first set of code resizes the column back to its original size each time the user goes to another cell, and this is preferred.

I like the elegance of:
VBA Code:
Range(Replace(colList, ",", "1,") & 1)

But from the functional side of using it, it is not giving me 100% of what i need.

PS: I like how this post turned out and I am confident someone in the future would find it useful. Cheers to all (y)
 
Upvote 0
I tested the speed performance with the naked eye and users could not have noticed any difference in the code performance. So it was acceptable and I placed the code in production.
That's a fine approach to take. :)

It keeps the cells open and does not return them to 4.5 when i come out and move to another cell.
It does reduce them if you move to another cell that is not in the target range. However, leaving it 'open' if you move to a 'target' cell in a different column was not my intention so it was an oversight. I think this code fixes that issue (& it's shorter too. :))

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  ' names of the columns which the code should be applied to. ie. columns to widen
  ' if you want to include a new column then add the letter to the list below
  Const colList As String = "D,F,H,N,O,P"
  
  'rows with Data Validation where columns should be widened.
  Const rowList As String = "1:100"
  
  With Range(Replace(colList, ",", "1,") & 1)
    .ColumnWidth = 4.5
    If Not Intersect(ActiveCell, .EntireColumn, Rows(rowList)) Is Nothing Then ActiveCell.ColumnWidth = 10
  End With
End Sub
 
Upvote 0
Hi Peter.

Thanks again for your valuable input this works just fine! I owe you a drink when I meet you in person. :)

Cheers bro!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top