Macro that opens a separate workbook to a specified worksheet whenthe active workbook is right mouse

  • Thread starter Thread starter Michael Lanier
  • Start date Start date
M

Michael Lanier

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

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
 
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
Click to expand...

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").Text
'//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

Thanks for all your effort. I tried the macro in my existing file as
well as a new file and in both it does to things. It debugs in the
following line in both:

Application.RunAutoMacros xlAutoOpen

It does however open the file. I fear I'm going to have to punt on
this one and try something else, but again, thanks for your input.

Michael
 
Back
Top