Closing and Opening Workbooks using a Command Button

Question

Hi,

I have a Button in a Workbook I assigned a macro to.  I'm trying to get the following to happen:

Close Active Workbook and Save

then

Open another Workbook located in (C:MyDocuments\ExcelFiles\Workbook1)

I tried to write the above as simply as possible for you to understand.

The code I tried to write keeps hanging up.  I'm new to VBA.

Thank-you in advance,

Amy

Answer 1

Hi,

try this

in Active workbook

step1

in a regular module:

[Edit]

Sub macro1()

Dim wb1 As Workbook, wb2 As Workbook

Set wb1 = ThisWorkbook

Application.DisplayAlerts = False

Set wb2 = Workbooks.Open**(C:\MyDocuments\ExcelFiles\Workbook1.xlsx)  '<<<  change xlsx or xlsm**

wb1.Close True

Application.DisplayAlerts = True

End Sub

step2

double click on ThisWorkbook

and paste in...

Private Sub Workbook_Open()

Application.OnKey "^b", "macro1"  ' <<< ctrl+b

End Sub

step3

Save As with extension  xlsm (macros enabled)

close and re-open it

press Ctrl+b

Answer 2

Thank-you TasosK