VBA code to quit excel workbook from command button on User Form

[ProudHook]

Hi Everyone

I have created a userform for a financial model that contains a disclaimer. Whilst the userform (disclaimer) is active the excel workbook is not visible.

At the bottom of the userform you can either accept the terms and conditions or not accept.

When selecting not accept the intent is that the disclaimer will disappear and the workbook will not appear or be visible and close down in the background. When you click accept a new userform appears to enter a password.

When the do not accept button is clicked the disclaimer disappears and the workbook does not appear or become visible as expected however, when I click on the workbook to try and open it again the work book opens automatically without the disclaimer showing.

The VBA code I have written for the "do not accept" command button is as follows:

Private Sub CommandButton1_Click()


Unload Me
ActiveWorkbook.Close True
Application.Quit


End Sub

-------------------------------------------------


Private Sub CommandButton2_Click()


UserForm2.Show


End Sub

----------------------------------------



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Unload Me
ActiveWorkbook.Close True
Application.Quit


End Sub

Can anyone advise what I have wrong in my code? It would appear that when the button is clicked it is not showing the workbook but it is opening somehow in the background so that when I click to open the workbook again it is not launching the disclaimer. Any help would be appreciated.

Regards

Doug

 

[igold]

Would this work..

In the "This Workbook" module in the Workbook_Open event, place some code similar to this. You may want to hide the sheets that have your financial models on or just activate a blank sheet

Private Sub Workbook_Open()


    Sheets(1).Visible = False
    Sheets(2).Visible = False
    UserForm1.Show
    
End Sub

and then on your form with the Accept and Not accept buttons add some code similar to this

Private Sub CommandButton2_Click()


    'Accept
    Unload Me
    Sheets(1).Visible = True
    Sheets(2).Visible = True
End Sub


Private Sub CommandButton1_Click()


    'Not Accept
    Unload Me
    ActiveWorkbook.Close True
    Application.Quit
    
End Sub

 

[ProudHook]

Hi thanks for your reply

I have tried changing the code to reflect the above but it is now coming up with an error on opening of the work book and highlights the following:

Sheets(2).Visible = False

The workbook sheets are listed as 2 and 3 in the Project pane.

Sorry I am new to VBA so may not have understood your recommendations correctly. Do you know what I am doing wrong? Is there more information that you need.

Code is now:

Workbook:

Private Sub Workbook_Open()
Sheets("Annual").Visible = False
Sheets("Monthly").Visible = False
UserForm1.Show

End Sub

Command Button

Private Sub CommandButton1_Click()
'Reject & Close
Unload Me
ActiveWorkbook.Close True
Application.Quit
End Sub


Private Sub CommandButton2_Click()
'Accept
Unload Me
Sheets(2).Visible = True
Sheets(3).Visible = True

End Sub

Thanks again

Doug

 

[ProudHook]

Apologies workbook code as follows:

Private Sub Workbook_Open()
Sheets(2).Visible = False
Sheets(3).Visible = False
UserForm1.Show​

 

[igold]

I was trying to give you an example of what you might want to do. You do not have to hide any worksheets, it was a matter of what you wanted your end user to see in the background behind the form. I am guessing that your error is coming from that you do not have a Sheet2 in your workbook.

The idea was that you should place your code (to open the userform) in the Workbook Open event.

If you do not understand, no problem. Just reply back and we can re-visit this, with better explanations.