Hi,
It still shows the // no matter what format i change the cell to. I have
looked at your remedy but not entirely sure where to put it.
Regards,
Nigel
"Dave Peterson" wrote:
I didn't look at all your code, but my bet the trouble is that you're treating
the value in the textbox as a date. It's just text. You have to convert it to
a date (or any number).
Kind of like:
Option Explicit
Private Sub CommandButton1_Click()
Dim myDate As Date
If IsDate(Me.TextBox1.Text) Then
myDate = CDate(Me.TextBox1.Text)
With ActiveSheet
With .Range("a1")
.Value = myDate
.NumberFormat = "mm/dd/yyyy"
End With
End With
Else
MsgBox "Not a date"
End If
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Text = Format(Date, "Long Date")
End Sub
===
But I would be careful with the "long date" format.
With my windows setting, I get:
Tuesday, May 10, 2005
And isdate("Tuesday, May 10, 2005") returns false.
I think I'd be more explict (and avoid a setting a user can change):
Me.TextBox1.Text = Format(Date, "mmmm dd, yyyy")
Nigel wrote:
Hi,
"The date entered on my sheet" means when i press apply on the userform to
inforamtion is added to my sheet in relevant cells. The cell which the date
is applied to is formatted to a date format. initially, i did have format in
my code as per your detail but it did not alter anything. i have included my
complete code below in 2 sections. section 1 is the macro module and section
2 is the code on the userform1.
Section 1:
Public ClientName
Public CompanyName
Public Address
Public Town
Public County
Public Postcode
Public StartName
Public QuoteDate
Public QuoteDay
Public QuoteMonth
Public QuoteYear
Public RemPag
Public Test1
Public MyActivePrinter
Public Test2 As Boolean ' exist sub routine if no order left after message box
Public Test3 As Boolean ' to enable run on of orders
Sub FillInSheet()
Range("A5").Select
ActiveCell.FormulaR1C1 = ClientName
Range("A6").Select
ActiveCell.FormulaR1C1 = CompanyName
Range("A7").Select
ActiveCell.FormulaR1C1 = Address
Range("A8").Select
ActiveCell.FormulaR1C1 = Town
Range("A9").Select
ActiveCell.FormulaR1C1 = County
Range("A10").Select
ActiveCell.FormulaR1C1 = Postcode
Range("A14").Select
ActiveCell.FormulaR1C1 = "Dear " & StartName & ","
Range("H9").Select
ActiveCell.FormulaR1C1 = QuoteDay & "/" & QuoteMonth & "/" & QuoteYear
Range("E46").Select
Total = ActiveCell.Value
Range("E16").Select
End Sub
Sub ActivateSheet()
Test2 = False
Test3 = False
If ActiveSheet.Name < "QUOTE SHEET" Then ' CHECK IF
IN QUOTE SHEET IF NOT DISPLAY MESSAGE
MsgBox ("Macro will only operate in QUOTE SHEET"), , _
"Please switch to QUOTE SHEET"
Exit Sub
End If
If ActiveSheet.Name = "QUOTE SHEET" Then 'CHECK IF ON QUOTE SHEET PAGE
StartSheet 'RUN STARTSHEET MACRO
Exit Sub
End If
Sheets("QUOTE SHEET").Select ' SELECT QUOTE SHEET IN BOOK
CollectSheetDetails ' RUN COLLECTSHEETDETAILS MACRO
End Sub
Sub LoadDetailToForm()
If DeliveryDay = Null Then
UserForm1.Show
Else
UserForm1.Show
End If
' DISPLAY USERFORM1
End Sub
Sub CollectSheetDetails()
Range("A5").Select 'SELECT C1
ClientName = ActiveCell.Value 'PASS VALUE TO VARIABLE
Range("A6").Select 'SELECT C1
CompanyName = ActiveCell.Value 'PASS VALUE TO VARIABLE
Range("A7").Select 'SELECT C1
Address = ActiveCell.Value 'PASS VALUE TO VARIABLE
Range("A8").Select 'SELECT C19
Town = ActiveCell.Value ' PASS VALUE TO VARIABLE
Range("A9").Select 'SELECT C1
County = ActiveCell.Value 'PASS VALUE TO VARIABLE
Range("A10").Select 'SELECT C1
Postcode = ActiveCell.Value 'PASS VALUE TO VARIABLE
Range("A14").Select 'SELECT C1
StartName = ActiveCell.Value
Range("H9").Select
QuoteDate = ActiveCell.Value
Range("C56").Select
QuoteDay = ActiveCell.Value
Range("C57").Select
QuoteMonth = ActiveCell.Value
Range("C58").Select
QuoteYear = ActiveCell.Value
End Sub
Sub StartSheet()
On ErrorHandler GoTo ErrorHandler
Sheets("QUOTE SHEET").Select ' select QUOTE SHEET page
Test3 = True
Range("A5").Select
Test1 = ActiveCell.Text
If Test1 = "Client Contact Name" Then
UserForm1.Show
Exit Sub ' Exit to avoid handler.
End If
If Test1 = "" Then
GoTo ErrorHandler
Exit Sub
End If
UserForm1.TxtClientName.Text = ClientName
ErrorHandler: ' Error-handling routine.
MsgBox ("Sorry you have filled this page. Please contact Nigel Shaw For
Assistance or save as revision"), , _
" Quote Sheet Message"
Test2 = True
End Sub
Section 2:
Private Sub Frame1_Click()
End Sub
Private Sub Lblcomplete1_Click()
Lblcomplete1.Visible = False
End Sub
Private Sub LblComplete1_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub LblComplete2_Click()
Lblcomplete2.Visible = False
End Sub
Private Sub LblComplete2_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub LblComplete3_Click()
Lblcomplete3.Visible = False
End Sub
Private Sub LblComplete3_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub LblComplete4_Click()
Lblcomplete4.Visible = False
End Sub
Private Sub LblComplete4_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub LblComplete5_Click()
Lblcomplete5.Visible = False
End Sub
Private Sub LblComplete5_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub LblComplete6_Click()
Lblcomplete6.Visible = False
End Sub
Private Sub LblComplete6_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub LblComplete7_Click()
Lblcomplete7.Visible = False
End Sub
Private Sub LblComplete7_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub UserForm_Activate()
UserForm1.TxtClientName.Text = ""
UserForm1.TxtQuoteDay.Text = Format(Date, "Long Date")
End Sub
Private Sub UserForm_Click()
End Sub
If it is easier,i can email to you.
Kind Regards,
nigel
"Dave Peterson" wrote:
Did you mean something like:
UserForm1.TxtQuoteDay.Text = Format(Date, "Long Date")
And what does "when the data is entered to my sheet" mean?
If it means you take the Text in the txtquoteday textbox and put it on a
worksheet, try checking the numberformat for that cell.
If that's not it, you may want to share the code that puts it on the sheet.
Nigel wrote:
Hi,
i have a problem. my userform has a text box that enters the date when used.
my code is this:
UserForm1.TxtQuoteDay.Text = (Date, "Long Date")
the problem is, when the data is entered to my sheet, the date comes out
like this:
09 May 2005//
Everything is fine except for the //. Can this be removed or am i doing
something wrong? the cell format is date 09 May 2005.
Help appreciated.
Regards,
Nigel
--
|