deactivate code

[Afarag]

Dears,

kindly please i have a question related to VBA code that i want to determine a date that when match it, the running code will be deactivate

regards,

 

[Debraj]

As soon As match function match the lookup value.. press "Ctrl + Pause Break" to stop running the code anymore..

 

[Afarag]

Hi @Debraj

I'm so sorry for confusing to understand your idea, but you can clarify if i have some VBA code "EX: as the below code", that i want the operation of this code will go deactivated at #12:00:00 PM, 1-Mar-2014, the users can use this code till the mentioned date only.

Function GetStart(Name As String, Instance As Long) As Variant
    Dim r As Long
    Dim c As Long
    Dim f As Boolean
    Dim n As Long
    Dim wsh As Worksheet
    Dim cel As Range
    GetStart = ""
    Set wsh = Worksheets("Scheduale")
    Set cel = wsh.Range("C4:C" & wsh.Rows.Count).Find(What:=Name, LookAt:=xlWhole, _
        MatchCase:=False)
    If cel Is Nothing Then Exit Function
    r = cel.Row
    For c = 6 To 65 ' F to BM
        If wsh.Cells(r, c).Interior.Color = vbRed Then
            If f = False Then
                n = n + 1
                If n = Instance Then
                    GetStart = wsh.Cells(3, c).Value
                    Exit Function
                End If
                f = True
            End If
        ElseIf f = True Then
            f = False
        End If
    Next c
End Function

Function GetEnd(Name As String, Instance As Long) As Variant
    Dim r As Long
    Dim c As Long
    Dim f As Boolean
    Dim n As Long
    Dim wsh As Worksheet
    Dim cel As Range
    GetEnd = ""
    Set wsh = Worksheets("Scheduale")
    Set cel = wsh.Range("C4:C" & wsh.Rows.Count).Find(What:=Name, LookAt:=xlWhole, _
        MatchCase:=False)
    If cel Is Nothing Then Exit Function
    r = cel.Row
    For c = 6 To 65 ' F to BM
        If wsh.Cells(r, c).Interior.Color = vbRed Then
            If f = False Then
                n = n + 1
                f = True
            End If
        ElseIf f = True Then
            If n = Instance Then
                GetEnd = wsh.Cells(3, c).Value
                Exit Function
            End If
            f = False
        End If
    Next c
End Function

 

[dan_l]

Wrap it with an if statement?

 

[dan_l]

The only thing is, this sort of thing sometimes doesn't help as much as you think it will. I have some workbooks that 'spoil' - for example - where calculations are based on data that expires every time. I use the if old msgbox "GET THE NEW VERSION DUDE" else do whatever trick. It works really well when you've got 20 users that are right next to you. Less so when there's 50 users who are more distributed.

 

[Afarag]

Dear Dan_l

thanks a lot it's handled, by the below solution


Here's an even more 'dastardly' solution: if the date is on or after the specified date, then the workbook immediately closes after an announcement that the license has expired.



This goes into the ThisWorkbook code module



Private Sub Workbook_Open()
Dim termDate As Date

termDate = #3/1/2014 12:00:00 PM#
If Now() >= termDate Then
MsgBox "The license on this software has expired.", _
vbOKOnly + vbCritical, "License Expired"
ThisWorkbook.Close
End If
End Sub



If you'll set the VBAProject security to require a password to view the code, then they will have a difficult time even getting to their data. While they can hold the [Shift] key while opening the file to stop the shutdown code from running, then none of the other VBA code in the workbook is going to work either.



If you do assign a password to the VBAProject, be sure to keep a copy of the file without that lockdown so that if you forget the password in the future, you'll have a copy you can open and work with easily. I'd also actually 'write down' the VBAProject password in a .txt file and store it in the same folder on your computer with the source files. That helps you remember it, and since presumably others don't have access to your computer, it's still secure.

 

[Afarag]

Dears,

I ask for how can i control the expiry date to modify or change it from another excel sheet?

 

[Marc L]

Hi,

much ado about nothing !

This kind of protection can be easily bypassed just by changing the computer system date
or by passing the project password (so many tools through the net !) …

And you should know, the shift key prevents only to run the Open procedure
but all others procedures still available just by clicking the icon to disable the Creation Mode !

So if you want a clever protection, you must control at the beginning of each procedure …

If you're an expert VBA developer, it could be possible to modify the code by another VBA procedure
but it's easier to do it manually ! Best is to store the expiring date in a crypted file or in the Registry …​

 

[Afarag]

the question didn't related to the security somehow but concern some organization for the data, that i want to control the using of the sheet and force the user to use any operation till specific date till another sheet modification. so that i ask how to control the sheet remotely in this question

Gratefully,

 

[SirJB7]

Hi, Afarag!

I agree with Marc L. Just in case you're not aware of it, if you press and hold Shift key when opening a file with macros (.xls, .xlsm, .xlsb), you bypass elegantly any code in the workbook open section, but all the other macros and VBA code will continue operating normally.

Regards!

 

[dan_l]

Yeah there is an area where you're trying to enforce practices on your users rather than trying to do security. I've got a project right now where I'm going to have probably 100-200 distributed users and I thought about putting a date thing on this, but after thinking about it, I'm going with tight version control and big bold red letters that say "IF IT"S AFTER THIS DATE< DONT" USE THIS AND GO HERE TO GET A NEW COPY".