MuliColumn Combo

[Little Penny]

I'm trying to use the same form for multi column combo box for 10
columns. I have started with two Column L and Column N. If I reference
this line of code

If ActiveCell.Address = Range("L25:L39").Address Then as
If ActiveCell.Address = Range("L25").Address

And

If ActiveCell.Address = Range("N25:N39").Address Then as
If ActiveCell.Address = Range("N25").Address

Then the code works but only for that cell. I want the for to load
with the list if any cell from L25:L39 is selected. There are two
works sheet selection events to call the user form

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("L25:L39")) Is Nothing Then
Call aaa
End If


If Not Intersect(Target, Range("N25:N39")) Is Nothing Then
Call aaa
End If
End sub


Here id my code


Private Sub UserForm_activate()
Dim MyList(10, 10) 'as array type

If ActiveCell.Address = Range("L25:L39").Address Then
With ComboBox1
.ColumnCount = 2
.ColumnWidths = "60,80"
.Width = 240
.Height = 15
.ListRows = 6
End With


With ActiveSheet


MyList(0, 0) = .Range("A201")
MyList(1, 0) = .Range("A202")
MyList(2, 0) = .Range("A203")
MyList(3, 0) = .Range("A204")
MyList(4, 0) = .Range("A205")
MyList(5, 0) = .Range("A206")
MyList(6, 0) = .Range("A207")
MyList(7, 0) = .Range("A208")
MyList(8, 0) = .Range("A209")
MyList(9, 0) = .Range("A210")

'//MyList (Row{0 to 9}, Column{second}) = (Column D in this
example)
MyList(0, 1) = .Range("B201")
MyList(1, 1) = .Range("B202")
MyList(2, 1) = .Range("B203")
MyList(3, 1) = .Range("B204")
MyList(4, 1) = .Range("B205")
MyList(5, 1) = .Range("B206")
MyList(6, 1) = .Range("B207")
MyList(7, 1) = .Range("B208")
MyList(8, 1) = .Range("B209")
MyList(9, 1) = .Range("B210")


End With

'//now populate the combo box
ComboBox1.List() = MyList
End If


'*****************************************************************
If ActiveCell.Address = Range("N25:N39").Address Then
With ComboBox1
.ColumnCount = 2
.ColumnWidths = "60,80"
.Width = 240
.Height = 15
.ListRows = 6
End With


With ActiveSheet


MyList(0, 0) = .Range("J201")
MyList(1, 0) = .Range("J202")
MyList(2, 0) = .Range("J203")
MyList(3, 0) = .Range("J204")
MyList(4, 0) = .Range("J205")
MyList(5, 0) = .Range("J206")
MyList(6, 0) = .Range("J207")
MyList(7, 0) = .Range("J208")
MyList(8, 0) = .Range("J209")
MyList(9, 0) = .Range("J210")

MyList(0, 1) = .Range("K201")
MyList(1, 1) = .Range("K202")
MyList(2, 1) = .Range("K203")
MyList(3, 1) = .Range("K204")
MyList(4, 1) = .Range("K205")
MyList(5, 1) = .Range("K206")
MyList(6, 1) = .Range("K207")
MyList(7, 1) = .Range("K208")
MyList(8, 1) = .Range("K209")
MyList(9, 1) = .Range("K210")


End With

ComboBox1.List() = MyList
End If


'*****************************************************************

'//The combo box in this example contains 3 data columns

End Sub



Thanks



Little Penny

 

[JLGWhiz]

Something like:

If Not Intersect(Range("L24: L39"), Selection) Is Nothing Then

That should make any selection in L23:L39 equate to true. You can even
refine that with:

If Selection.Cells.Count = 1 Then
If Not Intersect(Range("L24: L39"), Selection) Is Nothing Then
'Do something
End If
End If

This force the user to select only one cell or the code does nothing.

 

[Dave Peterson]

I'm not sure what you're doing, but the Activecell is just a single cell. Its
address is never going to be the same as the address of a multicell range.

And I don't see what you're doing with an array of (10,10) elements. It really
looks like you wanted 10 rows by 2 columns.

This may give you an idea (or not???).

This goes in a General module. It's just a public variable that can be seen
from any module in that workbook's project:

Option Explicit
Public myRngToUse As Range


This goes behind the worksheet:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set myRngToUse = Nothing

If Not (Intersect(Target, Me.Range("L25:L39")) Is Nothing) Then
Set myRngToUse = Me.Range("A201:B210")
Else
If Not (Intersect(Target, Range("N25:N39")) Is Nothing) Then
Set myRngToUse = Me.Range("J201:K201")
End If
End If

If myRngToUse Is Nothing Then
'not in one of the ranges, don't show the userform
Else
UserForm1.Show
End If

End Sub

And this goes behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim MyList As Variant

If myRngToUse Is Nothing Then
MsgBox "Design error!"
Exit Sub
End If

MyList = myRngToUse.Value '2 columns in the range

With Me.ComboBox1
.ColumnCount = myRngToUse.Columns.Count
.ColumnWidths = "60,80"
.Width = 240
.Height = 15
.ListRows = 6
.List = MyList
End With
End Sub

 

[Little Penny]

Wow....

That did.

Thanks for such a quick reply....