Macro to open workbook

sdrubezki

New Member
Joined
Aug 5, 2010
Messages
16
Hello everyone, I have a workbook and the first sheet is an index to go to other sheets in the same file, each sheet is a form.

The info for those forms is in different workbooks, so what I need is a macro to open a certain workbook when a certain sheet is selected, keep it hiden and close it when user hits a send button.

Can anyone assist?
Thanks much in advanced.
Sebastian
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hello everyone, I have a workbook and the first sheet is an index to go to other sheets in the same file, each sheet is a form.

The info for those forms is in different workbooks, so what I need is a macro to open a certain workbook when a certain sheet is selected, keep it hiden and close it when user hits a send button.

Can anyone assist?
Thanks much in advanced.
Sebastian


Maybe:

Code:
Private Sub Worksheet_Activate() ' goes into the certain sheet code
    Workbooks.Open Filename:= _
        "D:\PATH\Certain Workbook.xls"
    ActiveWindow.Visible = False
End Sub

Call the below in the SEND buttons click/doubleclick event

Code:
Sub CLOSEIT()
'
'
    Workbooks("Certain Workbook.xls").Activate
    ActiveWindow.Visible = True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
End Sub
 
Upvote 0
Hi again, it worked perfectly! Thanks. One more question: I want to avoid excel from asking if to save the "certain workbook", just need to close it without saving when hitting "send". I tried a few options to change the ActiveWorkbook.Save line but didn't succeeded. Can you help?

Thanks in advanced.
 
Upvote 0
Maybe:

Code:
Private Sub Worksheet_Activate() ' goes into the certain sheet code
    Workbooks.Open Filename:= _
        "D:\PATH\Certain Workbook.xls"
    ActiveWindow.Visible = False
End Sub

Call the below in the SEND buttons click/doubleclick event

Code:
Sub CLOSEIT()
'
'
    Workbooks("Certain Workbook.xls").Activate
    ActiveWindow.Visible = True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
End Sub

In the CLOSEIT Sub, insert the Application.DisplayAlerts = False code as the first line. In its final state it should look something like this....
Code:
Sub CLOSEIT()
'
'
    Application.DisplayAlerts = False
    Workbooks("Certain Workbook.xls").Activate
    ActiveWindow.Visible = True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
End Sub
 
Last edited:
Upvote 0
In the CLOSEIT Sub, insert the following code
Code:
Application.DisplayAlerts = False

This will only not show the dialog box but the workbook will save.

So instead of
Code:
ThisWorkbook.Save
ThisWorkbook.Close

Substitue this whole code with
Code:
ThisWorkbook.Close SaveChanges:=False
<CODE>
</CODE>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top