Turn ON design mode...

[Flemming]

Hi,

When my macro runs I at some point wants to change into design mode -
knowing that I properly can not get back to run mode with out a manual act.

Anybody know the code to switch to design mode?

Thanks,
Flemming

 

[Guest]

this worked in US English version:

Sub ABC()
Application.CommandBars("Control Toolbox" _
).Controls("&Design Mode").Execute
End Sub

You might need to identify the ID of the control and use FindControl to
return a reference to it, then use the reference to execute it.

 

[Flemming]

Thanks Tom

this worked in US English version:

Sub ABC()
Application.CommandBars("Control Toolbox" _
).Controls("&Design Mode").Execute
End Sub

You might need to identify the ID of the control and use FindControl to
return a reference to it, then use the reference to execute it.

 

[Chip Pearson]

Flemming,

Try code like the following:

Sub EnterDesignMode()
Application.CommandBars.FindControl(ID:=1605).Execute
End Sub

Sub ExitDesignMode()
Dim Ctrl As CommandBarControl
Set Ctrl = Application.CommandBars.FindControl(ID:=2597)
If Ctrl.State <> 0 Then
Ctrl.Execute
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Flemming]

Hey Chip

Thanks really helpful - had found the 1605 Execute

Thanks,
Flemming

 

[Flemming]

Your ExitDesignMode module returns an error on If Ctrl.State <> 0
Then -"Run-tiem error '91' - Object variable or with block variable not set.

Could it be because of the new ribbon in Office 2007 which I use...

Cheers,
Flemming

 

[Chip Pearson]

I wrote and tested the code in Excel 2003. I'll see what's going on in 2007.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Peter T]

I don't know about the situation in 2007 but in earlier versions I find it's
not always possible to set a reference to the Exit Design Mode control,
depending on how and where (same or different project Design mode was
entered) the code is run.

Sometimes as soon as code starts design mode can automatically exit and the
control no longer exists, akin to the changing 'existence' of the Insert
button in different scenarios.

I think even in earlier versions it might be worth checking 'Ctrl' returns a
reference to the control. If it's Nothing, either the mere fact of running
the code exited Design Mode or Design mode was not entered in the first
place.

Alternatively -

If Application.CommandBars.FindControl(ID:=1605).State Then
Application.CommandBars.FindControl(ID:=2597).Execute
End If

plus a bit of error handling and/or attempt to first to set references to
the controls and only proceed if they are not nothing.

Regards,
Peter T

 

[Flemming]

Thanks a lot all for your participation in my issue.