I have created a small user form with 4 textboxes on it.
1. Text goes into the first two........which I want to transfer into cells
on the spreadsheet.....
2. The third is a currency figure, this figure has to be transferred to 3
cells on the spread sheet.
3. The Fourth cell is a random date cell 09/04/2005 12:34:56 is it possibl
to have this entered into the Footer using code.

Any advise would be appreciated, a link to a basic sample of the above would
be great if that was possible.
Thanks
Dermot

Here is a simple example

Private Sub CommandButton1_Click()
Sheets(1).Select
Range("A1").Value = TextBox1.Value
Range("A2").Value = TextBox2.Value
Range("A3").Value = Format(TextBox3.Value, "currency")
Range("A4").Value = Format(TextBox3.Value, "currency")
Range("A5").Value = Format(TextBox1.Value, "currency")


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value
End Sub

Thanks for the Code Steve
That worked great. One last question...I was experimenting with date and
time today but was unsucessful...please advise....

If I wanted to change the Footer time only and enter a manual date from the
UserForm.... would the following be correct?

ActiveSheet.Pagesetup.CentreFooter= Textbox4.Value [&Time]

Kind Regards
Dermto

"stevebriz" wrote:

Here is a simple example

Private Sub CommandButton1_Click()
Sheets(1).Select
Range("A1").Value = TextBox1.Value
Range("A2").Value = TextBox2.Value
Range("A3").Value = Format(TextBox3.Value, "currency")
Range("A4").Value = Format(TextBox3.Value, "currency")
Range("A5").Value = Format(TextBox1.Value, "currency")


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value
End Sub

Dermot wrote:
Thanks for the Code Steve
That worked great. One last question...I was experimenting with date and
time today but was unsucessful...please advise....

If I wanted to change the Footer time only and enter a manual date from the
UserForm.... would the following be correct?

ActiveSheet.Pagesetup.CentreFooter= Textbox4.Value [&Time]

Kind Regards
Dermto

Do you want the footer time to be the time you run everything else and do you want today's date?
For the date : if you don't want todays' date you can add a calendar to
the form and have the user click on the date.

Hi Steve
I overlooked to ask, using the code you provided say for Textbox1.....
How would I modify the range of cells if I wanted that data entered in say
three cells A1 F1 and M1.
Thanks in advance
Dermot

"stevebriz" wrote:

Here is a simple example

Private Sub CommandButton1_Click()
Sheets(1).Select
Range("A1").Value = TextBox1.Value
Range("A2").Value = TextBox2.Value
Range("A3").Value = Format(TextBox3.Value, "currency")
Range("A4").Value = Format(TextBox3.Value, "currency")
Range("A5").Value = Format(TextBox1.Value, "currency")


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value
End Sub

Dermot wrote:
Hi Steve
I overlooked to ask, using the code you provided say for Textbox1.....
How would I modify the range of cells if I wanted that data entered in say
three cells A1 F1 and M1.
Thanks in advance
Dermot

Like This:
Private Sub CommandButton1_Click()
Sheets(1).Select
Range("A1").Value = TextBox1.Value
Range ("F1").Value =Textbox1.Value
Range ("M1").Value =Textbox1.Value
Range("A2").Value = TextBox2.Value
Range("M1").Value = Format(TextBox3.Value, "currency")
Range("A4").Value = Format(TextBox3.Value, "currency")
Range("A5").Value = Format(TextBox1.Value, "currency")


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value
End Sub

Thanks for this Steve
So they are individually added via the textbox 1...nice when you know how,
cheers.
Don't beat me up.....could you have a look at the other questions I posted?
Thanks in advance
Dermot


"stevebriz" wrote:


Dermot wrote:
Hi Steve
I overlooked to ask, using the code you provided say for Textbox1.....
How would I modify the range of cells if I wanted that data entered in say
three cells A1 F1 and M1.
Thanks in advance
Dermot

Like This:
Private Sub CommandButton1_Click()
Sheets(1).Select
Range("A1").Value = TextBox1.Value
Range ("F1").Value =Textbox1.Value
Range ("M1").Value =Textbox1.Value
Range("A2").Value = TextBox2.Value
Range("M1").Value = Format(TextBox3.Value, "currency")
Range("A4").Value = Format(TextBox3.Value, "currency")
Range("A5").Value = Format(TextBox1.Value, "currency")


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value
End Sub

Steve, Please be patient,
I realise I have posted another three posting, but have been struggling to
resolve these other issues......
I know you have already answered my original question but for the continuity
of the functionality of the form I have been experimenting with can you
explain a little further.....
1. How am I meant to get the form to display on my worksheet, without
entering the VBA environment?
2. Can you supply code for Print, and Save?
3. Is there any other basic function you would include in this exercise that
i may have overlooked.?

Thanks

Dermot
"stevebriz" wrote:

Here is a simple example

Private Sub CommandButton1_Click()
Sheets(1).Select
Range("A1").Value = TextBox1.Value
Range("A2").Value = TextBox2.Value
Range("A3").Value = Format(TextBox3.Value, "currency")
Range("A4").Value = Format(TextBox3.Value, "currency")
Range("A5").Value = Format(TextBox1.Value, "currency")


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value
End Sub

Dermot wrote:
Steve, Please be patient,
I realise I have posted another three posting, but have been struggling to
resolve these other issues......
I know you have already answered my original question but for the continuity
of the functionality of the form I have been experimenting with can you
explain a little further.....
1. How am I meant to get the form to display on my worksheet, without
entering the VBA environment?
2. Can you supply code for Print, and Save?
3. Is there any other basic function you would include in this exercise that
i may have overlooked.?

I will reply first on you Q1\ ...
there are few ways to bring the form up.

It can load up as soon as you open the workbook or You can add command
button on the spreadsheet that when clicked will open the userform.. Or
you can add to the excel menu like under tools? Which one would you
like?
For a the button you add to the spreadsheet. just add in the
command1_click
userform1.show ( this assuming your userform is this name)


Q2a/ sorry to answer a question with an answer.... you need to tell me
what range on the spreadsheet you want to print eg: sheet number and
Cells A1 to G10...
Q2b/For the save do you just want a save button on the userform to
save the workbook?
Q3/ this depends on what you want the user to be able to do...If you
give me and idea of what and who this project is intended for then I
might be able to throw you some ideas.

Hi Steve
The spread sheet is a little contrived to get a better understanding of how
to incoporate a user form.
In this situation it is a worksheet with client and finance data that is
entered and then printed off. The user form makes it simple enough to enter
the data without jumping all over the place.
1. The print Area is A1:T12
2. I would like to be able to save the Worksheet under the Clients name in
Last Name First order (Although it is in First name First on the worksheet.
3. To Quote you Steve
It can load up as soon as you open the workbook or You can add command
button on the spreadsheet that when clicked will open the userform.. Or
you can add to the excel menu like under tools? Which one would you
like?
For a the button you add to the spreadsheet. just add in the
command1_click
userform1.show ( this assuming your userform is this name)
4.Question
What did you mean by "add to the excel menu like under tools?
It would be nice to know how to do both as I can see the differnt methods
could be used in different circumstance.
I have noticed in other postings that some use a "hide" statement in their
code....but didn't really understand how this would be used...how would the
user form be unhidden?
My thought on entering the data via the user form....
Type data in text boxes, Click Enter cmd button, Print, Save as,... Next
sheet
I hope I explained this okay.
Lokiing forward to your response.
Cheers
Dermot


"stevebriz" wrote:


Dermot wrote:
Steve, Please be patient,
I realise I have posted another three posting, but have been struggling to
resolve these other issues......
I know you have already answered my original question but for the continuity
of the functionality of the form I have been experimenting with can you
explain a little further.....
1. How am I meant to get the form to display on my worksheet, without
entering the VBA environment?
2. Can you supply code for Print, and Save?
3. Is there any other basic function you would include in this exercise that
i may have overlooked.?

I will reply first on you Q1\ ...
there are few ways to bring the form up.

It can load up as soon as you open the workbook or You can add command
button on the spreadsheet that when clicked will open the userform.. Or
you can add to the excel menu like under tools? Which one would you
like?
For a the button you add to the spreadsheet. just add in the
command1_click
userform1.show ( this assuming your userform is this name)


Q2a/ sorry to answer a question with an answer.... you need to tell me
what range on the spreadsheet you want to print eg: sheet number and
Cells A1 to G10...
Q2b/For the save do you just want a save button on the userform to
save the workbook?
Q3/ this depends on what you want the user to be able to do...If you
give me and idea of what and who this project is intended for then I
might be able to throw you some ideas.

ok here goes...here is some code I put together for you for various
things you need.
If you have any question then let me know.


HINT:
Click on you userform and go look in the properties
Set the Show Modal to be false.
DO THIS FIRST!
-----------------------------------------------------------------------------
PRINT

Add a cmd button to your form
In the properties change the Caption to Print
and double click on it and paste this in:

Sheet1.PageSetup.PrintArea = "A1:T12"
Application.Dialogs(xlDialogPrint).Show
---------------------------------------------------------------------------
PRINT PREVIEW
You need the Userform to be set as ShowModal as False or you will get a
lock up
Add a cmd button to your form
In the properties change the Caption to Print Preview
and double click on it and paste this in:

Sheet1.PageSetup.PrintArea = "A1:T12"
Application.Dialogs(xlDialogPrintPreview).Show
-------------------------------------------------------------------------------------
SAVE AS
Add a cmd button to your form
In the properties change the Caption to Save As
and double click on it and paste this in:

Application.Dialogs(xlDialogSaveAs).Show

----------------------------------------------------------------------------------
NEXT SHEET
Firstly you need to remember that the code below Isent earlier is
written for sheet 1
To change this to work with the current sheet you need to change
sheet1.select to Activesheet.select
eg:
Private Sub CommandButton1_Click()
Activesheet.select
Range("A1").Value = TextBox1.Value
Range("A2").Value = TextBox2.Value
Range("A3").Value = Format(TextBox3.Value, "currency")
Range("A4").Value = Format(TextBox3.Value, "currency")
Range("A5").Value = Format(TextBox1.Value, "currency")
ActiveSheet.PageSetup.LeftFooter = TextBox4.Value
End Sub
.................................................. .....................
Ok...now to put the NEXT SHEET Button in.
Add a cmd button to your form
In the properties change the Caption to Next Sheet
and double click on it and paste this in:

If Sheets(ActiveSheet.Name).Index < Worksheets.Count Then
Sheets((Sheets(ActiveSheet.Name).Index) + 1).Activate
End If
ActiveSheet.Select


----------------------------------------------------------------------------------------------------------------
OPEN FORM FROM MENU
TO add an item to the menu bar under tools to open the userform1

I called the description I added to the Tools menu was "My Excel Form"
( you can change this to what every you like.)

In vb editor double click This workbook and paste the code below:


Private Sub Workbook_Open()
Dim item As CommandBarControl
On Error Resume Next
Application.CommandBars(1).Controls("Tools").Contr ols("MY EXCEL
FORM").Delete
Set item = Application.CommandBars(1).Controls("Tools").Contr ols.Add
item.Caption = "&MY EXCEL FORM"
item.BeginGroup = True
item.OnAction = "MYEXCELSHOW"
item.BeginGroup = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars(1).Controls("Tools").Contr ols("&MY EXCEL
FORM").Delete
End Sub


.................................................. ..................................

Then you will need to insert a module( eg: module 1) and paste the
code below:

Sub MYEXCELSHOW()
UserForm1.Show
End Sub
--------------------------------------------------------------------------------------------------------------------
AUTO OPEN FORM

To have the form come up automatically when you open the xls then
paste the following in a module (eg: module 1)

Sub auto_open()

Call MYEXCELSHOW

End Sub

-----------------------------------------------------------------------------------------------------------------
CLOSE FORM
Add a cmd button to your form
In the properties change the Caption to Close
and double click on it and paste this in:

Unload me

For the time in the footer
eg:
ActiveSheet.PageSetup.LeftFooter = TextBox4.Value & " " & Time

Hi Steve,
I am just back from my hols in the Spanish sun and want to thankyou for you
help before I went away.
I realise that there has now been 27 postings, but wondered if you could
advise me if there would be any error handling code you might consider as
standard that would be worth me considering for this userform I have created
through out these postings.
Cheers and thanks

Dermot

"stevebriz" wrote:

For the time in the footer
eg:
ActiveSheet.PageSetup.LeftFooter = TextBox4.Value & " " & Time

The error handling really depends on what errors you are getting in
each part of the code.
In any coding the goal is to handle all the exceptiosns before they
become a error message
If you post the code and what action cause the error, and the error.
I can help you with some error handling....or maybe prevent the error
occuring.
Steve

Dermot wrote:
Hi Steve,
I am just back from my hols in the Spanish sun and want to thankyou for you
help before I went away.
I realise that there has now been 27 postings, but wondered if you could
advise me if there would be any error handling code you might consider as
standard that would be worth me considering for this userform I have created
through out these postings.
Cheers and thanks

Dermot

"stevebriz" wrote:

For the time in the footer
eg:
ActiveSheet.PageSetup.LeftFooter = TextBox4.Value & " " & Time

Hi Steve,
Thanks for the reply.

I don't actually get any error messages.
It may be that this UserForm is a poor example to use to explain a
fundamental approach to error handling.................
From looking at code from other random code samples, I noticed that error
handling always seems to be included and assumed from this that I was
overlooking something...............
To quote your last posting................
"In any coding the goal is to handle all the exceptions before they
become a error message"..........................can you explain this a
little further........is there a methology behind determining if error
handling.

Any advise that will help me approach coding correctly would be
appreciated......somthing to build from.

Thanks
Dermot




"stevebriz" wrote:

The error handling really depends on what errors you are getting in
each part of the code.
In any coding the goal is to handle all the exceptiosns before they
become a error message
If you post the code and what action cause the error, and the error.
I can help you with some error handling....or maybe prevent the error
occuring.
Steve

Dermot wrote:
Hi Steve,
I am just back from my hols in the Spanish sun and want to thankyou for you
help before I went away.
I realise that there has now been 27 postings, but wondered if you could
advise me if there would be any error handling code you might consider as
standard that would be worth me considering for this userform I have created
through out these postings.
Cheers and thanks

Dermot

"stevebriz" wrote:

For the time in the footer
eg:
ActiveSheet.PageSetup.LeftFooter = TextBox4.Value & " " & Time