Help with workbook open code

[CV899000]

Hello, I have this code that runs when I open my workbook.

The problem I have is that I have a macro that updates my exchange rates and I prompt a message when I open the workbook asking if I want to update or not.

The sheet where the exchange rates are in, is hidden, so I have to unhide the sheet when updating and hide it again when done.

This works fine when I say yes to the update.

If I say no, then I exit the sub and the exchange rate sheet is not hidden.
How do I ensure that it is hidden when I press YES and NO?

Here is the code:

Private Sub Workbook_Open()
Worksheets("Copied Prices").Cells.Delete
Worksheets("Shock Compliance Information").Activate
Worksheets("Prices").Range("B5").Value = ""
Application.ScreenUpdating = False
Sheets("Exchange rates").Visible = True
If MsgBox("Internet connection needed to update exchange rates. Update now?", vbYesNo) = vbNo Then Exit Sub
Sheets("Exchange rates").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Exchange rates").Visible = False
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[CV899000]

I solve it.

I will input the new workbook_beforeclose if anybody in the future needs it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Macros disabled").Visible = True
Sheets("Shock Compliance Information").Visible = xlVeryHidden
Sheets("Prices").Visible = xlVeryHidden
Sheets("Copied Prices").Visible = xlVeryHidden
For Each Worksheet In Worksheets
If Worksheet.Name <> "Macros disabled" Then Worksheet.Visible = xlSheetVeryHidden
Next
End Sub

 

[S_Wish]

I prefer that usage of sheet clearing

Worksheets("Copied Prices").UsedRange.Clear

why you activate that sheet now?

Worksheets("Shock Compliance Information").Activate

not before you use

Range("A1").Select

you cant select a sheet)
i suppose you need something like

Sheets("Exchange rates").UsedRange.Select

 

[CV899000]

So here is my update,

I ended up not using the messagebox when I open the wordbook.

I created a macro button to update the exchange rates when required.

My new workbook code looks like this, and it works just as I need it to.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Macros disabled").Visible = True
Sheets("Shock Compliance Information").Visible = xlVeryHidden
Sheets("Prices").Visible = xlVeryHidden
Sheets("Copied Prices").Visible = xlVeryHidden
For Each Worksheet In Worksheets
If Worksheet.Name <> "Macros disabled" Then Worksheet.Visible = xlSheetVeryHidden
Next
End Sub
Private Sub Workbook_Open()
Sheets("Shock Compliance Information").Visible = True
Worksheets("Shock Compliance Information").Activate
Sheets("Prices").Visible = True
Sheets("Copied Prices").Visible = True
Sheets("Macros disabled").Visible = xlVeryHidden
Worksheets("Copied Prices").Cells.Delete
Worksheets("Prices").Range("B5").Value = ""
End Sub

Many many thanks for your help S_wish!