Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date in the form (2000 sr 1)

    modify, please...

    make a button on the sheet and associated that to a mcaro with this condition:

    If the date in the column K and L are the same make to appear a msgbox "ALL OK!" and after skip to call MY MACRO(), if the date present in the column K and L are not the same start the form etc, etc...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date in the form (2000 sr 1)

    You can create the command button yourself, from the Forms toolbar or from the Control Toolbox. If you create a command button from the Forms toolbar, you can right-click it to assign a macro. If you use the Control Toolbox, make sure you are in design mode when modifying it; double clicking will create an (empty) On Click event procedure for you. The code could look like this in both cases:

    Private Sub cmdTest_Click()
    Dim lngRow As Long
    ' Loop through rows
    For lngRow = 2 To Range("K65536").End(xlUp).Row
    ' Test cells in columns K and L
    If Not (Range("K" & lngRow) = Range("L" & lngRow)) Then
    ' Show form and get out
    UserForm2.Show
    Exit Sub
    End If
    Next lngRow
    ' We only get here if there are no differences
    MsgBox "All OK!", vbInformation
    End Sub

    (The name of the command button could be different.)

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date in the form (2000 sr 1)

    MISSION IMPOSSIBLE...

    Naturally in the event open wbook

    is possible to insert in the column K of this sheet the day not used this (add this tu up and not delete existing date).

    Note: i have insert in autoclose of this sheet a comand to save it always.

    Example:

    i open this today
    i close (and autosave)

    i reopen this after 12 day

    after the open sheet insert in the column K the step of date relative to not used this
    sheet in the wbook

    consider:

    without :Saturday Sunday

    without :Cristmas, New Year's Day,

    without : a range of date to refer the national italian holyday

    in effect refer to a complete calendar day with only a work day...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date in the form (2000 sr 1)

    I don't think I understand what you want. In the worksheet you attached in the first post in this thread, column K contains dates. Now you seem to want to add a date difference to column K.

    To compute the difference between two dates, you can use the NETWORKDAYS function (GIORNI.LAVORATIVI.TOT in Italian). To use this function, you must activate the Analysis Toolpak add-in in Tools | Add-Ins... You can specify the start date, the end date and a range of cells containing holidays such as Christmas Day.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date in the form (2000 sr 1)

    This is another problem the first is solved.

    New problem:

    Example:

    i open the sheet on 01/09/2004
    i close the sheet in the same date (and autosave)

    i reopen this after 12 day

    when i open the wbook in the sheet insert in the column K the step of date relative to not used sheet.
    with not deleted the exinsting date up from the last cell

    in this case

    02/09/2004
    03/09/2004
    04/09/2004
    07/09/2004
    08/09/2004
    09/09/2004
    10/09/2004
    11/09/2004
    ....
    ....

    I hope of to have been clear

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date in the form (2000 sr 1)

    I have attached a workbook that does what you want (I hope).

    There is a range named Holidays that contains national and public holidays. In the attached workbook, it is defined as =OFFSET(Test!$Q$2,,,COUNTA(Test!$Q:$Q)-1). It is a dynamic range, that will adapt itself automatically if you add new holidays in column Q.

    The standard module Module1 contains a function IsHoliday:

    Function IsHoliday(d As Date) As Boolean
    IsHoliday = Not (Range("Holidays").Find(d) Is Nothing)
    End Function

    This function returns True if the date supplied is in the list of holidays. The Workbook_Open event procedure adds dates to column K that are not weekend days or holidays, up to and including today:

    Private Sub Workbook_Open()
    Dim lngRow As Long
    Dim d As Date
    lngRow = Range("K65536").End(xlUp).Row
    d = Range("K" & lngRow) + 1
    Do While d <= Date
    If Weekday(d) > 1 And Weekday(d) < 7 And IsHoliday(d) = False Then
    lngRow = lngRow + 1
    Range("K" & lngRow) = d
    End If
    d = d + 1
    Loop
    End Sub

    If you don't want to include today, change d <= Date to d < Date.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date in the form (2000 sr 1)

    nothing is impossible for HansV!!!!!!!!!!!!!!!!!!!!!

    only a dubt and litle question.
    I post Monnday.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date in the form (2000 sr 1)

    I am here...

    My dubt:

    You have maked this test:

    The range of date start at 06/08/2004, have you change the date of the clok of your computer in 06/08/2004 and afeter you have save the file in this date?
    Re change the date of your computer in today and reopen the file?

    But is possible to get the date in txt format? (for example date, dd/mm/yyyy)

    I can insert others dates in the column Q?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date in the form (2000 sr 1)

    1. I just entered some dates in column K manually to start with. There must be at least one date in column K, otherwise the code won't work.
    2. You can change the format of column K (and of column Q) to whatever you like.
    3. You can add and remove holidays in column Q. The range named "Holidays" is a dynamic range, it adapts itself automatically to the contents of column Q.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date in the form (2000 sr 1)

    For test i have make this:

    1)I have saved the sheet (befor the save i have change the date of my computer in 01/09/2004)
    2)I have see the propety of sheet "last used 01/09/2004"
    3)I have re-change the date of my computer in 25/09/2004
    4) I have re open the wbook.

    But the step of date not are present from 01/09/2004 to today....

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date in the form (2000 sr 1)

    Does the workbook I posted this afternoon (in <post#=411160>post 411160</post#>) work for you? It does fill in a series of dates for me when I open it.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date in the form (2000 sr 1)

    Does it work better for you if you change the Workbook_Open code to the following?

    Private Sub Workbook_Open()
    Dim lngRow As Long
    Dim d As Date
    lngRow = Range("K65536").End(xlUp).Row
    d = CDate(Range("K" & lngRow)) + 1
    Do While d <= Date
    If Weekday(d) > 1 And Weekday(d) < 7 And IsHoliday(d) = False Then
    lngRow = lngRow + 1
    Range("K" & lngRow) = Format(d, "dd/mm/yy")
    End If
    d = d + 1
    Loop
    End Sub

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date in the form (2000 sr 1)

    Solved all prob....
    I have inserted this sheet in a wbook and for i hide this (this is a sheet service not important to show this, it contain only a workin data)
    But is possible to run the macro with the sheet hide or i must set and define the complete range?

    If yes...

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date in the form (2000 sr 1)

    To make the code work for a hidden worksheet, you must refer to it explicitly:

    Private Sub Workbook_Open()
    Dim lngRow As Long
    Dim d As Date
    Dim wsh As Worksheet
    Set wsh = Worksheets("DB AGENZIE")
    lngRow = wsh.Range("K65536").End(xlUp).Row
    d = CDate(wsh.Range("K" & lngRow)) + 1
    Do While d <= Date
    If Weekday(d) > 1 And Weekday(d) < 7 And IsHoliday(d) = False Then
    lngRow = lngRow + 1
    wsh.Range("K" & lngRow) = Format(d, "dd/mm/yy")
    End If
    d = d + 1
    Loop
    Set wsh = Nothing
    End Sub

    Modifications to the code are indicated in bold. Change the worksheet name if necessary.

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date in the form (2000 sr 1)

    ok! for the hide solution...

    But If in your macro if I want to exclude for example only the monday and Sunday...

    Or if decide the days to exclude from the range? is possible...

Page 1 of 2 12 LastLast

Posting Permissions

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