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

-------------------


The Error I get is..Run-time error 1004, Method 'Range' of '_Global
Faile

--
Message posted from http://www.ExcelForum.com

Again, replied in the MrExcel board...

--
Regards

Juan Pablo González

"RPIJG " wrote in message
...
I'm having a problem adding an item to a list in a combobox that is
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

--------------------


The Error I get is..Run-time error 1004, Method 'Range' of '_Global'
Failed


---
Message posted from http://www.ExcelForum.com/

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.
--
Regards,
Tom Ogilvy

"RPIJG " wrote in message
...
I'm having a problem adding an item to a list in a combobox that is
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

--------------------


The Error I get is..Run-time error 1004, Method 'Range' of '_Global'
Failed


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

See the second part of my answer:

Does that fit your situation.

--
Regards,
Tom Ogilvy

"RPIJG " wrote in message
...
your reply did not fix the problem, hence why I searched other outlets
for help. I'm getting the same error either way as I posted above.
Thanks.


---
Message posted from http://www.ExcelForum.com/

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").A ddress(external:=True)
End I
-------------------


I'm using ExcelX

--
Message posted from http://www.ExcelForum.com

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(ext ernal:=True)
End If
End Su
-------------------


--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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.

--
Regards,
Tom Ogilvy

"RPIJG " wrote in message
...
I guess maybe I'm confused here...

I have a box open when the workbook opens and from the NameBox I want
that value entered into the worksheet named CustomerInfo, and then this
will add itself to the combobox list. I'm getting a subscript out of
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").A ddress(external:=True)
End If
--------------------


I'm using ExcelXP


---
Message posted from http://www.ExcelForum.com/