Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date question, forms & templates (XP)

    I have a few form templates where dates are required. I would like to have the date entered automatically into the template when opened, however the date needs to remain static when saved as an xls. When others view the saved form later, the date must remain original.

    On another form template I would like to have the date converted to an invoice number with an additional incremental increase. For example the format would follow 051403-1, 051403-2, 051403-3 & so on. Again, this item must remain static when opened as an xls.

    As an example, I recently started work for this small firm, and noticed that they were using "=NOW", and they were confused as to why the dates on forms were never accurate.... I'd like to cure this...

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date question, forms & templates (XP)

    ctrl-semicolon (ctrl-[img]/forums/images/smilies/wink.gif[/img] will add the date as static in a cell
    Otherwise you could write a macro that adds the date automatically. The following will add the date in col A in the row that something is added to a cell in column B.

    Note the code goes into the SHEET objec (not a normal module) of the sheet where you want the code to run.

    Steve
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Cells(Target.Row, 1).Value = Date
    End If
    End Sub
    </pre>


  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date question, forms & templates (XP)

    From what you say, it is not altogether clear whether you are seeking to rebuild from scratch or work with an existing system.

    <UL><LI>On the basis that yourfirst template will have "=NOW()" in it, the VBA code you will need to freeze the value of the cell - say B4 - is:

    Range("B4")=Range("B4").Value

    As long as you're happy with having the code stay inside the invoice workbook, you can set this up in the Workbook_BeforeSave event.

    <LI>For the other Tempate, try using the CONCATENATE function for the invoice numbering

    =CONCATENATE(TEXT(NOW(), "mmddyy")&"-1")
    [/list]Some of what you are suggesting will take a bit more planning. Post back if you have more queries.

    HTH
    Gre

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date question, forms & templates (XP)

    This code, placed in the Open event routine will place a datevalue in cell A1 of worksheet Sheet1 when the template is used to create a new workbook.

    <pre>Private Sub Workbook_Open()
    If ActiveWorkbook.Path = "" Then
    Worksheets("Sheet1").Range("A1").Value = Date
    End If
    End Sub
    </pre>

    Legare Coleman

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date question, forms & templates (XP)

    Alrighty then, you folks have me pointing in the right direction, this is where I stand currently:

    On the invoice:

    Private Sub Workbook_Open()
    If ActiveWorkbook.Path = "" Then
    Worksheets("Invoice").Range("M13").Value = Date
    Worksheets("Invoice").Range("M3").Value = Date + Time
    End If
    End Sub

    where using the "Date + Time" allows me to get away from using date-1, date-2, date-3 etc for the invoice number, and both are static on later views of the sheet. Perfect.

    Now, I would like to have the data from M3 used as the file name when saving & closing from a new macro button--

    Sub Invoice_Save()
    With ActiveWorkbook
    .SaveAs "MayM3_cell_data_should-be_here.xls"
    .Close
    End With
    End Sub

    I haven't been able to work out the command to grab the cell data though...

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date question, forms & templates (XP)

    Something like:
    .saveas "may"& format(range("m3").value, "mm-dd-yyyy hh-mm-ss AM/PM"))& ".xls"

    change the format string to get the date/time different. You can NOT use colons and slashes so I substituted dashes

    Or even to make the month folder even generic:

    .saveas format (range("m3").value, "mmmm") &""& format(range("m3").value, "mm-dd-yyyy hh-mm-ss AM/PM"))& ".xls"


    Steve

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date question, forms & templates (XP)

    .saveas format (range("m3").value, "mmmm") &""& format(range("m3").value, "mm-dd-yyyy hh-mm-ss AM/PM"))& ".xls"

    wow- now that's pretty darn smooth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •