Problem with adding an item
 

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

Problem with adding an item
 

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/

Problem with adding an item
 

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/

Problem with adding an item
 

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

Problem with adding an item
 

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/

Problem with adding an item
 

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

Problem with adding an item
 

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

Problem with adding an item
 

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

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

Problem with adding an item
 

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/