Michael Lanier presented the following explanation :
Garry
Unfortunately, yours and Don's suggestions still debug, which may well
be something I'm doing. I have been able to get my initial macro to
work which was due to a path problem in my string text in
Worksheets("DataBase").Range("M373"). However, I could very much use
any suggestions you might have that would allow me to jump to a
designated sheet when the file is opened. Everything I've tried fails
to execute once the file is opened. Regardless, thanks for your
suggestions.
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
Dim VHM As Workbook
Set VHM = ActiveWorkbook
Dim exlApp As Excel.Application
Set exlApp = New Excel.Application
exlApp.Visible = True
exlApp.Workbooks.Open Worksheets("DataBase").Range("M373")
VHM.Activate
Cancel = True
End Sub
Michael
The file you open here is...
exlApp.Workbooks("E-Z Vocal Help Menu.xls")
...which you need to set a reference to if you want to work with it.
What I don't understand is why you need to open this file in a separate
instance of Excel. Assuming that Sheets("Wizard") is in the opened
file:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim wkbSource As Workbook, sFilename As String
sFilename = ActiveWorkbook.Sheets("DataBase").Range("M373").Te xt
'//assumes cell contains "C:\Documents\E-Z Vocal Help Menu.xls"
'Hide screen activity
Application.ScreenUpdating = False
Set wkbSource = Workbooks.Open(Filename:=sFilename, UpdateLinks:=3)
Application.RunAutoMacros xlAutoOpen
With wkbSource
.Sheets("Wizard").Select: .Range("A1").Select
'//do whatever else needs doing...
End With 'wkbSource
'Display the open file when ready
Application.ScreenUpdating = True
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc