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
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
