Streetsweeper
New Member
- Joined
- May 7, 2021
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
I am trying to create a save button on a user form that checks cells on sheet are not empty and saves.
The required cells for save will change daily to match the current date.
I use the f=application match to find the column that matches today’s date,
then Worksheet.functioncount to see if the column fields are completed.
Currently all this code is housed under the userform.
But after some reading it sounds like the I need a 2nd sub called Workbook_BeforSave and that needs to be stored under ThisWorkbook. I’ve tried this approach but no luck either.
Private Sub SaveButton_Click(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range, f As Variant, col&
Set rng = Sheets(“QLDailySheet”).Range(“C4:AG4”)
f = Application.Match(CLng(Date), rng, 0)
If Not IsError(f) Then
col = rng(f).Column
Else
Msgbox “Today’s date not found”
Exit Sub
If WorksheetFunction.CountA(Worksheets(“QLDailySheet”).Cells(6,7,8,9,10,11,12,13,15,16,17,18, col)) > 12 Then
Msgbox “—> Save Aborted, “ vbCrLf & “—> All required fields must be completed.”
Cancel = True
Application.
End If
End Sub
The required cells for save will change daily to match the current date.
I use the f=application match to find the column that matches today’s date,
then Worksheet.functioncount to see if the column fields are completed.
Currently all this code is housed under the userform.
But after some reading it sounds like the I need a 2nd sub called Workbook_BeforSave and that needs to be stored under ThisWorkbook. I’ve tried this approach but no luck either.
Private Sub SaveButton_Click(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range, f As Variant, col&
Set rng = Sheets(“QLDailySheet”).Range(“C4:AG4”)
f = Application.Match(CLng(Date), rng, 0)
If Not IsError(f) Then
col = rng(f).Column
Else
Msgbox “Today’s date not found”
Exit Sub
If WorksheetFunction.CountA(Worksheets(“QLDailySheet”).Cells(6,7,8,9,10,11,12,13,15,16,17,18, col)) > 12 Then
Msgbox “—> Save Aborted, “ vbCrLf & “—> All required fields must be completed.”
Cancel = True
Application.
End If
End Sub