Best way to open a macro in design view using VBA

[Thomas Koester]

Hello group,

I need to open a macro in design view using VBA. Here the code I am currently
using:

Private Sub OpenMacroInDesignMode(sMacro as String)
'-- activate database window (what enables its menu)
SendKeys "{F11}"
'-- wait until database window is open and menu is available
DoEvents
'-- now go to the screen with the macros
DoCmd.RunCommand acCmdViewMacros
'-- typing the name will select the wanted macro
SendKeys sMacro
'-- CTRL+ENTER will open the macro in design view
SendKeys "^{ENTER}"
End Sub

It works (in A2K) but I am not sure how version- and language-safe this is
(it is part of a published Add-In). Does anybody know a way to do this
without SendKeys?

Thanks

 

[Robert Morley]

Not sure about the actual opening part, but I can certainly get you a lot
closer to a safe method of opening a macro:

Private Sub OpenMacroInDesignMode(sMacro as String)
DoCmd.SelectObject acMacro, sMacro, True
'-- CTRL+ENTER will open the macro in design view
SendKeys "^{ENTER}"
End Sub


Rob

 

[Thomas Koester]

Thank you Robert, works like a charm. So at least on SendKeys less.

Maybe the wrong newsgroup but does somebody know if CTRL+ENTER opens an
object in design view in all Access versions 2000 and up?

 

[Robert Morley]

I can confirm that it does on Access XP and 2003, at least. I no longer
have Access 2000 installed, unfortunately, but if you're really desperate
and nobody else has answered in this thread, ask me again on Tuesday. I
still have the install files for 2000, so I can always install it in a VM
and verify. I'm about 99.99% certain that it does, though. As for 2007,
you're on your own; I haven't yet upgraded(?) to the ribbon.

If Ctrl-Enter fails on one of the versions, Alt-D might work instead.
Again, it certainly does on XP and 2003.


Rob

 

[Thomas Koester via AccessMonster.com]

Thank you again, Rob, for investigating further. I can confirm that it does
on Access 2000 (that's my development platform) and I found a web page
somewhere that listed the shortkey in question for Access 2007.
So I think that this is sufficient evidence that your proposed code is
version-safe.