PDA

View Full Version : POP - UP Window on Excel file start



loooki84
07-07-2016, 03:41 AM
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
07-07-2016, 06:48 AM
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
07-07-2016, 07:05 AM
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
07-07-2016, 07:18 AM
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
07-07-2016, 07:25 AM
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
07-07-2016, 09:22 AM
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
07-07-2016, 10:02 AM
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
07-07-2016, 11:24 AM
I don't know of anyway to copy the formatting from the hidden text box