Disclaimer in VBA

[jraymond1984]

Hi

I have the following VBA code for a disclaimer in my spreadsheet. At the moment what it does is an icon box pops up when you open the spreadsheet, and if you click 'Yes' all the other tabs to the spreadsheet open, if you click 'No' then you cannot access the spreadsheet.

What I am trying to do instead is have my spreadsheet such that when you open the spreadsheet, only the disclaimer page opens, and you have to click on a button (i.e. linked to the macro) which is essentially the 'Accept' the disclaimer, following which all the remaining tabs on the spreadsheet will now appear.

If you are able to please help me change the code, it would be much appreciated.

Many thanks in advance,

Jonathan



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Integer, j As Integer

j = ActiveWorkbook.Sheets.Count

For i = 2 To j
ActiveWorkbook.Sheets(i).Visible = xlSheetVeryHidden
Next i

ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()
Dim i As Integer, j As Integer

j = ActiveWorkbook.Sheets.Count

MSG1 = MsgBox("Please click yes to confirm you adhere to the Disclaimer", vbYesNo, "Disclaimer")

If MSG1 = vbYes Then
For i = 2 To j
ActiveWorkbook.Sheets(i).Visible = True
Next i
Else
ActiveWorkbook.Close
End If

End Sub

 

[kpark91]

Where is your disclaimer on? Is it on your Sheet1?

If so, go to Developer Ribbon -> Insert -> ActiveX Control Command Button ->
Draw on the worksheet -> Double click the button

copy + paste this code in

Dim i As Integer
For i = 2 To ActiveWorkbook.Sheets.Count
    ActiveWorkbook.Sheets(i).Visible = True
Next i

Then Replace your caption of your commandbutton to Accept and play with the properties of it if you want.

I hope this helped.

 

[jraymond1984]

Perfect!

Thank you very much, both for your assistance and your incredibly fast response!!!