Is it possible to restrict/lock a command button in excel to only be used in once a week?

Question

I am working on a Excel program for school, based off of my parents' business. It is basically something I have to create to organize their business data (formerly on paper) digitally. The thing is, clients for the business usually pay every month, so i would like to update their required payment weekly. I've created a command button that updates the payment required by the client, but I need a way for it to only be able to be clicked every 7 days. Is there any way to do this within Excel (with or without VBA).

Answer 1

Here's an option for you. How about:

• After each time your code has run add a timestamp to a cell in a workings sheet to record the run
• Then for each run going forwards read in the timestamp (i.e. the last time that the code was executed, calculate the difference between these two dates in terms of number of days then depending on this decided whether to run the main code or not

So, first of all, after each code run record the date and time of the run in a cell:

Option Explicit

Sub test()

    Dim RunDateTime As Date

    'standard code
    '...

    'note the date and time of the run
    RunDateTime= Now()

    'record the date and time of the run in a workings sheet in excel
    Sheets("Workings").Range("A1") = RunDateTime

End Sub

Then at the start of each run going forwards compare this timestamp with the current date to decide how to proceed:

(full example code below)

Option Explicit

Sub test()

    Dim LastRunDateTime As Date
    Dim DaysSinceLastRun As Integer
    Dim proceed As Boolean
    Dim RunDateTime As Date

    ' Find when last run
    LastRunDateTime = Sheets("Workings").Range("A1")

    ' Depending on days since last run decide whether to proceed or not
    If LastRunDateTime = 0 Then

        MsgBox "Hasn't been run before so will proceed with main code"

        proceed = True

    Else

        ' Calculate how many days since last run
        DaysSinceLastRun = Now() - LastRunDateTime

        If DaysSinceLastRun < 7 Then

            MsgBox "Code was only run on " & LastRunDateTime & " therefore not time yet to run again"

            proceed = False

        Else

            MsgBox "Code was last run > 7 days ago therefore will proceed with running main code!"

            proceed = True

        End If

    End If

    'standard code
    If proceed = True Then

        'blahblahblah

        MsgBox "Running main code!"

    End If

    'note the date and time of the run
    RunDateTime = Now()

    'record the date and time of the run in a workings sheet in excel
    Sheets("Workings").Range("A1") = RunDateTime

End Sub