Problem with adding an item

R

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
 
T

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

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
 
R

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
 
R

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
 
T

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