Entering Data into a spreadsheet via a UserFrom Advise Please

[Guest]

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]

Try this

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

 

[Guest]

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

 

[Guest]

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]

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

 

[stevebriz]

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

 

[Guest]

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

 

[Guest]

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]

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

 

[Guest]

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]

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.

 

[Guest]

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