Application-defined or object-defined error on copy

I have an Excel 97-2003 add-in that includes a toolbar that includes a copy
of Excel's Design Mode toolbar button (View | Toolbars | Control Toolbox).
My users click this button to put the worksheet into design mode so they can
change the layout of controls (command buttons, etc) programatically placed
onto the worksheet.

Another button on this toolbar allows them to save their updated worksheet
design. If they click this button while they are still in design mode, then
when my VBA code gets around to performing:

thisWorkbook.ActiveSheet.Copy after:=thatWorkbook.Worksheets(1)

it gets "Application-defined or object-defined error" error message.

The way I've implemented the design mode, my code never sees the user's
button click that puts them into (or out of) design mode. So I currently
have no way to "remember" that I'm currently in design mode and get myself
out before trying to save the redesigned worksheet (of course that assumes I
know how to programatically get our of design mode and I don't).

So a few questions:

- Is there a way for my code to detect that the workbook (or is it the
project?) is still in design mode? If so how?

- If there is, is there some way for my code to get out of design mode?

- Is there some other way of having a toolbar button toggle in and out of
design mode where my code is in the execution loop rather than just copying
Excel's Design Mode toolbar button onto my toolbar?

TIA,

josh

I thought you asked this before and were provided a response. Did it not
work? I didn't test it.

http://groups-beta.google.com/group/...8a74e3235117b4

Watch the line breaks in the URL.

--

Vasant

"Josh Sale" <jsale@tril dot cod> wrote in message
news:[email protected]...
> I have an Excel 97-2003 add-in that includes a toolbar that includes a
copy
> of Excel's Design Mode toolbar button (View | Toolbars | Control Toolbox).
> My users click this button to put the worksheet into design mode so they
can
> change the layout of controls (command buttons, etc) programatically
placed
> onto the worksheet.
>
> Another button on this toolbar allows them to save their updated worksheet
> design. If they click this button while they are still in design mode,
then
> when my VBA code gets around to performing:
>
> thisWorkbook.ActiveSheet.Copy after:=thatWorkbook.Worksheets(1)
>
> it gets "Application-defined or object-defined error" error message.
>
> The way I've implemented the design mode, my code never sees the user's
> button click that puts them into (or out of) design mode. So I currently
> have no way to "remember" that I'm currently in design mode and get myself
> out before trying to save the redesigned worksheet (of course that assumes
I
> know how to programatically get our of design mode and I don't).
>
> So a few questions:
>
> - Is there a way for my code to detect that the workbook (or is it the
> project?) is still in design mode? If so how?
>
> - If there is, is there some way for my code to get out of design mode?
>
> - Is there some other way of having a toolbar button toggle in and out of
> design mode where my code is in the execution loop rather than just
copying
> Excel's Design Mode toolbar button onto my toolbar?
>
> TIA,
>
> josh
>
>

Indeed, I asked a related question a couple of months ago. The response I
got was that VBA code doesn't run in design mode. So if you're code is
running, then you're guarenteed to not be in design mode.

In digging around at the time, I found a property of (as I recall) the VBE
Project object that purported to show if the project was in design mode and
sure enough, this property reported that the project was always Running when
I examined that property from by VBA code ... even if the project was in
design mode when I kicked off my code.

So I blew off the problem and moved on.

Now my users are running into problems if they switch into design mode, made
changes and then try to save their updates without first toggeling out of
design mode (as described in my original post).

There is (it seems to me) clearly more to the design mode story than VBA
code never runs in design mode. If you put a project into design mode and
then click a toolbar button that runs some VBA code, even if the project
isn't in design mode while the code is running, it clearly is back in design
mode when the VBA code completes (e.g., clicking a command button on the
worksheet selects the command button rather than raising the click event).

Sorry to be so long winded ...

Thanks,

josh





"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:%[email protected]...
>I thought you asked this before and were provided a response. Did it not
> work? I didn't test it.
>
> http://groups-beta.google.com/group/...8a74e3235117b4
>
> Watch the line breaks in the URL.
>
> --
>
> Vasant
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:[email protected]...
>> I have an Excel 97-2003 add-in that includes a toolbar that includes a
> copy
>> of Excel's Design Mode toolbar button (View | Toolbars | Control
>> Toolbox).
>> My users click this button to put the worksheet into design mode so they
> can
>> change the layout of controls (command buttons, etc) programatically
> placed
>> onto the worksheet.
>>
>> Another button on this toolbar allows them to save their updated
>> worksheet
>> design. If they click this button while they are still in design mode,
> then
>> when my VBA code gets around to performing:
>>
>> thisWorkbook.ActiveSheet.Copy after:=thatWorkbook.Worksheets(1)
>>
>> it gets "Application-defined or object-defined error" error message.
>>
>> The way I've implemented the design mode, my code never sees the user's
>> button click that puts them into (or out of) design mode. So I currently
>> have no way to "remember" that I'm currently in design mode and get
>> myself
>> out before trying to save the redesigned worksheet (of course that
>> assumes
> I
>> know how to programatically get our of design mode and I don't).
>>
>> So a few questions:
>>
>> - Is there a way for my code to detect that the workbook (or is it the
>> project?) is still in design mode? If so how?
>>
>> - If there is, is there some way for my code to get out of design mode?
>>
>> - Is there some other way of having a toolbar button toggle in and out of
>> design mode where my code is in the execution loop rather than just
> copying
>> Excel's Design Mode toolbar button onto my toolbar?
>>
>> TIA,
>>
>> josh
>>
>>
>
>

Please consider this matter resolved.

Thanks,

josh