getting Runtime Error 1004

[ArielZusya]

I've got the following code:

Sub CreateButton()
Dim btnMyButton As Button

Set btnMyButton = ActiveSheet.Buttons.Add(460, 75, 140, 30)

btnMyButton.Caption = "Delete and Update Charts and Lists"
btnMyButton.Select
Selection.OnAction = "btnDeleteAndUpdateSeatingChart"
Selection.Name = "btnDeleteAndUpdate"
End Sub

If I create a new workboox and then create a module with this sub in it and
then run it it works without error. However, when I run it from a second
workbook I get the error. In other words if I use the current workbook to
run vba script that copys the current worksheet to a new workbook and then
while that new workbook is active I run the above code it creates the button
on the new worksheet but when it tries to change the caption on the button
from "Button 1" to "Delete and Update Charts and Lists" I get Runtime Error
1004: Unable to update the Caption Property of the Button Class. Anyone have
any idea why this would be? I've tried saving the new workbook before
running the create button script and it had no impact on the problem. This
is in Excel 2007. Your help is greatly appreciated. Thanks!

 

[Dave Peterson]

I'd drop the select's:

Option Explicit

Sub CreateButton()
Dim btnMyButton As Button

Set btnMyButton = ActiveSheet.Buttons.Add(460, 75, 140, 30)

btnMyButton.Caption = "Delete and Update Charts and Lists"
btnMyButton.OnAction = "btnDeleteAndUpdateSeatingChart"
btnMyButton.Name = "btnDeleteAndUpdate"
End Sub

In fact, I'd be more specific with the .onaction portion.

btnMyButton.OnAction _
= "'" & ActiveSheet.Parent.Name & "'!btnDeleteAndUpdateSeatingChart"

 

[ArielZusya]

That has no impact. Even if I change this to:


Sub CreateButton()
Dim btnMyButton As Button
Set btnMyButton = ActiveSheet.Buttons.Add(460, 75, 140, 30)
btnMyButton.Caption = "Delete and Update Charts and Lists"
End Sub


It errors out with that error at btnMybutton.Caption = "Delete..." The
problem is definately at btnMyButton.Caption = "Delete..." If I step through
the sub before it runs this line a mouseover the work btnMyButton has the
float bubble of "Button 1" the caption of the button before it's renamed (not
sure if that's useful to know). thanks!

 

[Dave Peterson]

The code I posted worked fine for me.

Does that suggested code work in a brand new workbook?

Just a guess...Is the worksheet protected?

 

[ArielZusya]

Are you using Excel 2007? I'm worried that the techs here didn't do a very
good job on their upgrade (they insisted on upgrading my system and I have no
ability to update or change anything). Incidentally, to be more specific, on
your recommendation, I changed the code as follows:

Set btnMyButton =
Workbooks(stMyWorkbookName).Sheets("TextAdjust").Buttons.Add(460, 75, 140, 30)

and then pass stMyWorkbookName to the sub with the name of the new file set
to that string. As for protection, all the other worksheets in the new book
are protected but this worksheet isn't. The point of this worksheet is to
let the user edit the data, click the button and have the system update the
other worksheets in the same workbook (which have a number of formulas
referring to one another) without messing with their referential integrity.
for that reason I've protected the other sheets but left this sheet alone.
Anyhow... if you have any suggestions I'd be greatful. Thanks!

 

[Dave Peterson]

I used xl2003, but I don't see anything in your code that would cause problems
with xl2007.

Did you try the code on a test workbook/worksheet?

Are you using Excel 2007? I'm worried that the techs here didn't do a very
good job on their upgrade (they insisted on upgrading my system and I have no
ability to update or change anything). Incidentally, to be more specific, on
your recommendation, I changed the code as follows:

Set btnMyButton =
Workbooks(stMyWorkbookName).Sheets("TextAdjust").Buttons.Add(460, 75, 140, 30)

and then pass stMyWorkbookName to the sub with the name of the new file set
to that string. As for protection, all the other worksheets in the new book
are protected but this worksheet isn't. The point of this worksheet is to
let the user edit the data, click the button and have the system update the
other worksheets in the same workbook (which have a number of formulas
referring to one another) without messing with their referential integrity.
for that reason I've protected the other sheets but left this sheet alone.
Anyhow... if you have any suggestions I'd be greatful. Thanks!

 

[ArielZusya]

I did try the code on a test workbook/worksheet and had the same result.
While the code may be fine in 2003, there may be a yet undiagnosed bug in
2007. I know in the past there were a number of bugs in 2003 that produced
error 1004. Perhaps this is just that... an undxed bug. Everyone else I've
shown this code to on various usergroups run the code and get no error... but
I've yet to hear from someone running the code on xl2007 that they are
successful. since that seems to be the only difference between my system and
the system others use to run it I'm starting to think this might be a problem
with xl2007. I have it on one of my computers at home and since I installed
it myself I know how it was installed and if it is up to date (details over
which I have no control at the office) and so I can see if the issue repeats
at home. The only trouble there would be that my copy at home is a full
version license (not an upgrade... in fact its the version that MS provided
at the launch... which I attended here in Denver) and while many will say
that there is no difference, I've found in the past that a clean install full
version of an MS app is less buggy than an upgrade install... installing one
on top of the other. Consequently, I can't say difinitively that my home test
will answer the question as to whether the blame lies with 2007 or elsewhere.
Anyhow... if you have any other thoughts let me know... if not I'll just
assume it's isolated to 2007 until I can run my home test or hear from
someone else. Thanks!

 

[Dave Peterson]

Try changing the length of the caption. Fewer than 34 characters.

I did try the code on a test workbook/worksheet and had the same result.
While the code may be fine in 2003, there may be a yet undiagnosed bug in
2007. I know in the past there were a number of bugs in 2003 that produced
error 1004. Perhaps this is just that... an undxed bug. Everyone else I've
shown this code to on various usergroups run the code and get no error... but
I've yet to hear from someone running the code on xl2007 that they are
successful. since that seems to be the only difference between my system and
the system others use to run it I'm starting to think this might be a problem
with xl2007. I have it on one of my computers at home and since I installed
it myself I know how it was installed and if it is up to date (details over
which I have no control at the office) and so I can see if the issue repeats
at home. The only trouble there would be that my copy at home is a full
version license (not an upgrade... in fact its the version that MS provided
at the launch... which I attended here in Denver) and while many will say
that there is no difference, I've found in the past that a clean install full
version of an MS app is less buggy than an upgrade install... installing one
on top of the other. Consequently, I can't say difinitively that my home test
will answer the question as to whether the blame lies with 2007 or elsewhere.
Anyhow... if you have any other thoughts let me know... if not I'll just
assume it's isolated to 2007 until I can run my home test or hear from
someone else. Thanks!

 

[Dave Peterson]

Make that less than 33 characters.

This worked: "Delete and Update Charts and Lis"
This didn't: "Delete and Update Charts and List"

I did try the code on a test workbook/worksheet and had the same result.
While the code may be fine in 2003, there may be a yet undiagnosed bug in
2007. I know in the past there were a number of bugs in 2003 that produced
error 1004. Perhaps this is just that... an undxed bug. Everyone else I've
shown this code to on various usergroups run the code and get no error... but
I've yet to hear from someone running the code on xl2007 that they are
successful. since that seems to be the only difference between my system and
the system others use to run it I'm starting to think this might be a problem
with xl2007. I have it on one of my computers at home and since I installed
it myself I know how it was installed and if it is up to date (details over
which I have no control at the office) and so I can see if the issue repeats
at home. The only trouble there would be that my copy at home is a full
version license (not an upgrade... in fact its the version that MS provided
at the launch... which I attended here in Denver) and while many will say
that there is no difference, I've found in the past that a clean install full
version of an MS app is less buggy than an upgrade install... installing one
on top of the other. Consequently, I can't say difinitively that my home test
will answer the question as to whether the blame lies with 2007 or elsewhere.
Anyhow... if you have any other thoughts let me know... if not I'll just
assume it's isolated to 2007 until I can run my home test or hear from
someone else. Thanks!