Excel 2007 Selection.Characters.Text 1004 Error

[wifirocks]

All,

I have a macro that does some error checking on the user's input, changes the control/shape text and assigned macro, and then save the file to a SharePoint site. The problem arrises when the macro changes the control/shape text; for some people it works without issue (like myself) and for others they get an error "400" (in actuality it's a runtime error 1004, unable to set the text property... but from a user perspective they see a 400 error).

The worksheets are protected; however, in the code I unprotect the sheets prior to the section of code I have posted below (it's a lot of code, so i'm just posting the relevant section.

If someone has a better way of performing the actions I am attempting to accomplish I'm all eyes. Please note, the reason I am changing the text and assigned macro of the control/shape is that this is part of a process involving different people (e.g. one person initiates the process, uploads the workbook, another person does another portion of the process, etc.).

Any help with this baffling problem would be greatly appreciated!

Thanks!

    'Change Send and upload Macro Button to Sales Engineering Manager
 
    Worksheets("CONTACTS").Activate
    Worksheets("CONTACTS").Shapes("Button 2883").Select
    Selection.Characters.Text = "Save File and Send to Sales Engineering Manager"
        With Selection.Characters(Start:=1, Length:=47).Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 14
        End With
    Selection.OnAction = "Sheet14.SendFormToSEM"
    Worksheets("SITE INFORMATION").Activate
    Worksheets("SITE INFORMATION").Shapes("Button 3140").Select
    Selection.Characters.Text = "Save File and Send to Sales Engineering Manager"
        With Selection.Characters(Start:=1, Length:=47).Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 14
        End With
    Selection.OnAction = "Sheet14.SendFormToSEM"
    Worksheets("CONTACTS").Activate
    Range("H6:X6").Select

 

[Warship]

Couple quick thoughts...

Is everyone on the same Excel Ver?

Now way out in left field as a work around:
Maybe create two buttons then hide/unhide as needed?

Even further out:
Could Trust settings be interfering in some way?

Maybe do OnAction before text change?


When one box runs it and the next one don't, it can be a bother sorting it out...

Good luck and welcome to the board.

 

[wifirocks]

Warship,

Thanks for the quick reply. In regards to Execl versions everyone who has had a problem has reported using 2007. Of course, that may be only part of it because there is no guarentee that they have all the same service packs, fixes, etc. Most are Windows 7 users with a couple of XP users thrown in for good measure (not sure if that would make a difference).

Thanks for the tip on hiding/unhiding the buttons; however, I don't think that will work in this case. There are up to 10 "steps" in this process, which would mean having 10 different buttons to manage (and I don't have the room to fit that many in regardless).

It could possibly an issue in the trust settings. I pull kind of a fudge when the workbook is opened by having a "Macro Instruction" sheet as the visable sheet, if macros are enabled then the sheet gets hidden, if they are not enabled then all they get is the sheet with instructions on how to enable their macros.

I never get an issue when running the code, but one thing I did do is enable the directory where the workbook resides to be a trusted location. That maybe something to look at *except* that when the workbook is opened I save the file to the HOMEPATH in the users environment variable, and that is not in my trusted locations settings, which may/may not be an issue. Regardless I'll take that out and see if I can make it fail for me (UGH).

Not familiar with OnAction but I can look it up and try it (any port in a storm!).

Thanks!

 

[Warship]

10 different buttons to manage (and I don't have the room to fit that many in regardless)

I have stacked two in the same location before (for the life of me, I can't rem why tho) so no extra space is needed. Hide all but the one the user should have. 10 though may become a bit much.

Not familiar with OnAction

I meant to simply re-order the code you already have:

    Selection.OnAction = "Sheet14.SendFormToSEM"
    Selection.Characters.Text = "Save File and Send to Sales Engineering Manager"
        With Selection.Characters(Start:=1, Length:=47).Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 14
        End With

Like you said - "any port". Sometimes the weirdest things bother Excel.

GOOD LUCK

 

[wifirocks]

Warship,

Just as a follow up I tried fiddling with my trust center settings to see if the code would fail for me, naturally works like a charm every time, and I don't have a reliable user who can test changes for me.

I think; however, what I may do is fire up a VM run the code on a version of Office without the service packs and fixes and see if I can make it fail that way.

Thanks for all the help!

 

[Warship]

Couple thoughts:

If you can't change the text, maybe it will let you delete and re-create the button with the changed text. You could check Excel's Version and do it this way only for 2007.

If you can change to ActiveX, you could use a Select Case in the Click event and run code based on the Button's Caption.

 

[wifirocks]

Hi Warship,

Figured I'd followup with this post. I "semi" found the problem, it was with the text length (ugh). On my PC I have Excel 2007 (SP2) and Win7, never a problem with the Selection.Characters property. With a lot of the users that are having issues they have Excel 2007 (SP1) and WinXP.

So I fired up a XP virtual machine with no service packs for the OS or Excel. Lo and behold, when I kept the text string below 12 characters, no errors.

Not sure if it is an XP service pack issue, or an Excel SP issue (don't want to go through the hassle of making another VM to find out). I developed kind of a workaround for it, essential grouping a bunch of form controls and then just modifying the text properties and assigned macros.

Thanks...