Excel data validation list two columns

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

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.

Try adding another line to the "exclusion" list:

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

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

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

'====================================

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

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

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

List 21 - Transport by sea

Thanks again!

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

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

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?

Hi Bertie,

Just wanted to say that I got the above to work with my spreadsheet. Thank you very much for all your help, it was a great help!

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.

Thanks for your help.

Hi Bertie,

I'm also using the Worksheet_Change event but would also like to repeat this in other colums on the same worksheet but with their other validation list values. I'm not sure how to add the code. Please help. Thanks.

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

Thank you for your time and attention to my questions.

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

How can this be applied to multiple cells within the same worksheet using a different list name for each of the cells?

Thank you Bertie. I appreciate you taking the time to post this info. That code is very helpful and EXACTLY what I needed.

Hi Bertie. I am using your code above to strip out the "-" hyphen on a text and all is fine with the code you provided except I need to do the same function twice in the same worksheet. In column C the code works great and reports the answer correctly looking at a list on a separate worksheet. I don't know how to modify the code to allow a similar function to remove the hyphen on Column D but of a different list. Here is the code that works for me below. Any thoughts? Thanks. Private Sub Worksheet_Change(ByVal Target As Range) Dim strInput As String 'assumed column C - YOU HAVE TO SET THE COLUMN NUMBER TO WORK, HERE IT IS COLUMN 3 If Target.Column <> 3 Then Exit Sub If Target.Value = "" 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

I should clarify the question above, when I make a new entry into the worksheet macro (basically copy the code as you see but changing the target column to 4 to reflect Column D) I get errors. For some reason excel does not like to have the code above for column C with an identical macro and code but modified for column D. I basically need the same code to perform the same function on columns C and D. Whereby for column C it is looking at a pulldown list on say "Worksheet A" and the code for column D is looking at a pull down list for "Worksheet" B. Hopefully that is clear above, the issue is the codes don't want to coexist on the same worksheet macro page. Thanks for your help.