I have a macro that opens a separate workbook when I right mouse click
but I want it to open to a specified sheet named "Wizard". It opens
the workbook by reading the string text displayed in M373 (see below).

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

Thanks for any help you can offer.

Michael

I like to keep it simple. Just recorded and edited this

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 12/19/2011 by Donald B. Guillett
'

'
Workbooks.Open(Filename:= _
"C:\yourfoldername\yourfilename.xls", _
UpdateLinks:=3).RunAutoMacros Which:=xlAutoOpen
Sheets("yoursheetname").Select
Range("A12").Select
End Sub



On Dec 19, 12:53*pm, Michael Lanier wrote:
I have a macro that opens a separate workbook when I right mouse click
but I want it to open to a specified sheet named "Wizard". It opens
the workbook by reading the string text displayed in M373 (see below).

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

Thanks for any help you can offer.

Michael

Don

I must be missing something still. The Workbooks.Open... line
debugged. I arrangend your macro as follows:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
Workbooks.Open(Filename:="C:\Documents\E-Z Vocal Help Menu.xls",
UpdateLinks:=3).RunAutoMacros Which:=xlAutoOpen
Sheets("Wizard").Select
Range("A1").Select
End Sub

Do you see where I went wrong? Many thanks.

Michael

It happens that Michael Lanier formulated :
UpdateLinks:=3).RunAutoMacros Which:=xlAutoOpen

Change the above to...

UpdateLinks:=3, RunAutoMacros Which:=xlAutoOpen

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Oops! Should have waited until after my 1st coffee...

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Workbooks.Open _
Filename:="C:\Documents\E-Z Vocal Help Menu.xls", UpdateLinks:=3
Application.RunAutoMacros Which:=xlAutoOpen
Sheets("Wizard").Select: Range("A1").Select
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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