Does anyone know how to do this?
 

Hi, i'm a new user of Macros/VBA and altho i'm slowly improving (mainly
thanks to theadvice on this forum!) i find myself stuck once again!

I want to create a macro that will open a user form which instructs the
user to enter their name, the date and the subject in three seperate
boxes.

On clicking ok, a second form pops up asking the users to click on the
macro they wish to run, eg macro 1, macro 2, macro 3 etc (these macros
are already written and working fine!)

When these macros are run they will bring up a template (which i have
created using macro recorder) - I would like the template to show the
information the user entered in the first form - eg, the name will be
entered in a1, the date in a2, the subject in a3.

Ideally i would like the user to be able to select as many of the
macros they wish to run and when they click ok have each of the
selected macros run in turn (as ultimately the aim is to allow the user
to create a customised workbook from a range of recorded templates.)

Is this, or something like this, possible? Can anyone explain how i
should do it or point me in the right direction? Your help is much
appreciated.


---
Message posted from http://www.ExcelForum.com/

Does anyone know how to do this?
 

I got started with forms w/ a book called excel 2000 vba. I have found
it most useful as a vba reference when learning from scratch. This book
also has some code in ith with working forms that you can modify.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Does anyone know how to do this?
 

Thanks for the recommendation - i have a couple of vba books but they're
very over my head at the moment so i'll have a look for the one you
recommend.

In the meantime if anyone else can suggest a possible solution it would
be much appreciated (especialy as these vba books are getting
expensive!!!!!)

Once again, any thanks


---
Message posted from http://www.ExcelForum.com/

Does anyone know how to do this?
 

Books are useful, but with the development of the Internet, you can
find the answers to most questions online. You could also avail
yourself the resources of the local library. Anyway, ....

Everything that you are requesting sounds doable.

Here are some resources provided by Tom Ogilvy in a response to a
prior posting :

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic
for
Applications macros that show you how to take advantage of the
capabilities
of UserForms and use each of the ActiveX controls that are available
for
UserForms

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data

John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I
http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartI.asp
Part II
http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartII.asp


HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Hi, i'm a new user of Macros/VBA and altho i'm slowly improving (mainly
thanks to theadvice on this forum!) i find myself stuck once again!

Snipped

Is this, or something like this, possible? Can anyone explain how i
should do it or point me in the right direction? Your help is much
appreciated.

Does anyone know how to do this?
 

This appears to simply be a matter of program control. It
is definetly something that is possible. As I understand
it, you want Form1 to contain data that is accessible
after Form1 is no longer visible.

So, you create Form1 to have the three fields you desired;
name, date, and subject. The form also has an OK button
on it. Probably a Cancel button would be a good idea.
Form1 should have public accessor functions to get to the
data that you have entered in form1.

Form2 would have your list of macros, either as a list
that can be multiselected (more than I care to go in at
the moment) or just a set of radio buttons/check boxes
that can be selected, multi selectable however you decide
to do it, given what you want.


Code in Form1, For example:

Public function GetName() as string
GetName = txtNameBox.text
end function

The action for the OK button would be something like

private sub OK_Click()
Form1.hide
load Form2 'This gets your macro form in memory AND
'keeps the data from Form1 in memory.
'Now you can set anything for Form2 that you wish.
Form2.txtNameBox = Form1.GetName ' See function above
'that is part of the Form1 code. It sets the
'text box on form2 that will hold the persons name.
'if you want to display that on Form2.
Form2.Show 'This displays the Form2
'Now everything that Form2 does starts and when it is
' done it will return to the next line of code here.
' Remember, that if you are using this for some other
' purpose, that you do not want to get your program
' caught into some infinite loop or to end the
' program without properly "closing" things out.

Form2.Hide 'this hides Form2 following Form2 program
'completion.
'When you're done with Form2 and all of it's data then
'Simply unload it.

unload Form2

end sub

Now within Form2.

Say there is a RUN or OK button that will execute the
macros you have written and discovered work fine, then.
The following sub routine considers that you have used
check boxes for your Macro "selection" and called them
chkMacro1, chkMacro2, and chkMacro3, respectively.
the action(s) for the RUN control would be:

Private sub RUN_click()
If chkMacro1 = true then
'Run Macro1 here
End if
if chkMacro2 = true then
'Run Macro2 here
end if
if chkMacro3 = true then
'Run Macro3 here
end if

Form2.hide 'This will return control back to whatever
'Called Form2 to be shown.
end sub

Now the other issue you were wanting to know about was if
you could use the data entered in Form1 in each or one or
more of your macros.

For each macro that needs to use the data from Form1, just
pull the data from Form1 by using your accessor functions.
For example, if the name header for Macro1 is needed, then
within Macro1...

private (or public) sub (or function) madeUp()
NameTextBox = Form1.GetName()
end sub

That should get you off to a start of some sort. There
are other ways to do this, and this may not be the "best"
way, it depends on what kinds of things you have to
juggle. Is memory space a factor, is drive space a
factor, is speed a factor, could you get away with using
the registry, do you even want to go there, etc. etc.
etc.

-----Original Message-----
Hi, i'm a new user of Macros/VBA and altho i'm slowly
improving (mainly
thanks to theadvice on this forum!) i find myself stuck
once again!

I want to create a macro that will open a user form which
instructs the
user to enter their name, the date and the subject in
three seperate
boxes.

On clicking ok, a second form pops up asking the users to
click on the
macro they wish to run, eg macro 1, macro 2, macro 3 etc
(these macros
are already written and working fine!)

When these macros are run they will bring up a template
(which i have
created using macro recorder) - I would like the
template to show the
information the user entered in the first form - eg, the
name will be
entered in a1, the date in a2, the subject in a3.

Ideally i would like the user to be able to select as
many of the
macros they wish to run and when they click ok have each
of the
selected macros run in turn (as ultimately the aim is to
allow the user
to create a customised workbook from a range of recorded
templates.)

Is this, or something like this, possible? Can anyone
explain how i
should do it or point me in the right direction? Your
help is much
appreciated.


---
Message posted from http://www.ExcelForum.com/

.

Does anyone know how to do this?
 

Guys,

Thanks very much for your time and input on this problem - i am greatl
encouraged to learn that you believe it should be relatively simple t
achieve.

I will spend some time looking at your advice and trying to get them t
work for me.

In the meantime if anyone can offer any further advice it is, as ever
appreciated.

Com's interpretation that ideally an input form will allow the user t
enter information that will still be available after the userform i
closed and a multi-selection list of macros in the second box i
correct. In response to the questions posed memory is not an issue bu
i do not want to access the registry in any way(if it is possible t
avoid).

Many Thank

--
Message posted from http://www.ExcelForum.com

Does anyone know how to do this?
 

Perhaps it is a matter of personal preference, but
something = Userform1.Textbox1

will work just as well as using an accessor function (as long as userform1
is loaded - if it isn't, the accessor function wouldn't work anyway). To
me, the accessor function appears redundant.

Other than that, I don't know why you want other ideas - COM seems to have
laid it out pretty well. If you need more detail, the article links posted
should provide that.

--
Regards,
Tom Ogilvy

COM wrote in message
...
This appears to simply be a matter of program control. It
is definetly something that is possible. As I understand
it, you want Form1 to contain data that is accessible
after Form1 is no longer visible.

So, you create Form1 to have the three fields you desired;
name, date, and subject. The form also has an OK button
on it. Probably a Cancel button would be a good idea.
Form1 should have public accessor functions to get to the
data that you have entered in form1.

Form2 would have your list of macros, either as a list
that can be multiselected (more than I care to go in at
the moment) or just a set of radio buttons/check boxes
that can be selected, multi selectable however you decide
to do it, given what you want.


Code in Form1, For example:

Public function GetName() as string
GetName = txtNameBox.text
end function

The action for the OK button would be something like

private sub OK_Click()
Form1.hide
load Form2 'This gets your macro form in memory AND
'keeps the data from Form1 in memory.
'Now you can set anything for Form2 that you wish.
Form2.txtNameBox = Form1.GetName ' See function above
'that is part of the Form1 code. It sets the
'text box on form2 that will hold the persons name.
'if you want to display that on Form2.
Form2.Show 'This displays the Form2
'Now everything that Form2 does starts and when it is
' done it will return to the next line of code here.
' Remember, that if you are using this for some other
' purpose, that you do not want to get your program
' caught into some infinite loop or to end the
' program without properly "closing" things out.

Form2.Hide 'this hides Form2 following Form2 program
'completion.
'When you're done with Form2 and all of it's data then
'Simply unload it.

unload Form2

end sub

Now within Form2.

Say there is a RUN or OK button that will execute the
macros you have written and discovered work fine, then.
The following sub routine considers that you have used
check boxes for your Macro "selection" and called them
chkMacro1, chkMacro2, and chkMacro3, respectively.
the action(s) for the RUN control would be:

Private sub RUN_click()
If chkMacro1 = true then
'Run Macro1 here
End if
if chkMacro2 = true then
'Run Macro2 here
end if
if chkMacro3 = true then
'Run Macro3 here
end if

Form2.hide 'This will return control back to whatever
'Called Form2 to be shown.
end sub

Now the other issue you were wanting to know about was if
you could use the data entered in Form1 in each or one or
more of your macros.

For each macro that needs to use the data from Form1, just
pull the data from Form1 by using your accessor functions.
For example, if the name header for Macro1 is needed, then
within Macro1...

private (or public) sub (or function) madeUp()
NameTextBox = Form1.GetName()
end sub

That should get you off to a start of some sort. There
are other ways to do this, and this may not be the "best"
way, it depends on what kinds of things you have to
juggle. Is memory space a factor, is drive space a
factor, is speed a factor, could you get away with using
the registry, do you even want to go there, etc. etc.
etc.

-----Original Message-----
Hi, i'm a new user of Macros/VBA and altho i'm slowly
improving (mainly
thanks to theadvice on this forum!) i find myself stuck
once again!

I want to create a macro that will open a user form which
instructs the
user to enter their name, the date and the subject in
three seperate
boxes.

On clicking ok, a second form pops up asking the users to
click on the
macro they wish to run, eg macro 1, macro 2, macro 3 etc
(these macros
are already written and working fine!)

When these macros are run they will bring up a template
(which i have
created using macro recorder) - I would like the
template to show the
information the user entered in the first form - eg, the
name will be
entered in a1, the date in a2, the subject in a3.

Ideally i would like the user to be able to select as
many of the
macros they wish to run and when they click ok have each
of the
selected macros run in turn (as ultimately the aim is to
allow the user
to create a customised workbook from a range of recorded
templates.)

Is this, or something like this, possible? Can anyone
explain how i
should do it or point me in the right direction? Your
help is much
appreciated.


---
Message posted from http://www.ExcelForum.com/

.