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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200707/1

never mind, I figured it out

blackbox wrote:
>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

--
Message posted via http://www.officekb.com

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

On Jul 6, 5:39 am, "blackbox via OfficeKB.com" <u20390@uwe> wrote:
> never mind, I figured it out
>
>
>
>
>
> blackbox wrote:
> >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
>
> --
> Message posted viahttp://www.officekb.com- Hide quoted text -
>
> - Show quoted text -

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")


Susan wrote:
>what was the problem? interesting idea.................
>thanks
>susan
>
>> never mind, I figured it out
>>
>[quoted text clipped - 40 lines]
>>
>> - Show quoted text -

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200707/1

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


On Jul 6, 9:35 am, "blackbox via OfficeKB.com" <u20390@uwe> wrote:
> 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")
>
> Susan wrote:
> >what was the problem? interesting idea.................
> >thanks
> >susan
>
> >> never mind, I figured it out
>
> >[quoted text clipped - 40 lines]
>
> >> - Show quoted text -
>
> --
> Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200707/1