Problem with adding an item

[RPIJG]

I'm having a problem adding an item to a list in a combobox that i
bound to a worksheet.



Code
-------------------

Private Sub AddName_Click()
Dim SourceData As Range
Dim Found As Object

Set SourceData = Range("customerinfo")
Set Found = Nothing
Set Found = SourceData.Find(NameBox.Value)

If Found Is Nothing Then
SourceData.Resize(SourceData.Rows.Count + 1, 1).Name = "CustomerInfo"
SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value = NameBox.Value
NameBox.RowSource = Range("customerinfo").Address(external:=True)
End If
End Sub

 

[Juan Pablo González]

Again, replied in the MrExcel board...

 

[Tom Ogilvy]

If you are using Excel 97, try changing the TakeFocusOnClick property of the
commandbutton to False.

If customerInfo is on another worksheet and this button is on a worksheet as
well (thus the event code shown is in the worksheet module of the sheet
where the button is located), then qualify Range("CustomerInfo") with the
worksheet name

set sourcedata = Worksheets("Data").Range("CustomerInfo")

As written now it is equivalent to
set sourcedata = me.Range("CustomerInfo")

since an unqualified range reference in a sheet module implicitely refers to
the sheet that owns the module.

 

[RPIJG]

your reply did not fix the problem, hence why I searched other outlet
for help. I'm getting the same error either way as I posted above.
Thanks

 

[Tom Ogilvy]

See the second part of my answer:

Does that fit your situation.

 

[RPIJG]

I guess maybe I'm confused here...

I have a box open when the workbook opens and from the NameBox I wan
that value entered into the worksheet named CustomerInfo, and then thi
will add itself to the combobox list. I'm getting a subscript out o
range error with this code...


Code
-------------------
Private Sub AddName_Click()
Dim SourceData As Range
Dim Found As Object

Set SourceData = Worksheets("CustomerInfo").Range("customerinfo")
Set Found = Nothing
Set Found = SourceData.Find(NameBox.Value)

If Found Is Nothing Then
SourceData.Resize(SourceData.Rows.Count + 1, 1).Name = "CustomerInfo"
SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value = NameBox.Value
NameBox.RowSource = Worksheets("CustomerInfo").Range("customerinfo").Address(external:=True)
End I

 

[RPIJG]

ok so I replaced the values I had for range as follows, but now i
doesn't update the list in the combobox correctly, but it doesn't giv
me an error, so how do I get it to redefine the list when I add anothe
item to it?


Code
-------------------
Private Sub AddName_Click()
Dim SourceData As Range
Dim Found As Object

Set SourceData = Worksheets("CustomerInfo").Range("A1")
Set Found = Nothing
Set Found = SourceData.Find(NameBox.Value)

If Found Is Nothing Then
SourceData.Resize(SourceData.Rows.Count + 1, 1).Name = "CustomerInfo"
SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value = NameBox.Value
NameBox.RowSource = Worksheets("CustomerInfo").Range("A1").Address(external:=True)
End If
End Su

 

[RPIJG]

I figured it out... Helps if you have the nam
defined....grrrr....stupid mistakes cost much time

 

[Tom Ogilvy]

This worked fine for me:

Private Sub AddName_Click()
Dim SourceData As Range
Dim Found As Object

Set SourceData = Worksheets("CustomerInfo").Range("customerinfo")
Set Found = Nothing
Set Found = SourceData.Find(NameBox.Value)

If Found Is Nothing Then
SourceData.Resize(SourceData.Rows.Count + 1, 1) _
.Name = "CustomerInfo"
SourceData.Offset(SourceData.Rows.Count, 0) _
.Resize(1, 1).Value = NameBox.Value

' see correction on next line

NameBox.ListFillRange = Worksheets("CustomerInfo") _
.Range("customerinfo").Address(external:=True)
End If
End Sub


Note that RowSource is the property when the NameBox is on a Userform. When
it is on a worksheet, you use the ListFillRange property.