Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a named range as a source for a combobox on a userform
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a named range as a source for a combobox on a userform
UserForm1.ComboBox1.RowSource = Sheets("Sheet1").Range("MyNames").Address
"Keith R" skrev: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a named range as a source for a combobox on a userform
or just :
UserForm1.ComboBox1.RowSource = Range("MyNames").Address "excelent" skrev: UserForm1.ComboBox1.RowSource = Sheets("Sheet1").Range("MyNames").Address "Keith R" skrev: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges from hidden sheets (was Using a named range as a source for a combobox on a userform)
excelent- thank you for your responses. I'm partway there, but now I'm more
confused than before. Both versions you suggested work, but only when the sheet that contains the data referred to in the named range (e.g. Lists) is visible. When I hide that sheet (so other users don't get confused or mess it up) the named range that gets pulled into the combobox using =Range("MyNames").Address suddenly becomes the same range on whatever sheet is active, and not the Lists worksheet. Is there a way to ensure that the named range always refers to the Lists sheet, even when it is hidden? Currently my named range is: MyNames = OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A$2:$A$1000),1 ) which includes the source sheet name, so I'm not sure why I'm getting ranges from the active sheet instead. I'm using XL2003 on WinXP Thank you, Keith When I tried the long version to include the "excelent" wrote in message ... or just : UserForm1.ComboBox1.RowSource = Range("MyNames").Address "excelent" skrev: UserForm1.ComboBox1.RowSource = Sheets("Sheet1").Range("MyNames").Address "Keith R" skrev: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges from hidden sheets (was Using a named range as a
Well y that was strange, didnt no this would happens but think this vil fix it
Sub Strange() UserForm1.ComboBox1.RowSource = Sheets("Lists").Name & "!" & Range("MyNames").Address UserForm1.Show End Sub "Keith R" skrev: excelent- thank you for your responses. I'm partway there, but now I'm more confused than before. Both versions you suggested work, but only when the sheet that contains the data referred to in the named range (e.g. Lists) is visible. When I hide that sheet (so other users don't get confused or mess it up) the named range that gets pulled into the combobox using =Range("MyNames").Address suddenly becomes the same range on whatever sheet is active, and not the Lists worksheet. Is there a way to ensure that the named range always refers to the Lists sheet, even when it is hidden? Currently my named range is: MyNames = OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A$2:$A$1000),1 ) which includes the source sheet name, so I'm not sure why I'm getting ranges from the active sheet instead. I'm using XL2003 on WinXP Thank you, Keith When I tried the long version to include the "excelent" wrote in message ... or just : UserForm1.ComboBox1.RowSource = Range("MyNames").Address "excelent" skrev: UserForm1.ComboBox1.RowSource = Sheets("Sheet1").Range("MyNames").Address "Keith R" skrev: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a named range as a data source for a chart | Charts and Charting in Excel | |||
Using a named range as a data source for a chart | Excel Worksheet Functions | |||
Using a Named Range for Data Source In A Chart | Charts and Charting in Excel | |||
Use named range to fill Userform combobox XL2003? | Excel Programming | |||
Combobox and named range as source | Excel Programming |