Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheet whenthe active workbook is right mouse clicked
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheetwhen the active workbook is right mouse clicked
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheetwhen the active workbook is right mouse clicked
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheet when the active workbook is right mouse clicked
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheet when the active workbook is right mouse clicked
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheetwhen the active workbook is right mouse clicked
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheet when the active workbook is right mouse clicked
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheetwhen the active workbook is right mouse clicked
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that opens a separate workbook to a specified worksheet when the active workbook is right mouse clicked
It happens that GS formulated :
Application.RunAutoMacros xlAutoOpen Sorry.., my bad! Change the above line to: wkbSource.RunAutoMacros xlAutoOpen -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy active worksheet to new workbook | Excel Discussion (Misc queries) | |||
When I open a workbook, how can I choose which worksheet opens? | Excel Worksheet Functions | |||
Issuing macro in workbook from separate workbook | Excel Discussion (Misc queries) | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
How to end macro on inital active worksheet containing macro button that was clicked | Excel Programming |