OK, so I edit my VBA a lot. In Word, I made sneaky macros to
quickly open my add-ins, etc., in the VBA editor and display
specific code modules. Now that I'm soaking in Excel, I tried
the same sort of code, and it SORT OF works:

Sub EditMT()
Dim w As Workbook
Set w = Workbooks.Open(Filename:="C:\Path\MTstuff.XLS")
w.VBProject.VBComponents("Module1").CodeModule.Cod ePane.Show
End Sub

Now, what would cause this to show a totally different VBA
project the *first* time it's run during an Excel session.
(Typically it shows the project that's alphabetically first
in the project explorer, if that means anything). It works
properly for the rest of the session. (Of course, the first
time tends to be when I want it open most urgently!)

I have 4 custom buttons to open different projects, and they
misbehave identically, so it's not related to the file being
opened.

TIA

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

Hi keepITcool,

Thanks for this. It looked promising, but it didn't change
the behavior. First, for some reason the very last line
throws a 'variable not found' error, and it highlights the
'VBE' -- ehhh? (The VBA extensibility library *is* added
in references.) So I preceded that with 'Application.' and
it stopped complaining, but the problem continued.

When I inserted an 'On Error GoTo 0' after the End If, then,
on this line:

wkb.VBComponents("Module1").Activate

I get an non-supported property/method error. Fixed that
by using:

wkb.VBProject.VBComponents("Module1").Activate

But the behavior continues. I added an Application.OnTime
statement at the end and moved the last line (the .Show)
into its own macro, to run 1 second later. Still no change.

Grrr. What could be wrong??

Thanks again for following up.

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

keepitcool wrote:

Mark,

try this:
1.checks to see if opened, if not does.
2.activates codemodule
3.activates VBE

Sub EditMT()
Dim wkb As Workbook
Const csPath = "c:\path\"
Const csName = "mtstuff.xls"
On Error Resume Next
Set wkb = Workbooks(csName)
If wkb Is Nothing Then
Set wkb = Workbooks.Open(csPath & csName)
End If
wkb.VBComponents("Module1").Activate
VBE.ActiveCodePane.Show
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

Mark Tangard wrote:


Sub EditMT()
Dim w As Workbook
Set w = Workbooks.Open(Filename:="C:\Path\MTstuff.XLS")
w.VBProject.VBComponents("Module1").CodeModule.Cod ePane.Show
End Sub

hmm..

xlXP is more forgiving... i've checked in xl97 and it runs flawlessly.

A reference to extensibility is not needed.
I (and the code) assume that MTSTUFF contains a module named "Module1"

Hope you dont have a Password on your VBA code?????
Check macrosecurity/ trusted sources : Trust access to VB project

Included a DoEvents..
I dont know which events mtstuff is running on opening (which should be
no problem, but you could insert a few debug.print lines to see who does
what in which sequence....


Sub EditMT()
Dim wkb As Workbook
Const csPath = "c:\path"
Const csName = "mtstuff.xls"
On Error Resume Next
Set wkb = Workbooks(csName)
On Error GoTo 0
If wkb Is Nothing Then
Set wkb = Workbooks.Open(csPath & csName)
End If
Do Events
wkb.VBProject.VBComponents("Module1").Activate
Application.VBE.ActiveCodePane.Show
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Mark Tangard wrote:

Hi keepITcool,

Thanks for this. It looked promising, but it didn't change
the behavior. First, for some reason the very last line
throws a 'variable not found' error, and it highlights the
'VBE' -- ehhh? (The VBA extensibility library *is* added
in references.) So I preceded that with 'Application.' and
it stopped complaining, but the problem continued.

When I inserted an 'On Error GoTo 0' after the End If, then,
on this line:

wkb.VBComponents("Module1").Activate

I get an non-supported property/method error. Fixed that
by using:

wkb.VBProject.VBComponents("Module1").Activate

But the behavior continues. I added an Application.OnTime
statement at the end and moved the last line (the .Show)
into its own macro, to run 1 second later. Still no change.

Grrr. What could be wrong??

Thanks again for following up.

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

keepitcool wrote:

Mark,

try this:
1.checks to see if opened, if not does.
2.activates codemodule
3.activates VBE

Damn....

This is XL2000, tested on 2 different machines (one Win98, one Win2K),
fails on both! Yes, there's a module Module1, and no password, and
security all happy. MTStuff doesn't run anything when opened, isn't
even an add-in yet (will be later). Feh.

Well, it's not a horrible thing. Sometimes this stuff fixes itself
overnight too. Thanks again.

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

keepitcool wrote:

hmm..

xlXP is more forgiving... i've checked in xl97 and it runs flawlessly.

A reference to extensibility is not needed.
I (and the code) assume that MTSTUFF contains a module named "Module1"

Hope you dont have a Password on your VBA code?????
Check macrosecurity/ trusted sources : Trust access to VB project

Included a DoEvents..
I dont know which events mtstuff is running on opening (which should be
no problem, but you could insert a few debug.print lines to see who does
what in which sequence....

Sub EditMT()
Dim wkb As Workbook
Const csPath = "c:\path"
Const csName = "mtstuff.xls"
On Error Resume Next
Set wkb = Workbooks(csName)
On Error GoTo 0
If wkb Is Nothing Then
Set wkb = Workbooks.Open(csPath & csName)
End If
Do Events
wkb.VBProject.VBComponents("Module1").Activate
Application.VBE.ActiveCodePane.Show
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

Mark Tangard wrote:

Hi keepITcool,

Thanks for this. It looked promising, but it didn't change
the behavior. First, for some reason the very last line
throws a 'variable not found' error, and it highlights the
'VBE' -- ehhh? (The VBA extensibility library *is* added
in references.) So I preceded that with 'Application.' and
it stopped complaining, but the problem continued.

When I inserted an 'On Error GoTo 0' after the End If, then,
on this line:

wkb.VBComponents("Module1").Activate

I get an non-supported property/method error. Fixed that
by using:

wkb.VBProject.VBComponents("Module1").Activate

But the behavior continues. I added an Application.OnTime
statement at the end and moved the last line (the .Show)
into its own macro, to run 1 second later. Still no change.

Grrr. What could be wrong??

Thanks again for following up.

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

keepitcool wrote:

Mark,

try this:
1.checks to see if opened, if not does.
2.activates codemodule
3.activates VBE