POP - UP Window on Excel file start

[loooki84]

I have created Excel files with some tools and now I am trying to apply terms and conditions of using this excel files. Ideally I am looking for a pop-up window which will be poping-up every time a potential user will try to open this file with the button to accept the terms and with the other button to cancel which will close whole excel sheet. I tried to do this with user form but the problem is that the terms and conditions which I want to use are very long (4 pages A4). Ideally the text of this terms and conditions will be feed from the first tab in this excel sheet - which will be locked for changes.


Anybody any ideas?

[Paul_Hossler]

Simple way

T&Cs are on hidden WS

This is in a userform code sheet, and Thisworkbook Open will .Show it

Option Explicit
Private Sub cbAccept_Click()
    Call MsgBox("OK, you may proceed", vbInformation + vbOKOnly, "Terms and Conditions")
    
    Me.Hide
    Unload Me
End Sub
Private Sub cbNoThanks_Click()
    Call MsgBox("OK, you had your chance", vbCritical + vbOKOnly, "Terms and Conditions")
    
    Me.Hide
    Unload Me
    
    'thisworkbook.close (false)
End Sub
Private Sub UserForm_Initialize()
    With Me
        .tbTC.MultiLine = True
        .tbTC.ScrollBars = fmScrollBarsVertical
        .tbTC.SetFocus
        .tbTC.IntegralHeight = True
        .tbTC.Value = Join(Application.WorksheetFunction.Transpose(TC.Cells(1, 1).CurrentRegion.Columns(1)), vbLf)
        .tbTC.CurLine = 0
    
        .cbAccept.Enabled = True
        .cbNoThanks.Enabled = True
        
    End With
End Sub

[loooki84]

Cool, thank you. But now if potential user will not enable macros, he will still be able to use the file and will not see the terms and conditions. Also if he press the X button on the top corner it will close the pop-up window and he will still be able to use the file, and if he press the no thanks button then it doesn't close the whole file. and where I can put the text of terms and conditions?

cheers!

[Paul_Hossler]

if he press the no thanks button then it doesn't close the whole file. and where I can put the text of terms and conditions?

Uncomment

'thisworkbook.close (false)

T&C's are on a hidden worksheet - unhide it


Add this to the userform code sheet to disable the [X]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then Cancel = True
End Sub

There's examples around to force macros to be enabled. Here's one on VBAExpress

http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

[loooki84]

Thank you very much!! one additional can the text of these terms and conditions be feed from text box which will be in the first tab (hidden one) of the workbook?

[Paul_Hossler]

There's a text box on the hidden sheet named "TermsAndConditions"

Private Sub UserForm_Initialize()
    With Me
        .tbTC.MultiLine = True
        .tbTC.ScrollBars = fmScrollBarsVertical
        .tbTC.SetFocus
        .tbTC.IntegralHeight = True
        .tbTC.Value = TC.TextBoxes("TermsAndConditions").Text
        .tbTC.CurLine = 0
    
        .cbAccept.Enabled = True
        .cbNoThanks.Enabled = True
        
    End With
End Sub

[loooki84]

Wow, Thanks a lot. everything works perfectly. One more question. Is there any chance to keep the original formatting of the text? and also is there any chance to use a mouse scroll in the pop-up window?

[Paul_Hossler]

I don't know of anyway to copy the formatting from the hidden text box