Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Load A ComboBox On A UserForm
Greetings,
I have a ComboBox (CBX1) on a UserForm (UF1) that needs to be loaded from a named range (CustNames) on my spreadsheet. I have CBX1, but I am not sure of the best way to load it. One consideration is that sometimes there will not be a match in the "CustNames" list for the name I need to enter, so I will need to enter the name manually, directly into the ComboBox and if possible, have VBA add that name into the "CustNames" list. Is this possible? Any help would be appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Load A ComboBox On A UserForm
Hi Minitman,
You can use the RowSource property of the ComboBox to specify the source range for the dropdown. If your named range is Jake, you would use =Jake if setting it at design time (via the properties window). As long as the MatchRequired property is False, the user will be able to enter a value that doesn't match the current list. Here's some quick and dirty code that will add an item to the named range and the ComboBox: Private Sub CBX1_AfterUpdate() Dim l As Long Dim bFound Dim rng As Range With CBX1 For l = 0 To .ListCount - 1 If StrComp(.Value, .List(l), _ vbTextCompare) = 0 Then bFound = True Exit For End If Next l End With If Not bFound Then '/ add value to named range With Range("Jake") Set rng = .Resize(.Rows.Count + 1, _ .Columns.Count) rng.Cells(.Rows.Count + 1, _ .Columns.Count).Value = CBX1.Value End With Names("Jake").RefersTo = "=" & rng.Address CBX1.RowSource = "=Jake" End If End Sub Seems to work, but you'll want to test it thoroughly and add some error handling. This code should only add an item if it doesn't match an existing list item. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Minitman wrote: Greetings, I have a ComboBox (CBX1) on a UserForm (UF1) that needs to be loaded from a named range (CustNames) on my spreadsheet. I have CBX1, but I am not sure of the best way to load it. One consideration is that sometimes there will not be a match in the "CustNames" list for the name I need to enter, so I will need to enter the name manually, directly into the ComboBox and if possible, have VBA add that name into the "CustNames" list. Is this possible? Any help would be appreciated. TIA -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Load A ComboBox On A UserForm
Hey Jake,
This works the first time but then it changes the RowSourse when it is finished. I am using the named range called "CustName" which is on the sheet called "Customer_List". I start the UserForm from a CommandButton on the sheet called "Data_Collection". Like I said, the code does the proper posting of a new name to the bottom of the named range "CustName" (I have added a subroutine to resort "CustName"). When I check the address of CustName after I run it, It shows that "CustName" is no longer at "Customer_List!$A$1:$A$29" but has moved to "Data_Collection!$A$1:$A$30" I have not been able to figure out how to stop the change of sheets. Here is the modified code that I am using: Private Sub CBX1_AfterUpdate() Dim l As Long Dim bFound Dim rng As Range With CBX1 For l = 0 To .ListCount - 1 If StrComp(.Value, .List(l), _ vbTextCompare) = 0 Then bFound = True Exit For End If Next l End With If Not bFound Then '/ add value to named range With Range("CustName") Set rng = .Resize(.Rows.Count + 1, _ .Columns.Count) rng.Cells(.Rows.Count + 1, _ .Columns.Count).Value = CBX1.Value End With Names("CustName").RefersTo = "=" & rng.Address CBX1.RowSource = "=CustName" ' "This resorts the Customer List - Commented out for testing - has ' no effect on the named range" ' Sheets("Customer_List").Select ' Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ ' Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ ' Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End If End Sub Any help would be most appreciated. TIA -Minitman On Mon, 25 Oct 2004 15:49:46 -0700, "Jake Marx" wrote: Hi Minitman, You can use the RowSource property of the ComboBox to specify the source range for the dropdown. If your named range is Jake, you would use =Jake if setting it at design time (via the properties window). As long as the MatchRequired property is False, the user will be able to enter a value that doesn't match the current list. Here's some quick and dirty code that will add an item to the named range and the ComboBox: Private Sub CBX1_AfterUpdate() Dim l As Long Dim bFound Dim rng As Range With CBX1 For l = 0 To .ListCount - 1 If StrComp(.Value, .List(l), _ vbTextCompare) = 0 Then bFound = True Exit For End If Next l End With If Not bFound Then '/ add value to named range With Range("Jake") Set rng = .Resize(.Rows.Count + 1, _ .Columns.Count) rng.Cells(.Rows.Count + 1, _ .Columns.Count).Value = CBX1.Value End With Names("Jake").RefersTo = "=" & rng.Address CBX1.RowSource = "=Jake" End If End Sub Seems to work, but you'll want to test it thoroughly and add some error handling. This code should only add an item if it doesn't match an existing list item. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Minitman wrote: Greetings, I have a ComboBox (CBX1) on a UserForm (UF1) that needs to be loaded from a named range (CustNames) on my spreadsheet. I have CBX1, but I am not sure of the best way to load it. One consideration is that sometimes there will not be a match in the "CustNames" list for the name I need to enter, so I will need to enter the name manually, directly into the ComboBox and if possible, have VBA add that name into the "CustNames" list. Is this possible? Any help would be appreciated. TIA -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Load A ComboBox On A UserForm
I should have tested it a bit more before posting. If you change the
RefersTo assignment with the following, it should work: Names("CustName").RefersTo = "='" & rng.Parent.Name & _ "'!" & rng.Address -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Minitman wrote: Hey Jake, This works the first time but then it changes the RowSourse when it is finished. I am using the named range called "CustName" which is on the sheet called "Customer_List". I start the UserForm from a CommandButton on the sheet called "Data_Collection". Like I said, the code does the proper posting of a new name to the bottom of the named range "CustName" (I have added a subroutine to resort "CustName"). When I check the address of CustName after I run it, It shows that "CustName" is no longer at "Customer_List!$A$1:$A$29" but has moved to "Data_Collection!$A$1:$A$30" I have not been able to figure out how to stop the change of sheets. Here is the modified code that I am using: Private Sub CBX1_AfterUpdate() Dim l As Long Dim bFound Dim rng As Range With CBX1 For l = 0 To .ListCount - 1 If StrComp(.Value, .List(l), _ vbTextCompare) = 0 Then bFound = True Exit For End If Next l End With If Not bFound Then '/ add value to named range With Range("CustName") Set rng = .Resize(.Rows.Count + 1, _ .Columns.Count) rng.Cells(.Rows.Count + 1, _ .Columns.Count).Value = CBX1.Value End With Names("CustName").RefersTo = "=" & rng.Address CBX1.RowSource = "=CustName" ' "This resorts the Customer List - Commented out for testing - has ' no effect on the named range" ' Sheets("Customer_List").Select ' Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ ' Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ ' Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End If End Sub Any help would be most appreciated. TIA -Minitman On Mon, 25 Oct 2004 15:49:46 -0700, "Jake Marx" wrote: Hi Minitman, You can use the RowSource property of the ComboBox to specify the source range for the dropdown. If your named range is Jake, you would use =Jake if setting it at design time (via the properties window). As long as the MatchRequired property is False, the user will be able to enter a value that doesn't match the current list. Here's some quick and dirty code that will add an item to the named range and the ComboBox: Private Sub CBX1_AfterUpdate() Dim l As Long Dim bFound Dim rng As Range With CBX1 For l = 0 To .ListCount - 1 If StrComp(.Value, .List(l), _ vbTextCompare) = 0 Then bFound = True Exit For End If Next l End With If Not bFound Then '/ add value to named range With Range("Jake") Set rng = .Resize(.Rows.Count + 1, _ .Columns.Count) rng.Cells(.Rows.Count + 1, _ .Columns.Count).Value = CBX1.Value End With Names("Jake").RefersTo = "=" & rng.Address CBX1.RowSource = "=Jake" End If End Sub Seems to work, but you'll want to test it thoroughly and add some error handling. This code should only add an item if it doesn't match an existing list item. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Minitman wrote: Greetings, I have a ComboBox (CBX1) on a UserForm (UF1) that needs to be loaded from a named range (CustNames) on my spreadsheet. I have CBX1, but I am not sure of the best way to load it. One consideration is that sometimes there will not be a match in the "CustNames" list for the name I need to enter, so I will need to enter the name manually, directly into the ComboBox and if possible, have VBA add that name into the "CustNames" list. Is this possible? Any help would be appreciated. TIA -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Load A ComboBox On A UserForm
Hey Jake,
That fixed it, thanks. -Minitman On Tue, 26 Oct 2004 11:19:37 -0700, "Jake Marx" wrote: I should have tested it a bit more before posting. If you change the RefersTo assignment with the following, it should work: Names("CustName").RefersTo = "='" & rng.Parent.Name & _ "'!" & rng.Address -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Minitman wrote: Hey Jake, This works the first time but then it changes the RowSourse when it is finished. I am using the named range called "CustName" which is on the sheet called "Customer_List". I start the UserForm from a CommandButton on the sheet called "Data_Collection". Like I said, the code does the proper posting of a new name to the bottom of the named range "CustName" (I have added a subroutine to resort "CustName"). When I check the address of CustName after I run it, It shows that "CustName" is no longer at "Customer_List!$A$1:$A$29" but has moved to "Data_Collection!$A$1:$A$30" I have not been able to figure out how to stop the change of sheets. Here is the modified code that I am using: Private Sub CBX1_AfterUpdate() Dim l As Long Dim bFound Dim rng As Range With CBX1 For l = 0 To .ListCount - 1 If StrComp(.Value, .List(l), _ vbTextCompare) = 0 Then bFound = True Exit For End If Next l End With If Not bFound Then '/ add value to named range With Range("CustName") Set rng = .Resize(.Rows.Count + 1, _ .Columns.Count) rng.Cells(.Rows.Count + 1, _ .Columns.Count).Value = CBX1.Value End With Names("CustName").RefersTo = "=" & rng.Address CBX1.RowSource = "=CustName" ' "This resorts the Customer List - Commented out for testing - has ' no effect on the named range" ' Sheets("Customer_List").Select ' Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ ' Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ ' Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End If End Sub Any help would be most appreciated. TIA -Minitman On Mon, 25 Oct 2004 15:49:46 -0700, "Jake Marx" wrote: Hi Minitman, You can use the RowSource property of the ComboBox to specify the source range for the dropdown. If your named range is Jake, you would use =Jake if setting it at design time (via the properties window). As long as the MatchRequired property is False, the user will be able to enter a value that doesn't match the current list. Here's some quick and dirty code that will add an item to the named range and the ComboBox: Private Sub CBX1_AfterUpdate() Dim l As Long Dim bFound Dim rng As Range With CBX1 For l = 0 To .ListCount - 1 If StrComp(.Value, .List(l), _ vbTextCompare) = 0 Then bFound = True Exit For End If Next l End With If Not bFound Then '/ add value to named range With Range("Jake") Set rng = .Resize(.Rows.Count + 1, _ .Columns.Count) rng.Cells(.Rows.Count + 1, _ .Columns.Count).Value = CBX1.Value End With Names("Jake").RefersTo = "=" & rng.Address CBX1.RowSource = "=Jake" End If End Sub Seems to work, but you'll want to test it thoroughly and add some error handling. This code should only add an item if it doesn't match an existing list item. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Minitman wrote: Greetings, I have a ComboBox (CBX1) on a UserForm (UF1) that needs to be loaded from a named range (CustNames) on my spreadsheet. I have CBX1, but I am not sure of the best way to load it. One consideration is that sometimes there will not be a match in the "CustNames" list for the name I need to enter, so I will need to enter the name manually, directly into the ComboBox and if possible, have VBA add that name into the "CustNames" list. Is this possible? Any help would be appreciated. TIA -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Do I Load a ComboBox From a Dynamic Range | Excel Discussion (Misc queries) | |||
UserForm label doesn't load? | Excel Programming | |||
UserForm Wont Load | Excel Programming | |||
new error when try to load userform | Excel Programming | |||
Load a Userform | Excel Programming |