Display two columns in Data Validation list but return only one.

[JWGoldfinch]

I have a need for the user to enter a code in a cell (Example AUT) however when I display the drop down list I would like the user to see something like (AUT - AUTOCAR), but when the user select it I only want "AUT" to be returned to the cell. I have tried combining the columns for the list, but when the user selects the value it returns the :AUT - AUTOCAR". Any Ideas

 

[bertie]

Excel doesn't have an input mask that I know of that would do this sort of thing.

You could create create an input mask with vba, using a Worksheet_Change event to strip out the characters before the hyphen.

The sample code assumes the validation list is in column 2. Edit if necessary.
Right click the sheet tab and select View Code.
Copy and paste.
Try changing the value in the validation list.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim strInput As String
   
   'assumed column B
   If Target.Column <> 2 Then Exit Sub
   
   'find the hyphen and extract the required text
   strInput = Trim(Mid(Target.Value, 1, Application.Find("-", Target.Value) - 1))
   
   'disable events before writing to worksheet
   On Error Resume Next
   Application.EnableEvents = False
      Target.Value = strInput
   Application.EnableEvents = True
End Sub

 

[JWGoldfinch]

This worked like a charm, Thank you

There is one issue, when you try delete an incorrect selection instead of just replacing the selection like you want to "Clear out the cell" then you get "Run time error 13 - Type mismatch", but I think I could live with this. because hopefully the user would not be doing this very much.

 

[bertie]

Try adding another line to the "exclusion" list:

   'assumed column B
   If Target.Column <> 2 Then Exit Sub
   If Target.Value = "" Then Exit Sub

 

[JWGoldfinch]

That did it, thank you again. Just now getting into learning VBA, and this makes a lot of sense. I still struggle but when I look at some code I can see how it works. Not taking any actual courses, but maybe it is time I did. Thanks again

 

[Walshy2851]

Hi there,

I was hoping someone might be able to help me. I had been using the above formula in combination with a normal data validation drop down list.

However, I now need to use a combo box rather than just standard data validation. I am using some code to edit my Excel spreadsheet so that the combo box appears in any cell with data validation.

My problem is as follows: When I choose from the drop down list using a combo box, the whole string of text appears.. I only want the piece before the hypen to appear once I have chosen the entry.

For example, in the drop down list there is an entry as follows '2 - Road Transport'. Once selected, I only want '2' to appear in the cell.

Can anybody help please?

The code I am using for the combo box to appear in any cell with data validation is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler


If Target.Count > 1 Then GoTo exitHandler


Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If


On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If


exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler


End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp


Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
'====================================

 

[bertie]

Hi Walshy,

If all your CombBox value have a leading numeric value you can use the Val() function to extract it.

Sub test01()
   Dim tmp As String
   
   tmp = "2 - Road Transport"
   tmp = Val(tmp)
   
   MsgBox tmp
End Sub

Or you can determine the position of the hyphen and trim off the characters to the left.

Sub test02()
   Dim tmp As String
   Dim pos As Long
   
   tmp = "2 - Road Transport"
   
   'find the position of the hyphen in the string
   pos = InStr(tmp, "-")
   
   'trim off the part you need
   tmp = Trim(Left(tmp, pos - 1))
   
   MsgBox tmp
End Sub

 

[Walshy2851]

Hi Bertie,

Thank you for the prompt response!

Option two is definitely more appropriate because I have a number of lists in use in this Excel spreadsheet. Examples include

List 1
ES - Spain
IE - Ireland
GB - Great Britain

List 2
1 - Transport by sea
2 - Transport by rail

As such, when the drop down box appears I see the full list of choices but once selected I only want the cell to show the text before the hyphen. For example ES, GB, IE, 1, 2 etc. The reason for this is that the lines of data will eventually be imported in an application which limits the number of characters for certain fields.

I tried to use your code for Sub test02 () in conjunction with the code already in my spreadsheet which makes a combo box appear in any cell with data validation but unfortunately I was unable to get it to work. As you may have gathered, I'm quite new to all this so I would be grateful if you could provide some guidance?

Just to be clear, I copied and pasted your code underneath the code already in my spreadsheet and then tried to use it. Of course I ensured that the tmp = " " included a value that appears in my spreadsheet.

Thanks again!

 

[bertie]

Hi Conor,

You could try using a Change event on your combo box to trim what it displays.

Private Sub TempCombo_Change()
   On Error Resume Next
      
   Application.EnableEvents = False
      With Me.TempCombo
         .Value = Trim(Left(.Value, InStr(.Value, "-") - 1))
      End With
   Application.EnableEvents = True
End Sub

This is untested.
I have assumed an ActiveX ComboBox.
We have to disable events when writing to ComboBox, On Error Resume Next is to ensure Application (Excel) settings are reset before exit.

 

[Walshy2851]

Hi Bertie,

Thanks again for the reply and advice but unfortunately it still doesn't work with my spreadsheet.

I tried this piece of code with an active x combo box in a different workbook and it worked perfectly! However, I cannot seem to get it to work with the piece of code that makes a combo box appear in any cell with data validation. Perhaps I will have to just use normal data validation for my project?