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

 

[Walshy2851]

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!

 

[Walshy2851]

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.

Hi guys,

As I said in my previous post, this works perfectly.. However, once I change 'Stlye' from 0 - 'fmStlyeDropDownCombo' to '2 - fmStlyeDropDownList', it no longer works unfortunately.

Does anyone know of a workaround?

Thanks for your help.

 

[scarlett6]

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.

 

[swhitmore220]

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

I need to use this same code and need a little more details in my limited knowledge of Excel and VB. Where exactly do you enter this information? I'm assuming you copy and paste it where you want to do the validation or can it be in the data validation GUI? I am using Excel 2010.

Thank you for your time and attention to my questions.

 

[rsstorey]

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

bertie

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

 

[ExcelKnut]

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

 

[hlore73]

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

 

[hlore73]

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.