Code works for any WBK except the PMW

[Mark Tangard]

Hi gang. This a simple macro to open the VBE to a given module of a
given workbook for editing. It work perfectly for any workbook except
the Personal Macro Workbook. Is the PMW under some vague (if not
sloppy) protection as this implies?

Dim w As Workbook
Set w = Workbooks.Open(Filename:="J:\Test.xls")
w.VBProject.VBComponents("Bzzz").CodeModule.CodePane.Show
SendKeys "^{end}"

(In case you’re wondering, the macro does not live *in* the PMW.)

For any other workbook, this code brings up the Bzzz module in the
foreground. When tried on the PMW it does indeed open the VBE but the
foreground code window is chosen seemingly at random. Any of its 4
regular modules may end up with the focus, as well as the one userform
code window. The only window that never comes up is ThisWorkbook.
Often (but not consistently) the FIRST attempt in a given session brings
up the correct module, but the majority of later attempts won’t. The
workbook isn’t very old, hasn’t been terribly active, and has never been
fed poor-quality meat.

I’ve experimented with the lines below, in various permutations, in
addition to (and instead of) the .Show line above. Nothing seems to
make a difference.

w.VBProject.VBComponents("Module3").CodeModule.CodePane.Show
w.VBProject.VBComponents("Module3").CodeModule.CodePane.Window.SetFocus
w.VBProject.VBComponents("Module3").Activate

Is there a secret to this? TIA TIA TIA.

-----------------------
Mark Tangard
"Life is nothing if you're not obsessed." --John Waters

[keepITcool]

Mark,

I cant replicate.
Got rid of the sendkeys.. and following works for me:
called from VBE
called from MacroDialog in excel, with VBE invisible

NOTE:
"Personal.xls" is localized (in Dutch:Persnlk.xls)
not everybody has a personal.xls
not everybody has a module called "module1"


So.. you need an errorhandler.


Sub ShowPMW()
Dim w As Workbook
Set w = Workbooks.Open(Application.StartupPath & "\Personal.xls")
With w.VBProject.VBComponents("Module1").CodeModule
.CodePane.Show
.CodePane.SetSelection .CountOfLines + 1, 1, .CountOfLines + 1, 1
End With
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mark Tangard wrote :

> Hi gang. This a simple macro to open the VBE to a given module of a
> given workbook for editing. It work perfectly for any workbook
> except the Personal Macro Workbook. Is the PMW under some vague (if
> not sloppy) protection as this implies?
>
> Dim w As Workbook
> Set w = Workbooks.Open(Filename:="J:\Test.xls")
> w.VBProject.VBComponents("Bzzz").CodeModule.CodePane.Show
> SendKeys "^{end}"
>
> (In case you’re wondering, the macro does not live in the PMW.)
>
> For any other workbook, this code brings up the Bzzz module in the
> foreground. When tried on the PMW it does indeed open the VBE but
> the foreground code window is chosen seemingly at random. Any of its
> 4 regular modules may end up with the focus, as well as the one
> userform code window. The only window that never comes up is
> ThisWorkbook. Often (but not consistently) the FIRST attempt in a
> given session brings up the correct module, but the majority of later
> attempts won’t. The workbook isn’t very old, hasn’t been terribly
> active, and has never been fed poor-quality meat.
>
> I’ve experimented with the lines below, in various permutations, in
> addition to (and instead of) the .Show line above. Nothing seems to
> make a difference.
>
> w.VBProject.VBComponents("Module3").CodeModule.CodePane.Show
>
> w.VBProject.VBComponents("Module3").CodeModule.CodePane.Window.SetFocu
> s w.VBProject.VBComponents("Module3").Activate
>
> Is there a secret to this? TIA TIA TIA.
>
> -----------------------
> Mark Tangard
> "Life is nothing if you're not obsessed." --John Waters

[Mark Tangard]

Hi keepITcool,

Sorry for the huge delay (my job broke into my reverie...). I really appreciate
this solution.

One question for you or anyone else here: Is there a primer-level book or site
that deals with the use of the objects/properties/methods that are specific to
code that operates *within* the VBE (such as everyting beyond your second line
of code below)? I find myself in increasingly frequent need of understanding
these, in both Word and Excel, but I can't even find a list of them from which
to intuit their purposes. Lots of folks online seem to know them by heart.
Where can I learn what they've learned?

BTW, re error handler: This macro is for only 2 users whose VBE setups are
static. I would've used an error handler if this macro had been designed for
mass consumption, but, going by the standards set in the Word newsgroups, where
I was a proflic poster and MVP for some years, I would've excluded the handler
code from the post so as to focus attention on the main question. Is that
generally not advised here? (I'd like to know since I expect to be back at
intervals with even dumber questions.)

TIA

------------
Mark Tangard
"Life is nothing if you're not obsessed." --John Waters




keepITcool wrote:
> Mark,
>
> I cant replicate.
> Got rid of the sendkeys.. and following works for me:
> called from VBE
> called from MacroDialog in excel, with VBE invisible
>
> NOTE:
> "Personal.xls" is localized (in Dutch:Persnlk.xls)
> not everybody has a personal.xls
> not everybody has a module called "module1"
>
>
> So.. you need an errorhandler.
>
>
> Sub ShowPMW()
> Dim w As Workbook
> Set w = Workbooks.Open(Application.StartupPath & "\Personal.xls")
> With w.VBProject.VBComponents("Module1").CodeModule
> .CodePane.Show
> .CodePane.SetSelection .CountOfLines + 1, 1, .CountOfLines + 1, 1
> End With
> End Sub
>
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Mark Tangard wrote :
>
>
>>Hi gang. This a simple macro to open the VBE to a given module of a
>>given workbook for editing. It work perfectly for any workbook
>>except the Personal Macro Workbook. Is the PMW under some vague (if
>>not sloppy) protection as this implies?
>>
>> Dim w As Workbook
>> Set w = Workbooks.Open(Filename:="J:\Test.xls")
>> w.VBProject.VBComponents("Bzzz").CodeModule.CodePane.Show
>> SendKeys "^{end}"
>>
>>(In case you’re wondering, the macro does not live in the PMW.)
>>
>>For any other workbook, this code brings up the Bzzz module in the
>>foreground. When tried on the PMW it does indeed open the VBE but
>>the foreground code window is chosen seemingly at random. Any of its
>>4 regular modules may end up with the focus, as well as the one
>>userform code window. The only window that never comes up is
>>ThisWorkbook. Often (but not consistently) the FIRST attempt in a
>>given session brings up the correct module, but the majority of later
>>attempts won’t. The workbook isn’t very old, hasn’t been terribly
>>active, and has never been fed poor-quality meat.
>>
>>I’ve experimented with the lines below, in various permutations, in
>>addition to (and instead of) the .Show line above. Nothing seems to
>>make a difference.
>>
>> w.VBProject.VBComponents("Module3").CodeModule.CodePane.Show
>>
>>w.VBProject.VBComponents("Module3").CodeModule.CodePane.Window.SetFocu
>>s w.VBProject.VBComponents("Module3").Activate
>>
>>Is there a secret to this? TIA TIA TIA.
>>
>>-----------------------
>>Mark Tangard
>>"Life is nothing if you're not obsessed." --John Waters

[keepITcool]

re VBIDE object model.

set reference to
Microsoft Visual Basic for Application Extensibility.
then in object browser select VBE and press F1..

re errorhandling:
it was just a remark.
imo personal.xls s/b a dummy and temporary book only.
any code "for keeps" should be moved to another book.
(which may also be in xlstart)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mark Tangard wrote :

> Hi keepITcool,
>
> Sorry for the huge delay (my job broke into my reverie...). I really
> appreciate this solution.
>
> One question for you or anyone else here: Is there a primer-level
> book or site that deals with the use of the
> objects/properties/methods that are specific to code that operates
> within the VBE (such as everyting beyond your second line of code
> below)? I find myself in increasingly frequent need of understanding
> these, in both Word and Excel, but I can't even find a list of them
> from which to intuit their purposes. Lots of folks online seem to
> know them by heart. Where can I learn what they've learned?
>
> BTW, re error handler: This macro is for only 2 users whose VBE
> setups are static. I would've used an error handler if this macro
> had been designed for mass consumption, but, going by the standards
> set in the Word newsgroups, where I was a proflic poster and MVP for
> some years, I would've excluded the handler code from the post so as
> to focus attention on the main question. Is that generally not
> advised here? (I'd like to know since I expect to be back at
> intervals with even dumber questions.)
>
> TIA
>
> ------------
> Mark Tangard
> "Life is nothing if you're not obsessed." --John Waters
>
>