Skip to main content

Prevent macro from Running Twice in one day (Using GetSetting and SaveSetting to hold values)

Hi one and all,
This time I have come up with a RunOrNot Macro.The purpose of this macro is to restrict user to run the Main Macro (YOUR_MACRO) for once in a day.

Changes required in the code:-
In the Code below you have to change the YOUR_MACRO with your actual main macro.The text of Msgbox can be changed accordingly.


Sub RunOrNot()
    ExDate = GetSetting("Excel", "Forum", "ExDate")
    
    If Not IsDate(ExDate) Then
        SaveSetting "Excel", "Forum", "ExDate", Format(Date, "DD-MM-YYYY")
        MsgBox "This is the first and last time today that you are running the macro.", vbInformation
        Call YOUR_MACRO
    
    ElseIf (Date - DateValue(ExDate)) & 0 Then
        MsgBox "This is the next day. Click OK to run the macro.", vbInformation
        SaveSetting "Excel", "Forum", "ExDate", Format(Date, "DD-MM-YYYY")
        Call YOUR_MACRO
    
    Else
        MsgBox "You have already run this macro today. So now you can't.", vbInformation
    
    End If
End Sub


 Explanation:-
Actually, this macro uses Windows registry settings to save current day when the code is run for the first time in a day.
The benefit of using the Registries is that :-
If the user re-downloads the file (having this code inside) on the same computer, even then he will be restricted to run the Main Macro because the ExDate (Current Date) would have already been saved in the windows registry.
I hope you like the concept.
If you have any doubts in implementing it and understanding it then do comment.

Regards,
Vikas Gautam

Labels:

Comments

Post a Comment

Popular posts from this blog

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more