ok, I have this code below that populates ComboBox3 with the text in MyRange.
What I would like to do is record the address of the cell somwhere for
reference once a value is selected in combobox3.

EX. If ComboBox3 is populated and I select the text that is on cell K1 I
need the address K1 recoreded somewhere so that I can populate a second combo
box with range k3:k18. Or if the text for L2 is selected I need a
second combo box with L3:L18 and so forth.

==========================================
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
For Each C In MyRange
If C = "" Then
GoTo 10
Else
ComboBox3.AddItem C.Value
End If
10: Next
==========================================

When I do this I try to leave everything sequential so I can use the
Index of the combobox to shift columns, like this:
if ComboBox3 has options of data from K through M and "K" is selected
then
Range(Cells(3,ComboBox3.ListIndex +
10),Cells(18,ComboBox3.ListIndex)).Select

Let me know if you need help implementing that or if you need a
different solution altogether.

Charles Chickering

Kevin wrote:
ok, I have this code below that populates ComboBox3 with the text in MyRange.
What I would like to do is record the address of the cell somwhere for
reference once a value is selected in combobox3.

EX. If ComboBox3 is populated and I select the text that is on cell K1 I
need the address K1 recoreded somewhere so that I can populate a second combo
box with range k3:k18. Or if the text for L2 is selected I need a
second combo box with L3:L18 and so forth.

==========================================
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
For Each C In MyRange
If C = "" Then
GoTo 10
Else
ComboBox3.AddItem C.Value
End If
10: Next
==========================================

I would just add a hidden column and store the address there. Then you can
you can retrieve the information when you need it.


Private Sub UserForm_Initialize()
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
With ComboBox3
.ColumnCount = 2
.ColumnWidths = ";-1"
For Each c In MyRange
If c < "" Then
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Address
End If
Next
End With
End Sub

' some demo code showing how to get at it

Private Sub ComboBox3_Click()
With ComboBox3
MsgBox .Value & " address: " & .List(.ListIndex, 1)
End With
End Sub

This is form a userform, but a similar approach would work on a combobox on
the worksheet (from the controls toolbox toolbar)

--
Regards,
Tom Ogilvy


"Kevin" wrote:

ok, I have this code below that populates ComboBox3 with the text in MyRange.
What I would like to do is record the address of the cell somwhere for
reference once a value is selected in combobox3.

EX. If ComboBox3 is populated and I select the text that is on cell K1 I
need the address K1 recoreded somewhere so that I can populate a second combo
box with range k3:k18. Or if the text for L2 is selected I need a
second combo box with L3:L18 and so forth.

==========================================
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
For Each C In MyRange
If C = "" Then
GoTo 10
Else
ComboBox3.AddItem C.Value
End If
10: Next
==========================================

sorry, I had a typo in my post. The columnWidths command should be:

..ColumnWidths = "-1;0"

to hide the address column.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

I would just add a hidden column and store the address there. Then you can
you can retrieve the information when you need it.


Private Sub UserForm_Initialize()
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
With ComboBox3
.ColumnCount = 2
.ColumnWidths = ";-1"
For Each c In MyRange
If c < "" Then
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Address
End If
Next
End With
End Sub

' some demo code showing how to get at it

Private Sub ComboBox3_Click()
With ComboBox3
MsgBox .Value & " address: " & .List(.ListIndex, 1)
End With
End Sub

This is form a userform, but a similar approach would work on a combobox on
the worksheet (from the controls toolbox toolbar)

--
Regards,
Tom Ogilvy


"Kevin" wrote:

ok, I have this code below that populates ComboBox3 with the text in MyRange.
What I would like to do is record the address of the cell somwhere for
reference once a value is selected in combobox3.

EX. If ComboBox3 is populated and I select the text that is on cell K1 I
need the address K1 recoreded somewhere so that I can populate a second combo
box with range k3:k18. Or if the text for L2 is selected I need a
second combo box with L3:L18 and so forth.

==========================================
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
For Each C In MyRange
If C = "" Then
GoTo 10
Else
ComboBox3.AddItem C.Value
End If
10: Next
==========================================

Those are interesting results, but I cant quite get what I need out of it.
I'm sure its me. Lets say A1 says test1 and A2:A6 is 1-5. Then B1 says
test2 and B2:B6 is a-e.
I want combobox1 to be filled with a1:b1 text, then I want to choose one of
the 2 and when I click it it populates combobox2 with the 1-5 or a-e
depending on which text is selected.

test1 test2
1 a
2 b
3 c
4 d
5 e

The reason I dont want to just refer to the cells is I want to be able to
add more columns and more rows without having to update a lot of code.

Thanks for the help guys.


"Tom Ogilvy" wrote:

I would just add a hidden column and store the address there. Then you can
you can retrieve the information when you need it.


Private Sub UserForm_Initialize()
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
With ComboBox3
.ColumnCount = 2
.ColumnWidths = ";-1"
For Each c In MyRange
If c < "" Then
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Address
End If
Next
End With
End Sub

' some demo code showing how to get at it

Private Sub ComboBox3_Click()
With ComboBox3
MsgBox .Value & " address: " & .List(.ListIndex, 1)
End With
End Sub

This is form a userform, but a similar approach would work on a combobox on
the worksheet (from the controls toolbox toolbar)

--
Regards,
Tom Ogilvy


"Kevin" wrote:

ok, I have this code below that populates ComboBox3 with the text in MyRange.
What I would like to do is record the address of the cell somwhere for
reference once a value is selected in combobox3.

EX. If ComboBox3 is populated and I select the text that is on cell K1 I
need the address K1 recoreded somewhere so that I can populate a second combo
box with range k3:k18. Or if the text for L2 is selected I need a
second combo box with L3:L18 and so forth.

==========================================
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
For Each C In MyRange
If C = "" Then
GoTo 10
Else
ComboBox3.AddItem C.Value
End If
10: Next
==========================================

Paste these subs into your Userform Code:
Private Sub ComboBox1_Change()
Dim cnt As Long
ComboBox2.Clear
For cnt = 1 To 5 'Change 5 to the number of cells you want to
list
'To offset columns, Change the + 1 after ComboBox1.ListIndex to the
First used Column
ComboBox2.AddItem Cells(cnt + 1, ComboBox1.ListIndex + 1), cnt
- 1
Next
ComboBox2.ListIndex = 0
End Sub

'This procedure autoloads comboBox1 with the number of used columns in
Row 1
Private Sub UserForm_Activate()
Dim cnt As Long
ComboBox1.Clear
ComboBox2.Clear
For cnt = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
ComboBox1.AddItem Cells(1, cnt), cnt - 1
Next
ComboBox1.ListIndex = 0
End Sub

Let me know if you need help.

Charles Chickering

Kevin wrote:
Those are interesting results, but I cant quite get what I need out of it.
I'm sure its me. Lets say A1 says test1 and A2:A6 is 1-5. Then B1 says
test2 and B2:B6 is a-e.
I want combobox1 to be filled with a1:b1 text, then I want to choose one of
the 2 and when I click it it populates combobox2 with the 1-5 or a-e
depending on which text is selected.

test1 test2
1 a
2 b
3 c
4 d
5 e

The reason I dont want to just refer to the cells is I want to be able to
add more columns and more rows without having to update a lot of code.

Thanks for the help guys.


"Tom Ogilvy" wrote:

I would just add a hidden column and store the address there. Then you can
you can retrieve the information when you need it.


Private Sub UserForm_Initialize()
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
With ComboBox3
.ColumnCount = 2
.ColumnWidths = ";-1"
For Each c In MyRange
If c < "" Then
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Address
End If
Next
End With
End Sub

' some demo code showing how to get at it

Private Sub ComboBox3_Click()
With ComboBox3
MsgBox .Value & " address: " & .List(.ListIndex, 1)
End With
End Sub

This is form a userform, but a similar approach would work on a combobox on
the worksheet (from the controls toolbox toolbar)

--
Regards,
Tom Ogilvy


"Kevin" wrote:

ok, I have this code below that populates ComboBox3 with the text in MyRange.
What I would like to do is record the address of the cell somwhere for
reference once a value is selected in combobox3.

EX. If ComboBox3 is populated and I select the text that is on cell K1 I
need the address K1 recoreded somewhere so that I can populate a second combo
box with range k3:k18. Or if the text for L2 is selected I need a
second combo box with L3:L18 and so forth.

==========================================
Dim MyRange As Range
Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21")
For Each C In MyRange
If C = "" Then
GoTo 10
Else
ComboBox3.AddItem C.Value
End If
10: Next
==========================================