VBA - Problem with Coding controls for values based on Dates

[The Godfather]

Hi,

This is my first VBA project and I am preparing some sort of timesheet to be used in my workplace.

By extensive research, time committed and effort, I managed to do lots of things in my excel file such as only allowing data entry to "database" sheet via Userform, distinguish users by user/passwords therefore no user can modify data of other users, some dynamic dropdowns based on various criteria, using VBA codes to control such as disabling posting if wrong manhours are entered into form, restricting all unwanted format editing by others, etc. I am using excel since year 2006, however did not need any of those before, and it has been a perfect mental gymnastics and a upbeat journey for me to learn and implement those.

However I am stuck now.

To express my problem (Sorry in advance, I was never good in expressing myself briefly, so I'll give lots of details, mostly irrelevant);
There is a form sheet (not a Userform actually but simply a sheet due to several reasons) where users just arrange their "Weekly Table" to post into database.
A part of the form is attached. The logic might seem a little offbeat; but it works like this,
The user selects the which week to fill in Cell I7. Then, excel automatically brings the first weekday (Monday) and last weekday (Friday) of the selected week.
Then the 5 weekdays are automatically equally divided into 10 records as on rows 12-21 (4,5 hours each, a total of 45 hours/week). And for each row, the user selects activity, project name, etc.

The catch is; the user is free to redistribute the fractions of time in rows 12-21 as he/she likes.
For instance; the user may decide to go on already arranged 10-fractions with 4,5 hours each, like this;

Or the user can divide it into two parts (first row 2 days = 18 hours, second row 3 days = 27 hours).



As long as;
- The hours are 4,5 hours (or multipliers of 4,5 hours),
- The total hours sum up to 45,
- The manually dates typed by the user is in between (or equal) to the dates in cells J8 and J9 (in the first photo),
It is OK for posting.

In this form; I have to put several controls.
Here are the controls I was able to put:
1) The sum of all hours should be equal to 45 (Solved with VBA)
2) In each row, the difference between End and Start dates can be typed as multipers of 4,5. For instance; if two days are entered, the user can only type 13,5 hours (1,5 days) or 18 hours (Solved with Data Validation)

However these are the three controls I'd like to embed into my VBA code, to disable users to post into database if anything is not complying (Some could be solved by data validation but I do not prefer that);
As I explained before, the dates can be manually modified by user. Therefore I need to implement these controls;
1) The earliest date user typed to the range (cells between F12-G21) cannot be an earlier date than the date in Cell J8.
2) The latest date user typed to the range (cells between F12-G21) cannot be a later date than the date in Cell J9.
3) Whatever the dates are typed (typed into 1 row, 5 rows, 10 rows), the date-order should be in sequence..

I've been struggling to solve the first two problems for a couple of hours but could not reach a result.
I am trying to type a code like this (BTW let me tell cells F12-F21 are ranged as "Beginning" and G12-G21 are ranged as "Ending")

Function Validate() As Boolean

    Dim frm As Worksheet
    Set frm = ThisWorkbook.Sheets("Form")

    Validate = True

If frm.Range("Beginning") < frm.Cells("J8") Then
MsgBox "A date in your StartDate column is before the desired date range"
Validate = False
End If

If frm.Range("Beginning") > frm.Cells("J9") Then
MsgBox "A date in your StartDate column is after the desired date range"
Validate = False
End If

If frm.Range("Ending") < frm.Cells("J8") Then
MsgBox "A date in your EndDate column is before the desired date range"
Validate = False
End If

If frm.Range("Ending") > frm.Cells("J9") Then
MsgBox "A date in your EndDate column is after the desired date range"
Validate = False
End If

I understand that Dates are not to be treated as any other types (Strings, numbers, etc.) however need help on how to continue.

Thanks to anyone who read so far.

 

[NdNoviceHlp]

That's quite a read. I'm not sure what "cells F12-F21 are ranged as "Beginning" and G12-G21 are ranged as "Ending" means? If they're named ranges then you still need to loop through the range something like...

Dim Rng As Range
For Each Rng In frm.Range("Beginning")
If CDate(Rng.Value) < CDate(frm.Range("J8")) Then
MsgBox "A date in your StartDate column is before the desired date range"
Validate = False
Exit For
End If
Next Rng

HTH. Dave

 

[The Godfather]

That's quite a read. I'm not sure what "cells F12-F21 are ranged as "Beginning" and G12-G21 are ranged as "Ending" means? If they're named ranges then you still need to loop through the range something like...

Dim Rng As Range
For Each Rng In frm.Range("Beginning")
If CDate(Rng.Value) < CDate(frm.Range("J8")) Then
MsgBox "A date in your StartDate column is before the desired date range"
Validate = False
Exit For
End If
Next Rng

HTH. Dave

Thank you Dave, that worked nicely.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave