Return a cell address used by a ComboBox

Posted by Dominic Parsonson on January 24, 2001 3:25 AM

I have a Large Table that I want to tie to my UserForm
In ComboBox1 I have a Long RowSource, I now want to say .. the following

ComboBox1= (address of cell from Text List)
Therfore ComboBox2 = (address of cell from Text List)next cell

etc

How do I do this

Posted by Dominic Parsonson on January 24, 2001 6:02 AM

I solved it with this code

Private Sub ComboBox1_Change()
' Compares ComBox Text with text in sheet
DateFound = False
For I = 11 To 100
If UserForm1.ComboBox1.Text = (Sheets("Calculation").Range("A1").Offset(I, 0).Text) Then
DateFound = True
Set mc = ActiveSheet.Range("A1").Offset(I, 0)
Exit For
End If
Next I
' AddressFound returns Range of matching text
If DateFound Then AddressFound = mc.Address Else MsgBox "No valid data for this period!", vbExclamation
' Returns data from column in same row as matching text
UserForm1.Naph.Text = (Sheets("Calculation").Range(AddressFound).Offset(0, 1).Text)
UserForm1.E.Text = (Sheets("Calculation").Range(AddressFound).Offset(0, 2).Text)
UserForm1.A.Text = (Sheets("Calculation").Range(AddressFound).Offset(0, 3).Text)
End Sub