Event Series Button

[LibertyAvenger]

Hi All,

I am looking to create a button in my work sheet that floats below a table that can only work on the last day of the month and beyond but never sooner. It’s function would be as follows on click.

1. Message: “You Are About To Close This Month, This Can Not Be Undone” Proceed? Enter Y= Yes N= No.
2. Yes? Selects specific cells, Merge & Center, Highlight Red, Enter Text in white, “Closed For The Month of “Sheet Name”.
3. Create Copy of
Master Sheet, Rename New Sheet with Current Months Name and Current Year. Check if Sheet Exist if not proceed otherwise go to next month. (I.e. closed on March 31st 2022, March 2022 Exist so create April 2022 instead.)
4. Make New Sheet the Last Sheet in workbook
5. Show new worksheet.

Sample Workbook

 

[ranman256]

like this:

Public Sub CloseEoM()
Dim vRet, vMoNum, vYr, vMoYr

  'get from a FORM?
vMoNum = 3   '=txtMoNum
vYr = 2022  '=txtYear
vMoYr = vMoNum & "-" & vYr

If Date < getLastDoM(vMoNum, vYr) Then
   MsgBox "The month (" & vMoYr & ") is not over."
   Exit Sub
End If

vRet = MsgBox("You Are About To Close Month:" & vMoYr & vbCrLf & "This Can Not Be Undone." & vbCrLf & "Proceed", vbQuestion + vbYesNo, "Confirm")
Select Case vRet
   Case vbYes
     'process data
   Case vbNo
     Exit Sub
End Select

'run merge cells  here, also given on FORM

'copy sheet here

 'new sheet
sheets.Add
End Sub


Private Function getLastDoM(ByVal pvMoNum, ByVal pvYear)
Dim vDat
vDat = pvMoNum & "/1/" & pvYear
'vDat = Month(Date) & "/1/" & Year(Date)
getLastDoM = DateAdd("d", -1, DateAdd("m", 1, vDat))
End Function

 

[LibertyAvenger]

like this:

Public Sub CloseEoM()
Dim vRet, vMoNum, vYr, vMoYr

  'get from a FORM?
vMoNum = 3   '=txtMoNum
vYr = 2022  '=txtYear
vMoYr = vMoNum & "-" & vYr

If Date < getLastDoM(vMoNum, vYr) Then
   MsgBox "The month (" & vMoYr & ") is not over."
   Exit Sub
End If

vRet = MsgBox("You Are About To Close Month:" & vMoYr & vbCrLf & "This Can Not Be Undone." & vbCrLf & "Proceed", vbQuestion + vbYesNo, "Confirm")
Select Case vRet
   Case vbYes
     'process data
   Case vbNo
     Exit Sub
End Select

'run merge cells  here, also given on FORM

'copy sheet here

 'new sheet
sheets.Add
End Sub


Private Function getLastDoM(ByVal pvMoNum, ByVal pvYear)
Dim vDat
vDat = pvMoNum & "/1/" & pvYear
'vDat = Month(Date) & "/1/" & Year(Date)
getLastDoM = DateAdd("d", -1, DateAdd("m", 1, vDat))
End Function

Thank you so much for the reply. I will apply this to my current workbook on Sunday and let you know how it works out.

 

[LibertyAvenger]

like this:

Public Sub CloseEoM()
Dim vRet, vMoNum, vYr, vMoYr

  'get from a FORM?
vMoNum = 3   '=txtMoNum
vYr = 2022  '=txtYear
vMoYr = vMoNum & "-" & vYr

If Date < getLastDoM(vMoNum, vYr) Then
   MsgBox "The month (" & vMoYr & ") is not over."
   Exit Sub
End If

vRet = MsgBox("You Are About To Close Month:" & vMoYr & vbCrLf & "This Can Not Be Undone." & vbCrLf & "Proceed", vbQuestion + vbYesNo, "Confirm")
Select Case vRet
   Case vbYes
     'process data
   Case vbNo
     Exit Sub
End Select

'run merge cells  here, also given on FORM

'copy sheet here

 'new sheet
sheets.Add
End Sub


Private Function getLastDoM(ByVal pvMoNum, ByVal pvYear)
Dim vDat
vDat = pvMoNum & "/1/" & pvYear
'vDat = Month(Date) & "/1/" & Year(Date)
getLastDoM = DateAdd("d", -1, DateAdd("m", 1, vDat))
End Function

Ranman256

In my attempt attempt I applied this as a module and assigned it to a button… Was this the correct way to apply this? Is there anything I need to change in the code for it to work with my workbook?