Entering Data into a spreadsheet via a UserFrom Advise Please

[Guest]

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

 

[stevebriz]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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]

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

 

[stevebriz]

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.

 

[Guest]

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]

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.

 

[stevebriz]

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

 

[Guest]

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]

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").Controls("MY EXCEL
FORM").Delete
Set item = Application.CommandBars(1).Controls("Tools").Controls.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").Controls("&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

 

[Guest]

Thank you very much Steve,
It's good of you to put this together for me.
Vey much appreciated.
I'll let you know how I get on.

 

[Guest]

Hi Steve
After a little time I had all working except The Next Sheet.
I am not sure where the Two pieces of code go for this feature.....are they
both attached to the CommandButton_1Click()......
Also what can I expect to happen when I click the "Next Sheet"
button?........Are the text box data deleted for the next entries.

Today I was trying to work out how to incude Seconds in the footer....
For example....at the moment I get 12:45...........how do I get say 12.45:30
including the seconds?

 

[stevebriz]

put the next sheet on a cmd button as well ( like you did with the
others)

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

This will give you the seconds:


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value & " " & Format(Time,
"h:Nn:Ss AM/PM")

 

[stevebriz]

button?........Are the text box data deleted for the next entries.

You will go to the next sheet. and then if you click the neter button
it will enter data on the next sheet....
Caution ...did you make the change on I suggested form sheet1.select to
activesheet.select.
If you didn't the what every is type into the TB's then click enter
will end up on sheet 1 not the the activesheet.

other than no probs I can see from here.

 

[Guest]

Thank you very much again Steve for your time.
You have given me plenty food for though
Kind Regards
Dermot

 

[Guest]

Hi Steve,
thanks yet again.

I notice that if I want the "Real Time" in the footer I can enter ..........
&[Date]&[Time] but this shows only Hours and Minutes....
How to I get it to display the real time seconds too.

Best Wishes Dermot

 

[Guest]

Hi Steve
Again I will understand if this posting is not appropriate, as you have
already answered my original question.
The reason I have posted it is because of the contination of material and
it's easier to explain myself than start a fresh posting.

I had the print and print preview buttons working but now I have a problem.
I enter all the data into he text boxes, and then click the enter button.
The data is correctly entered into the appropriate cells.

Although I have set the "ShowModal" property to False, when I click on the
Print Preview command button, the print preview appears but the UserForm is
locked on top hiding it from view, I cannot move it either, the blue title
bar is grey. I can magnify the print preview of the worksheet under it.
Closing the print preview screen gives me back control of the UserForm.
Q1. Could you suggest what I may have overlooked?

Q2. Is it possible to put a minimise feature on the UserForm during print
preview or code it to be hidden during print preview......not sure what's
best practice here.

Q3 The Next Sheet button code takes me to Sheet 2 of the work book.
I want to stay on Sheet 1 because it is a prefilled form to which the
UserForm enters data in appropriate cells, or there a way to code it to
automatically transfer the basic form on Sheet 1 to the next work sheet
automatically where data could be entered into it via the UserForm, then when
I have all my populated forms I could print the entire workbook.

Thanks
Dermot

Hi Steve,
thanks yet again.

I notice that if I want the "Real Time" in the footer I can enter ..........
&[Date]&[Time] but this shows only Hours and Minutes....
How to I get it to display the real time seconds too.

Best Wishes Dermot

put the next sheet on a cmd button as well ( like you did with the
others)

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

This will give you the seconds:


ActiveSheet.PageSetup.LeftFooter = TextBox4.Value & " " & Format(Time,
"h:Nn:Ss AM/PM")

 

[stevebriz]

Dermott,

Ok your question Q1.re: print preview change code to as below
This should answer Q2 as well

'-----------------------------------------
'PRINT PREVIEW
UserForm1.Hide
ActiveSheet.Select
ActiveSheet.PageSetup.PrintArea = "A1:T12"
Application.Dialogs(xlDialogPrintPreview).Show
UserForm1.Show
'------------------------------------

Q3/ I am a little confused what you are looking for here. Do you want
to copy all of sheet 1 to the next sheet eg:sheet 2 then be able to
edit the 2nd sheet using the userform?
Please explaing to me a little more