Macro/VBA to remove all text before hypens - Active X Combo Box

[Walshy2851]

Hi all,


I am using some code (that I got from the Contextures website) which makes an active x combo box (named TempCombo) appear in any cell which contains data validation.


I have numerous cells which contain data validation linked to numerous lists of information which are stored in Sheet 2 of my workbook. All lists contain different information but are common in their format i.e. there is some text or a number then a space followed by a hyphen, another space and then text. Some examples include: 1 - Dog, 2 - Cat, ES - Spain, IE - Ireland.


So when someone clicks into a cell with data validation, the active x combo box instantly appears and gives a drop down of the information. Once the user has chosen an option from the drop down list, I only want the part before the hyphen to appear in the cell. Following on from my examples above this would mean 1, 2, ES or IE depending on which of the four options are selected.


I initially had been using the following code to achieve this:

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 works perfectly until I change the Style of my combo box from 0 - fmStlyeDropDownCombo to 2 - fmStlyeDropDownList then it no longer has any effect. Unfortunately, I cannot let the user type in information which isn't in the list, I can only allow them to choose something from the lists otherwise it will cause issues.


So I am hoping that somebody can update the code above which will still work when 2 - fmStlyeDropDownList is selected.


Alternatively, I am happy to consider the option of using the style 2 - fmStlyeDropDownList and then afterwards, applying a Macro / VBA code which will change the style of my combo box to 0 - fmStlyeDropDownCombo and then apply code to the whole of my worksheet that will trim every single cell to the text/number before hyphens.


Can anybody help? This is the last piece of a rather big project I have been working on so I would be delighted to get it over the line.


Thanks for reading,


Conor