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

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:

Code:

---

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

Unfortunately for you, I am never clear if it is Forms Controls or Control Toolbox controls that are ActiveX controls

If you right click on the combobox, do you see a Properties selection? If not, use the other type of Controls. With the other other set of controls, I believe that you can use a ListBox and/or prevent the user from typing anything in a ComboBox.

I never use the ones that don't have a Properties selection in the menu.

@SamT:

Hi Sam,

Assuming you are being literal and forget this particular thing, Forms Controls are the 'antiques', for a Dialog Sheet. For me leastwise, I just remember that plunking an ActiveX control gets something like '=EMBED("Forms.CommandButton.1","")' in the Formula Bar (which is the ProgID if adding a control to a userform programmatically), and of course, the default procedure is assigned to the sheet's module rather than a Standard Module.

Not sure if of any help, but being all-to-blond and more memory leaks than Excel ever thought of, there's a couple of ways I remember :-)

Mark

@GTO

Mark,

Ayep, meant what I said. I pretty much quit using MSO in my real life a while back. And never did have much use for "Worksheet" controls, anyway. On the very rare times I needed one, I would insert, right click, and delete if it was the wrong one.

In fact, just a few minutes ago, while making coffee, I was thinking that when the time comes that I feel like it's mandatory that I buy a newer version of MSO in order to keep helping our guests, I will probably quit coming here.

Quote:

---

Originally Posted by SamT

Unfortunately for you, I am never clear if it is Forms Controls or Control Toolbox controls that are ActiveX controls

If you right click on the combobox, do you see a Properties selection? If not, use the other type of Controls. With the other other set of controls, I believe that you can use a ListBox and/or prevent the user from typing anything in a ComboBox.

I never use the ones that don't have a Properties selection in the menu.

---

Hi Sam,

Apologies for the delay and thanks for your reply.

I can access a properties box with tabs for Alphabetic and Categorized.

Use VBA Menu >> Tools >> Options >> General Tab >> Error Trapping Frame >> Break on all Errors = checked.

Set the control's MatchEntry property to True

Edit your sub like this.

Code:

---

Private Sub TempCombo_Change()
    'On Error Resume Next
    'Application.EnableEvents = False

    With Me.TempCombo
        .Value = Trim(Left(.Value, InStr(.Value, "-") - 1))
    End With

  'Error = 0 'Don't leave errors floating around in memory
  'Application.EnableEvents = True
End Sub

---

Let us know what that did.

Cross Post

Message to cross posters

Thanks, Kris.

@ Walshy,

I think I am going to save this code.

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'code used to make an active x combo box appear in any cell with data validation:
'Source of code: http://www.ozgrid.com/forum/showthread.php?t=191506&p=729731#post729731

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

---

Code:

---


'Optional code to move to next cell if Tab or Enter are pressed
'***NOTE: if KeyDown causes problems, change to KeyUp
'Source of code:  http://www.ozgrid.com/forum/showthread.php?t=191506&p=729731#post729731
'from code by Ted Lanham

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

---

Quote:

---

Originally Posted by SamT

Use VBA Menu >> Tools >> Options >> General Tab >> Error Trapping Frame >> Break on all Errors = checked.

Set the control's MatchEntry property to True

Edit your sub like this.

Code:

---

Private Sub TempCombo_Change()
    'On Error Resume Next
    'Application.EnableEvents = False

    With Me.TempCombo
        .Value = Trim(Left(.Value, InStr(.Value, "-") - 1))
    End With

  'Error = 0 'Don't leave errors floating around in memory
  'Application.EnableEvents = True
End Sub

---

Let us know what that did.

---

No luck with the above unfortunately, I get an error message when I try to click into a cell with data validation.

A cross post yes, only because it seems to be an issue that nobody has a solution to. :(

Although I have your 'solution' I won't publish it as long as you do not provide a representateive sample worksheet.
There can be all kinds of restrictions in your worksheet that prevent any 'solution' to work due to the lack of information you provide, let alone the non-informative statement 'I get an error'.(please remove that sentence from your vocabulary).

If the content of a validated cell is restricted to the values in a combobox, the cell can't contain a part of a value in the combobox ever.

So: what is the structure of the validation( range, named range, string)
How is the combobox being populated : .List, rowsource, listfillrange, named range, Macro, etc
Does the combox have a .linkedcell ?

To: snb, From: SamT

Please see my next post as to why I removed your attachment. The VBAExpress management would appreciate it if you would wait until Walshy2851 posts again before you upload it again in this thread.

Thank you for your forbearance.

Sam.

Walshy:

• First post here: 10-20-2014, 02:40 PM (4 days ago)
• First response here: 10-20-2014, 07:04 PM (4 days ago)
  
• First post @ Ozgrid: 21st October 2014 (3 days ago)
  
• Solution at Ozgrid: 2 days ago = "Thank you very much for this, it works perfectly"
  
• Second post here: 10-22-2014, 04:57 PM (2 days ago) = "Apologies for the delay ...".
  
• New problem at Ozgrid: 1 day ago = "While your change does extract the text before the hyphen, it causes a problem with the active x combo box"

All times are my locale.

And now, you said

Quote:

---

A cross post yes, only because it seems to be an issue that nobody has a solution to

---

It is very obvious that that is not the reason you cross posted in violation of both our and Ozgrid's rules.

Your conversation here has been full of deliberate significant omissions and misstatement of facts.

For these reasons, I am giving you on a three day timeout.

Apologies again for breaching the terms of both websites. Just to clarify though, when I said nobody seems to have a solution to my issue - I had asked people I know and on another Excel forum. It was at that point I created my account on this site and on Ozgrid but I of course accept the temporary ban I received.

Snb,

Thank you for your message and apologies for the delay in replying, I have been unwell for the past few weeks.

As you've probably gathered, I am a beginner with VBA.

The active x combo box is populated by lists of information stored in Sheet 2 of my workbook. I can provide a sample spreadsheet and I would be grateful if you could take a look and assist. If there is much work involved, I am happy to financially compensate you for your time.

I have asked this question and attached this spreadsheet on Ozgrid, I am yet to find a solution so I hope you will allow me to post here.

Welcome back. :friends:

I'm glad you are feeling better.