VBA Code – Convert Date Entry in spreadsheet to First Monday of any selected Week

[wcm69]

I’m hoping someone out there can help me with a VBA problem.

I have created a timesheet to calculate the hours worked during a working week (Monday – Sunday) and would like a VBA code which will convert the date entered (for the 7 Days work period) - in my spreadsheet to the first Monday of the inputted date entered into the specific cell – In this case Cell:E6 (in my spreadsheet).

I have managed to build a Monthview calendar which I'm using to enter the date into the cell.

I have looked extensively on the internet and tried various example codes that I’ve found but unfortunately without success, so I thought it pointless posting one.

Ideally I need the date to be converted automatically as the date is picked and the calendar closes.

I’d be really grateful for any help as my VBA knowledge is limited but I can usually make sense of it with a little explanation of where to put what codes etc, i.e. in a module or worksheet/book.

Many thanks in advance

 

[Nine Zero]

Hello,

Here just try changing the commented line to the range where your calendar is inputting the date


*This is if you want the monday before the inputted date

Sub trial()
Dim fmonday As Date


'fmonday = Input the range where your calendar is inputting the date here Range("E6").Value


While Not Weekday(fmonday) = vbMonday
fmonday = fmonday - 1
Wend


Range("E6").Value = fmonday


End Sub

*This is if you want the monday after the inputted date

Sub trial()
Dim fmonday As Date


'fmonday = Input the range where your calendar is inputting the date here Range("E6").Value


While Not Weekday(fmonday) = vbMonday
fmonday = fmonday + 1
Wend


Range("E6").Value = fmonday


End Sub

 

[wcm69]

Hi Nine Zero,

many thanks for taking the time to respond.

Can you please advise me where I should be putting the above code, i.e. in the worksheet or module etc? Also how is it activated when I put the date in Cell:E6 (using a popup calendar)?

I'm trying in advance of any response. As I said in my OP I'm a relative VB noobie.

Again thank you in advance.

 

[Nine Zero]

Oops looks like i misread the E6 part....

So in cell E6 that is where you will be inserting your date correct?

Simply change the red part of the code to the location where you would like the Monday date to appear

Sub trial()
Dim fmonday As Date




'fmonday = Input the range where your calendar is inputting the date here Range("E6").Value




While Not Weekday(fmonday) = vbMonday
fmonday = fmonday + 1
Wend




Range("[B][COLOR=#ff0000]E6[/COLOR][/B]").Value = fmonday




End Sub

Also i have input all this into a Module... im not good with worksheet change events yet so what i did was put in a module and created a shape like a button on worksheet and attached the macro to the shape

 

[NoSparks]

Try this in the sheet module,
you may need to adjust the + 2 depending on your computers regional settings

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Or Target.Address <> "$E$6" Then Exit Sub
If IsDate(Target) Then
    If Weekday(Target) <> vbMonday Then
        Application.EnableEvents = False
        Target = Target - Weekday(Target) + 2
        Application.EnableEvents = True
    End If
End If

End Sub