K
Keith R
I already have an auto-expanding named range (per Steve Bullen) to grab user
input from a worksheet. When the user clicks a toolbar button, it brings up
my userform and I'd like to use the named range to populate a set of
comboboxes. I thought I could replace the rowsource property with the named
range, but so far I've been unsuccessful- probably a simple syntax problem
(it has been a while since I've done Excel userform programming). I'm using
the loop below because I actually need to load up 6 comboboxes with the same
source.
Any assistance would be greatly appreciated- using XL2003 on WinXP.
Thank you!
Keith
MyNames = OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A$2:$A$1000),1)
Relvant code:
Sub UF1Load()
Dim Ctl As Control
For Each Ctl In UF1.Controls
TempName = Left(Ctl.Name, 3)
If TempName = "txt" Then
'<snip>
ElseIf TempName = cmb Then
'Ctl.List = MyNames
Ctl.RowSource = MyNames (or Sheet1.MyNames)
End If
Next
UF1.Show
End Sub
input from a worksheet. When the user clicks a toolbar button, it brings up
my userform and I'd like to use the named range to populate a set of
comboboxes. I thought I could replace the rowsource property with the named
range, but so far I've been unsuccessful- probably a simple syntax problem
(it has been a while since I've done Excel userform programming). I'm using
the loop below because I actually need to load up 6 comboboxes with the same
source.
Any assistance would be greatly appreciated- using XL2003 on WinXP.
Thank you!
Keith
MyNames = OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A$2:$A$1000),1)
Relvant code:
Sub UF1Load()
Dim Ctl As Control
For Each Ctl In UF1.Controls
TempName = Left(Ctl.Name, 3)
If TempName = "txt" Then
'<snip>
ElseIf TempName = cmb Then
'Ctl.List = MyNames
Ctl.RowSource = MyNames (or Sheet1.MyNames)
End If
Next
UF1.Show
End Sub