having difficulty with multiple combo boxes and VBA code

[manc]

Good afternoon,

I am using the following code to populate a combobox from a data validation list.
Code provided by contextures:

Option Explicit' Developed by Contextures Inc.
' www.contextures.com
Private Sub ComboBox1_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Dim TgtMrg As Range
Dim c As Range
Dim TgtW As Double
Dim AddW As Long
Dim AddH As Long


Set ws = ActiveSheet
On Error Resume Next
'extra width to cover drop down arrow
AddW = 15
'extra height to cover cell
AddH = 5


If Target.Rows.Count > 1 Then GoTo exitHandler


Set Tgt = Target.Cells(1, 1)
Set TgtMrg = Tgt.MergeArea
On Error GoTo errHandler


  Set cboTemp = ws.OLEObjects("ComboBox1")
    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 Tgt.Validation.Type = 3 Then
    Application.EnableEvents = False
    If Not TgtMrg Is Nothing Then
      'get total width of merged cells
      TgtW = 0
      For Each c In TgtMrg.Cells
        TgtW = TgtW + c.Width
      Next c
    End If
    
    str = Tgt.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Tgt.Left
      .Top = Tgt.Top
      If TgtW <> 0 Then
        'use total width for merged cells
        .Width = TgtW + AddW
      Else
        .Width = Tgt.Width + AddW
      End If
      .Height = Tgt.Height + AddH
      .ListFillRange = str
      .LinkedCell = Tgt.Address
    End With
    cboTemp.Activate
    Me.ComboBox1.DropDown
  End If


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


End Sub

The above works fine with one combobox, but my worksheet contains three.
I thought i could just duplicate the code, changing any reference of 'combobox1' to 'combobox2' and 'combobox3'.

I have since found out that I can only use 'Worksheet_SelectionChange' once, so I tried changing the name on each of the procedures but it didn't work, so it seems the only answer i can think of is to combine the code. I have no idea how to do this.

For each combobox, there is a data validation drop-down list pointing to a unique named range. I want the code to react with combobox2 and 3 the same as it does with 1 - the only thing that is the different between the three comboboxes is the named range.

If someone could show me where and how to combine the code, i would be very grateful.

Best regards
manc

 

[manc]

So, the weirdest thing is going on....

I deleted the duplicate code that i thought would make combobox2 work.
So the only code on the worksheet, is exactly as per the example code above, referencing combobox1... right?

Why then, does the cell containing the data validation list A (which in turn activates the above code for combobox1) work ok, AND WHY does the cell containing the data validation list B also activate the above code for combobox 1, BUT display the correct data that it should.

In other words, the above code is working for two comboboxes and displaying the correct data from two separate sources?!?!?

That's it. I'm done.... This isn't VBA, this is sorcery of the darkest kind.

What is worse is that data validation list C is having none of it.

If someone can explain to me what is going on, i'd really appreciate it.
Furthermore, if someone can explain how to persuade data validation list C to join the darkside, even better.

Look forward to hearing from any 'wizards'.

Best regards
manc

 

[manc]

SOLVED!!!

Whilst the contextures code is great, especially when working with merged cells, it only works for horizontally merged cells, not vertical.

Once I got my head round the fact that the VBA code applies to all data validation lists on the sheet, AND NOT specific to the combobox, it all made sense.

Turns out that data validation list C was merged vertically.

Best regards manc