Pop Up Forms

T

TBD

Hi all,

I have created a form and saved it as a template in my templates folder.
However, I want to have this form automatically "pop-up" when I open the
specific Excel spreadsheet.

Its relatively easy doing this in Access, so I'm presuming Excel can't be
whole lot more difficult??? The form is nothing more complicated then a text
box, giving some basic instructions on entering data into the spreadsheet for
users.

Any help you could give would be much appreciated!!

TBD
 
J

JulieD

Hi

not sure what you mean by 'created a form' - do you mean you've edited a
worksheet to look like a form or you've created a userform in the VBE window
(insert / userform)

secondly, not sure what you mean by you've saved it as a template in your
templates folder ....

if you want the "form" to appear when you open a specific workbook why don't
you (if it's a worksheet looking like a form) insert it into the workbook
and code the Workbook_Open code to always go to that sheet. If it's a
userform, i'm not sure how you can save it as a template as it is a VBE
object which lives within VBE. Assuming however, that it is in the workbook
that you want it to display in, you can code the following in the
Workbook_Open event -
userform1.show

Hope this helps
Cheers
JulieD
 
T

TBD

Hi Julie,

Sorry, but I think I'm trying to confuse matters. Basically, I have a macro
that contains a number of input boxes. I would like a text information box
to display itself before these Input boxes.

I know this shouldn't be difficult but I've copied the macro and I'm not
particularly great with Excel Visual Basic!

Thanks in advance for any help you can give! My macro is below:

' Macro written by Tom Hannah on 27th January 2005
' All notes appear in green
'***************************************************************************
'Specify Visual Basic Options
Option Explicit 'must declare all variable
Option Compare Text 'Not case sensitive
Option Base 1 'declare lower bound of array to 1
'_______________________________________________________
'Defining Varianble
Dim names As String
Dim emp As String
Dim cost As String
Dim ord As String
'_____________________________________________________
'Declare Property
Property Get pMyPath()
pMyPath = ThisWorkbook.Path 'define path of this module
End Property

'********************************************************************************

Sub tomsmacro()
Sheets("Form").Select
Range("b3").Select
names = InputBox("Please enter your name", "Employee Name",
Range("b3").Value)
ActiveCell.Value = names
Range("b6").Select
emp = InputBox("Please enter Vehicle(s) Booked", "Vehicle",
Range("b6").Value)
ActiveCell.Value = emp
Range("b9").Select
cost = InputBox("Please enter the dates of Vehicle Booking", "Dates",
Range("b9").Value)
ActiveCell.Value = cost
Range("b12").Select
ord = InputBox("Please enter your specific prep requests (if any)",
"Prep Requests", Range("b12").Value)
ActiveCell.Value = ord
End Sub
 
J

JulieD

Hi TBD

makes a bit more sense now - but your "text information box" where is that
living currently? also, does this macro run for one workbook or more than
one workbok (ie is it stored in a module in a workbook or is it stored in
personal? and what event fires it now?

Cheers
JulieD
 
T

TBD

Hi Julie,

The macro runs for only one workbook. I haven't created the text
information box yet, that is what I was originally trying to do by creating a
form.

The macro is going to be called when I open the workbook (i.e Auto_Open).

I appreciate your helps on this!!

TBD
 
J

JulieD

Hi TBD

okay, got you now :) the way i would do it is
1) in the workbook press the Alt & F11 key to go into the VBE window
2) choose insert / userform
3) add a label to the userform (click on the label control on the toolbox,
click on the userform)
4) type your text message
5) add a command button to the user form (click on the command button
control, click on the userform)
6) click on the command button and then in the properties window (view /
properties window if you can't see it) in the NAME field type cmdOK and in
the caption field type OK and accelerator key type O
7) double click on the command button and type in the sub
Unload me

now in your original macro type
userform1.show

between
and test

Hope this helps
Cheers
JulieD


TBD said:
Hi Julie,

The macro runs for only one workbook. I haven't created the text
information box yet, that is what I was originally trying to do by
creating a
form.

The macro is going to be called when I open the workbook (i.e Auto_Open).

I appreciate your helps on this!!

TBD
Click to expand...
---- snip
 
T

TBD

Thats fantastic Julie, thank you so much for your help!

It works an absolute treat now.

Thanks again!

TBD
 
C

cdb

Would a Message Box not do the same thing??

JulieD said:
Hi TBD

okay, got you now :) the way i would do it is
1) in the workbook press the Alt & F11 key to go into the VBE window
2) choose insert / userform
3) add a label to the userform (click on the label control on the toolbox,
click on the userform)
4) type your text message
5) add a command button to the user form (click on the command button
control, click on the userform)
6) click on the command button and then in the properties window (view /
properties window if you can't see it) in the NAME field type cmdOK and in
the caption field type OK and accelerator key type O
7) double click on the command button and type in the sub
Unload me

now in your original macro type
userform1.show

between

and test

Hope this helps
Cheers
JulieD



---- snip
Click to expand...
 
J

JulieD

you're welcome and thanks for the feedback

now your next challenge is to create a userform which has all of your
questions on it, rather than using the inputboxes :)

Cheers
JulieD
 
J

JulieD

Hi

yes and no, you have more control over the formatting of a userform (i know
i didn't mention formatting, but i thought TBD could work that one out on
his own). i guess it depends on how much text you have and what you want
the output to look like. I went with the userform because right back in the
initial post TBD mentioned "forms" ....

Cheers
JulieD
 
Top