adding a self destruct feature

[blackbox via OfficeKB.com]

I have a workbook on a network server and have distributed shortcuts to
several people. The workbook requires users to login in with unique usernames
and passwords so I can track who's used it. I recently added a 30 day
expiration that is reset by logging in with my username and password but I
would like to take this a step further.

The SelfDestruct Sub works fine if manually run and the Calc. event works
with a different output event (placing a value in a cell or something similar)
but not together.

any ideas on this?


cell D12 =IF(A1<C1,"yes","no")


Private Sub Worksheet_Calculate()
Worksheet_Change (ThisWorkbook.Sheets("Input").Range("d12"))
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
With ThisWorkbook.Sheets("Input")
If Target.Value <> "" Then
If Target.Value = "yes" Then SelfDestruct
End If
End With
End Sub

Sub SelfDestruct()
Dim vbCom As Object

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
vbCom.Item ("Module2")

End Sub

 

[blackbox via OfficeKB.com]

never mind, I figured it out

I have a workbook on a network server and have distributed shortcuts to
several people. The workbook requires users to login in with unique usernames
and passwords so I can track who's used it. I recently added a 30 day
expiration that is reset by logging in with my username and password but I
would like to take this a step further.

The SelfDestruct Sub works fine if manually run and the Calc. event works
with a different output event (placing a value in a cell or something similar)
but not together.

any ideas on this?

cell D12 =IF(A1<C1,"yes","no")

Private Sub Worksheet_Calculate()
Worksheet_Change (ThisWorkbook.Sheets("Input").Range("d12"))
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
With ThisWorkbook.Sheets("Input")
If Target.Value <> "" Then
If Target.Value = "yes" Then SelfDestruct
End If
End With
End Sub

Sub SelfDestruct()
Dim vbCom As Object

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
vbCom.Item ("Module2")

End Sub

 

[Susan]

what was the problem? interesting idea.................
thanks
susan

 

[blackbox via OfficeKB.com]

felt kinda dumb when I figured it out.

in cell D12 "Yes" was upper case and in the code it was lower case...

also


vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
vbCom.Item ("Module2")

only deleted Module1

changed it to

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")

vbCom.Remove VBComponent:= _
vbCom.Item ("Module2")

what was the problem? interesting idea.................
thanks
susan

never mind, I figured it out

[quoted text clipped - 40 lines]

- Show quoted text -

 

[Susan]

don't feel dumb - & plus you might have just helped somebody out of a
future nightmare!
thanks!
susan

felt kinda dumb when I figured it out.

in cell D12 "Yes" was upper case and in the code it was lower case...

also

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
vbCom.Item ("Module2")

only deleted Module1

changed it to

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")

vbCom.Remove VBComponent:= _
vbCom.Item ("Module2")

what was the problem? interesting idea.................
thanks
susan

[quoted text clipped - 40 lines]

- Show quoted text -