Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Hi
Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Try it this way:
Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Note as well, if you are positive you have the named range DynamicDataRange,
then perhaps the formula is returning an error rather than a range. One way to test is to type DynamicDataRange in the name box and hit enter. Does it take you to your data? -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Try it this way: Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Tom
It's not picking up my dynamic named range: I have 'TeamArray' defined as: =OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65 534:$A996),COUNTA(TeamDetail!$2:$2)) Code below was modified appropriately by copying the name "TeamArray " from the Define Name and pasting over DynamicDataRange (ie no spelling mistake). Steve "Tom Ogilvy" wrote in message ... Try it this way: Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
The dynamic range sets marching ants around the correct range.
"Steve" <No Spam wrote in message ... Tom It's not picking up my dynamic named range: I have 'TeamArray' defined as: =OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65 534:$A996),COUNTA(TeamDetail!$2:$2)) Code below was modified appropriately by copying the name "TeamArray " from the Define Name and pasting over DynamicDataRange (ie no spelling mistake). Steve "Tom Ogilvy" wrote in message ... Try it this way: Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Hard to believe.
Your formula doesn't make much sense. If you base your formula on A2, then count the entries in A996:A65534 to get the row count, don't see how this could define the correct range. It appears you formula is using relative references and will vary based on the active Cell. If you clean up your formula, then it should work. -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... The dynamic range sets marching ants around the correct range. "Steve" <No Spam wrote in message ... Tom It's not picking up my dynamic named range: I have 'TeamArray' defined as: =OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65 534:$A996),COUNTA(TeamDeta il!$2:$2)) Code below was modified appropriately by copying the name "TeamArray " from the Define Name and pasting over DynamicDataRange (ie no spelling mistake). Steve "Tom Ogilvy" wrote in message ... Try it this way: Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
When you do what I described, you don't get marching ants. The range is
selected. -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... The dynamic range sets marching ants around the correct range. "Steve" <No Spam wrote in message ... Tom It's not picking up my dynamic named range: I have 'TeamArray' defined as: =OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65 534:$A996),COUNTA(TeamDeta il!$2:$2)) Code below was modified appropriately by copying the name "TeamArray " from the Define Name and pasting over DynamicDataRange (ie no spelling mistake). Steve "Tom Ogilvy" wrote in message ... Try it this way: Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Bob
I couldn't get a dynamic range working with the code below. However, Excel has many cat-skinning methods. Worksheet change event now has code to zap the existing name and create a new one. Result one Combo that works. Thank you for your guidance on the code. Steve "Tom Ogilvy" wrote in message ... When you do what I described, you don't get marching ants. The range is selected. -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... The dynamic range sets marching ants around the correct range. "Steve" <No Spam wrote in message ... Tom It's not picking up my dynamic named range: I have 'TeamArray' defined as: =OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65 534:$A996),COUNTA(TeamDeta il!$2:$2)) Code below was modified appropriately by copying the name "TeamArray " from the Define Name and pasting over DynamicDataRange (ie no spelling mistake). Steve "Tom Ogilvy" wrote in message ... Try it this way: Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Fred,
Glad you found a work around. -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Bob I couldn't get a dynamic range working with the code below. However, Excel has many cat-skinning methods. Worksheet change event now has code to zap the existing name and create a new one. Result one Combo that works. Thank you for your guidance on the code. Steve "Tom Ogilvy" wrote in message ... When you do what I described, you don't get marching ants. The range is selected. -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... The dynamic range sets marching ants around the correct range. "Steve" <No Spam wrote in message ... Tom It's not picking up my dynamic named range: I have 'TeamArray' defined as: =OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65 534:$A996),COUNTA(TeamDeta il!$2:$2)) Code below was modified appropriately by copying the name "TeamArray " from the Define Name and pasting over DynamicDataRange (ie no spelling mistake). Steve "Tom Ogilvy" wrote in message ... Try it this way: Private Sub UserForm_Initialize() Dim rArray As Variant Dim rng as Range On error Resume Next set rng = Thisworkbook.Names("DynamicDataRange").RefersToRan ge On error goto 0 if rng is nothing then msgbox "No range named DynamicDataRange" exit sub end if rArray = rng.Value With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Hi Excel 2K I need to fill a combo box from a dynamic range compiled by users. The first column (bound column, not visible) will contain letters for use in a "begins with" filter. The second column (visible, not bound) will contain the description to be seen by users on the form. I found an example how to fill a multi-column combo in a form at http://www.erlandsendata.no/english/ The example works. However, when I transfer the form to my spreadsheet and rename the dynamic range to my dynamic range, the code generates an error at rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of object Global failed. Pressing help at this point produce an informative blank page! My initial thougt was a need to size the array so, I tested with a fixed size rArray. Still received an error. Can comone give me some help in filling a two column combo from a dynamic named range? Steve The Erlandsen code: Private Sub UserForm_Initialize() Dim rArray As Variant rArray = Range("DynamicDataRange") With Me.ComboBox3 ' use the next line .List() = rArray ' or the next two lines '.RowSource = "Data!A4:C23" '.ColumnHeads = True .ListIndex = -1 ' no selected item End With End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill two column combo box
Tom
Don't you mean "Wally" ? "Tom Ogilvy" wrote in message ... Fred, Glad you found a work around. -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Bob .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill the values in combo box of excel sheet | Excel Worksheet Functions | |||
I need a specific value in auto filter to fill a combo box and than match the select value in 2 other columns. | Excel Worksheet Functions | |||
Fill in Combo Box with range values???? | Excel Programming | |||
Combo Box Fill Range | Excel Programming | |||
Fill a combo box from a named area on a sheet | Excel Programming |