Pop Up Screens

  • Thread starter Thread starter Darren O'Connell
  • Start date Start date
D

Darren O'Connell

Hi there,

I've put together a small Excel program that calculates
foreign exchange rates. I would like to add a disclaimer
but rather than simply adding a text box to my worksheet
(thereby increasing the size of the worksheet) I would
like to add a hyperlink that when pressed opens a small
pop up screen showing the text of the disclaimer. When the
mouse is cliked outside the pop up box it disappears. Is
this possible and if so how is it done?

Cheers,
D
 
A quite simple way would be to have your logo or whatever
in a cell or range, then have the disclaimer in a cell
comment.
An alternative might be to create a Userform. You can add
a timer so that it unloads itself after say 1 second.

You could use the sheet's SelectionChange to test if the
logo is selected then automatically open the form.
Say you logo cell/s are range named 'MyLogo' set the
sheets' event thusly:
Private Sub Worksheet_SelectionChange(ByVal Target _
As Range)
If Not Intersect(Target, _
ThisWorkbook.Names("MyLogo").RefersToRange) _
Is Nothing Then
frmDisclaimer.Show
End If
End Sub

add a userform, name it 'frmDisclaimer' and add this
Private Sub UserForm_Activate()
Dim T As Double
T = Timer + 1
Do
DoEvents
Loop Until Timer > T
Unload Me
End Sub

Now when you click the range, the form will show for one
second

Patrick Molloy
Microsoft Excel MVP
 
Darren,

You could biuld a small html page that you ship with your Excel program.
Then add the hyperlink that opens this file to show your disclaimer. The
problem with that type of process is that you are now forced to distribute,
and manage, more than just the single Excel file.

My preference would be to build a small userform in the Excel file that
displays the disclaimer info. You would simply call the show method of the
form to display it to the user. You could do something as simple as place a
textbox that is locked (user can not change your text), along with a button
for closing the form.

To test the size difference I created a new (empty) workbook on my hard
drive. I then saved it as a new file adding the user form mentioned above.
Not having a disclamier file to load I pasted an entire class into the text
property of a textbox then expanded the textbox so it covered the entire
form surface. The size difference between the two are listed below;

Empty workbook: 14 kb
Workbook with user form: 26 kb

As you can see, the addition of the user form really did not add much in the
way of size to the empty workbook.

Goodluck
 
Darren O'Connell said:
Hi there,

I've put together a small Excel program that calculates
foreign exchange rates. I would like to add a disclaimer
but rather than simply adding a text box to my worksheet
(thereby increasing the size of the worksheet) I would
like to add a hyperlink that when pressed opens a small
pop up screen showing the text of the disclaimer. When the
mouse is cliked outside the pop up box it disappears. Is
this possible and if so how is it done?

Cheers,
D
Click to expand...

The simplest way is to put your disclaimer on a sheet of the
workbook as either a text box image control and add a small
piece of VBA code to the ThisWorkbook.Workbook_Open()
event so that the sheet is always displayed first when the
workbook is opened.

Alternatively you can add your disclaimer to a user form and
use the Workbook_Open event to display it and add
the following code to the userform to close after a set delay

Private Sub UserForm_Activate()
Dim PauseTime, Start, Finish, TotalTime
PauseTime = 2 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Needed for form to display correctly
Loop

Me.Hide
End Sub

Note that the Timer function returns the no of seconds
elapsed since midnight so the form would be displayed for
2 seconds

Keith
 
Back
Top