Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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.

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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.

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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.

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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.

"stevebriz" wrote:

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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" wrote:

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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")

Entering Data into a spreadsheet via a UserFrom Advise Please
 

Dermot wrote:

Also what can I expect to happen when I click the "Next Sheet"
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.

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

"stevebriz" wrote:


Dermot wrote:

Also what can I expect to happen when I click the "Next Sheet"
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.

Entering Data into a spreadsheet via a UserForm Advise Please
 

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

"stevebriz" wrote:

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")

Entering Data into a spreadsheet via a UserForm Advise Please
 

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


"Dermot" wrote:

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

"stevebriz" wrote:

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")

Entering Data into a spreadsheet via a UserForm Advise Please
 

Dermott,

Ok your question Q1. 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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

6.Hi Steve
Hope you had a good weekend.
Thanks for this code....... I'll take a note of it.

In the mean time have added the following to give me seconds, the full code
provides the footer I am looking for except for one thing......I want to
format it to Font Times New Roman, Font size......... I have tried several
different code statements but keep getting syntax errors.....Ahhhh......I try
to follow some of the build in syntax screen tips that follow...but now
joy.....
Can you help me out with it?.......

Heres what I want in Times New Roman Font Size 16
28/08/2006 18:41:20 - Requested by MASTER - Page 1

Here's the code I am using...............

Sheet1.PageSetup.CenterFooter = _
"" & Format(Now, "DD/MM/YYYY HH:MM:SS") & _
" - Requested by MASTER - Page 1"

'I put a label on the user form to see what was happening in the Centre Footer
' Your above code displays the Center Footer the way I want it with Seconds.

Let lblDisplayFooter.Caption = Sheet1.PageSetup.CenterFooter

End Sub

I have tried recording macros to see what code is generated.......but that
has proven inconclusive in this case.

Kind Regards
Demot


"stevebriz" wrote:

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")

Entering Data into a spreadsheet via a UserFrom Advise Please
 

Try this

Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " &
Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by MASTER - Page 1"

Entering Data into a spreadsheet via a UserForm Advise Please
 

Hi Steve,
Thanks for the print preview code.

Q3. The worksheet is a form. I could simply save the active form and then
repopulate it with fresh values from the UserForm and save again.

Then I wondered how I could code the "Next button" on the user form to copy
and paste special to sheet 2 and populate it from the same userform1?
Incidently, would there be a way to include code to name the sheet by the
data entered from one of the text boxes.....ie Client code?...if so what's
the best approch to do this.......just curious?
Thanks
Dermot

"stevebriz" wrote:

Dermott,

Ok your question Q1. 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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

Thanks for the reply Steve
This posting is quite long winded......I have tried to explain myself....if
my questions are going on a bit and you don't want to post any further I will
understand.....
Your Code:-
Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " & _
Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by MASTER - Page 1"
Worked great and displayed the footer as I want it.
What I can't seem to figure out is......what syntax determines the displayed
text...I have been displaying the & etc...

For example I have put a label on the UserForm to reflect what's hapenning
in the footer.............
Let lblDisplayFooter.Caption = Sheet1.PageSetup.CenterFooter
End Sub

Using your code above displays everything in the label caption...... for
example
&"Times New Roman, Regular&"16......is all displayed...
I like this......I can see the formatting description on the userForm label,
and print the correct footer without the description........nice
touch...but....to help me understand....

Can you explain briefly how I reason this out.....for example How would I
change the label code to display exactly as the footer....is it the syntax
that's different?

Any help would again be much appriciated, as I have spent some time trying
to work this out, everytime I think I have a grasp of something....you know
the story, eh?

"stevebriz" wrote:

Try this

Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " &
Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by MASTER - Page 1"

Entering Data into a spreadsheet via a UserFrom Advise Please
 

The syntax for footers and headers is very different to labels
this part gives the font type, whether it is regular, bold, italic etc
and the font size.
"&""Times New Roman,Regular""&16 "

the critical part is to get the " 's in the right places.
If you want to reflect the footer in a label then the best thing is to
make the info you want in a footer as string and reference that.
eg:
Dim foot_string as string
foot_string = Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by
MASTER - Page 1"
label1.caption = foot_string
Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " &
foot_string

'to set the font size for label
label1.Font.Size =16
'then there is
Label1.Font.Name = "Times New Roman"

and so on...
hope this explains it.
Steve

Dermot wrote:
Thanks for the reply Steve
This posting is quite long winded......I have tried to explain myself....if
my questions are going on a bit and you don't want to post any further I will
understand.....
Your Code:-
Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " & _
Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by MASTER - Page 1"
Worked great and displayed the footer as I want it.
What I can't seem to figure out is......what syntax determines the displayed
text...I have been displaying the & etc...

For example I have put a label on the UserForm to reflect what's hapenning
in the footer.............
Let lblDisplayFooter.Caption = Sheet1.PageSetup.CenterFooter
End Sub

Using your code above displays everything in the label caption...... for
example
&"Times New Roman, Regular&"16......is all displayed...
I like this......I can see the formatting description on the userForm label,
and print the correct footer without the description........nice
touch...but....to help me understand....

Can you explain briefly how I reason this out.....for example How would I
change the label code to display exactly as the footer....is it the syntax
that's different?

Any help would again be much appriciated, as I have spent some time trying
to work this out, everytime I think I have a grasp of something....you know
the story, eh?

"stevebriz" wrote:

Try this

Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " &
Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by MASTER - Page 1"

Entering Data into a spreadsheet via a UserForm Advise Please
 

Dermott,
below should work for you for the nextbutton
It will copy all from sheet 1. to whatever your next sheet
now the second part of the question: as long as on your enter button
your refer to the active or selected sheet instead of sheet1 then you
userform should work with whatever sheet you have active or selected.

Private Sub CommandButton3_Click()
'next button sub
If Sheets(ActiveSheet.Name).Index < Worksheets.Count Then
Sheets((Sheets(ActiveSheet.Name).Index) + 1).Activate
End If
ActiveSheet.Select
If TextBox5.Value = vbNullString Then ' textbox5 is client id

MsgBox " operations aborted due to Textbox 5 ClientId empty"
Else
ActiveSheet.Name = TextBox5.Value ' sets sheetname to textbox5 value
With Worksheets("Sheet1") 'copies sheet 1 range
.Range("A1:T12").Copy

ActiveSheet.Range("A1:T12").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'pastes to active sheet.
End With
End If

End Sub

Entering Data into a spreadsheet via a UserFrom Advise Please
 

Hi Steve
Thank you very much for all your help with this project.

Lastly, in your opinion, what would you consider the best way to learn VBA,
in a thourough manner.

Thanks
Dermot

"stevebriz" wrote:

The syntax for footers and headers is very different to labels
this part gives the font type, whether it is regular, bold, italic etc
and the font size.
"&""Times New Roman,Regular""&16 "

the critical part is to get the " 's in the right places.
If you want to reflect the footer in a label then the best thing is to
make the info you want in a footer as string and reference that.
eg:
Dim foot_string as string
foot_string = Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by
MASTER - Page 1"
label1.caption = foot_string
Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " &
foot_string

'to set the font size for label
label1.Font.Size =16
'then there is
Label1.Font.Name = "Times New Roman"

and so on...
hope this explains it.
Steve

Dermot wrote:
Thanks for the reply Steve
This posting is quite long winded......I have tried to explain myself....if
my questions are going on a bit and you don't want to post any further I will
understand.....
Your Code:-
Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " & _
Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by MASTER - Page 1"
Worked great and displayed the footer as I want it.
What I can't seem to figure out is......what syntax determines the displayed
text...I have been displaying the & etc...

For example I have put a label on the UserForm to reflect what's hapenning
in the footer.............
Let lblDisplayFooter.Caption = Sheet1.PageSetup.CenterFooter
End Sub

Using your code above displays everything in the label caption...... for
example
&"Times New Roman, Regular&"16......is all displayed...
I like this......I can see the formatting description on the userForm label,
and print the correct footer without the description........nice
touch...but....to help me understand....

Can you explain briefly how I reason this out.....for example How would I
change the label code to display exactly as the footer....is it the syntax
that's different?

Any help would again be much appriciated, as I have spent some time trying
to work this out, everytime I think I have a grasp of something....you know
the story, eh?

"stevebriz" wrote:

Try this

Sheet1.PageSetup.CenterFooter = "&""Times New Roman,Regular""&16 " &
Format(Now, "dd/mm/yyyy HH:MM:SS") & " - Requested by MASTER - Page 1"

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

Hi Dermott.
Really handling the errors is tha last option if you can;t prevent them
from occurring.
this might give you a start to error handling

http://www.cpearson.com/excel/ErrorHandling.htm

When you code ...try to think about think that can cause error ( this
comes with practice)...
here is an simple..example
eg: say you have textbox...and you aske the user to enter a number you
are going to divide by 10? what happens if the user enters letters.???
you will get an error.
You can prevent the error message occuring by putting a check ..If not
is numeric(textbox1.value) then textbox1.value =""

we that just one example..but maybe you get the idea from it.
We can take this offline on to email..if you like. my email is as in
this forum.

Dermot wrote:
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

Entering Data into a spreadsheet via a UserFrom Advise Please
 

Thanks for this link Steve,

I will read through it. From your explanation, I take from it that it's like
using programming code to create Data Validation which is important and
introducing error handling only if nothing else can be done.
Cheers
Dermot

"stevebriz" wrote:

Hi Dermott.
Really handling the errors is tha last option if you can;t prevent them
from occurring.
this might give you a start to error handling

http://www.cpearson.com/excel/ErrorHandling.htm

When you code ...try to think about think that can cause error ( this
comes with practice)...
here is an simple..example
eg: say you have textbox...and you aske the user to enter a number you
are going to divide by 10? what happens if the user enters letters.???
you will get an error.
You can prevent the error message occuring by putting a check ..If not
is numeric(textbox1.value) then textbox1.value =""

we that just one example..but maybe you get the idea from it.
We can take this offline on to email..if you like. my email is as in
this forum.

Dermot wrote:
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