Code works for any WBK except the PMW
 

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.CodePa ne.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.Cod ePane.Show
w.VBProject.VBComponents("Module3").CodeModule.Cod ePane.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

Code works for any WBK except the PMW
 

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.CodePa ne.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.Cod ePane.Show

w.VBProject.VBComponents("Module3").CodeModule.Cod ePane.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

Code works for any WBK except the PMW
 

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 he 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.CodePa ne.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.Cod ePane.Show

w.VBProject.VBComponents("Module3").CodeModule.C odePane.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

Code works for any WBK except the PMW
 

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 he 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