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

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.

--
Regards,
Tom Ogilvy


"Flemming" wrote:

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

Thanks Tom



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Flemming" wrote:

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

Flemming,

Try code like the following:

Sub EnterDesignMode()
Application.CommandBars.FindControl(ID:=1605).Exec ute
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" wrote in message
...
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

Hey Chip

Thanks really helpful - had found the 1605 Execute

Thanks,
Flemming


"Chip Pearson" wrote in message
...
Flemming,

Try code like the following:

Sub EnterDesignMode()
Application.CommandBars.FindControl(ID:=1605).Exec ute
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" wrote in message
...
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

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" wrote in message
...
Flemming,

Try code like the following:

Sub EnterDesignMode()
Application.CommandBars.FindControl(ID:=1605).Exec ute
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" wrote in message
...
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

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)

"Flemming" wrote in message
...
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" wrote in message
...
Flemming,

Try code like the following:

Sub EnterDesignMode()
Application.CommandBars.FindControl(ID:=1605).Exec ute
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" wrote in message
...
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

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).Stat e Then
Application.CommandBars.FindControl(ID:=2597).Exec ute
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" wrote in message
...
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" wrote in message
...
Flemming,

Try code like the following:

Sub EnterDesignMode()
Application.CommandBars.FindControl(ID:=1605).Exec ute
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" wrote in message
...
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

Thanks a lot all for your participation in my issue.