date in the form (2000 sr 1)

[sal21]

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...

[HansV]

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

[sal21]

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...

[HansV]

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.

[sal21]

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

[HansV]

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.

[sal21]

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

only a dubt and litle question.
I post Monnday.

[sal21]

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?

[HansV]

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.

[sal21]

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....

[HansV]

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.

[HansV]

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

[sal21]

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...

[HansV]

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.

[sal21]

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...