Code works for any WBK except the PMW

M

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.
 
K

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 :
 
M

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
 
K

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 :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top