Delete Macros 3

[simoncpage]

Hi all, I am looking into adding some security to my excel sheet I want to add a macro that can check if the vba has been unlocked or if a sheet has been unlocked and then it deletes a specific macro from the workbook.

Can anyone help me with this, thanks

Simon

 

[xlbo]

tricky - Can be done BUT - once the macro is deleted, it will stay that way - it cannot be undone. Are you sure this is what you want to do ? Rgds
~Geoff~

 

[simoncpage]

Yep very sure, I want to send a cashflow to a competitor of ours and I want to make sure that if they unlock the cashflow or the vba that it deletes the macros purely as a security thing.

Thanks

 

[RobBroekhuis]

Wouldn't Excel's existing protections give you enough options? (I'm not sure, since I'm seldom concerned about data/macro security)
Rob

 

[xlbo]

Simon - before I post some code (I'm gonna have to trawl some old emails for that), you will also have to ensure that macros are enabled in the 1st place, otherwise any deletion of code will not work and the whole thing becomes redundant - this is a lot of work for something that sounds so easy
Would it not be easier to just shut the workbook down if protection is taken off...
In worksheet_Change
If activesheet.protected = false then
activeworkbook.close savechanges:=false
else
end if
end sub Rgds
~Geoff~

 

[simoncpage]

Not really that is an option I have used before in the past which is fine for certain circumstances.

But what I really want is a more protective measure so that if the vba code is unlocked it deletes specific macros.

I am okay with the deleting of the macros just dont know how to work out if the vba code has been unlocked or not.

I would really appreciate this if you have time

Thanks

 

[RobBroekhuis]

Simply check the activeworkbook.vbproject.protection property. It's 1 if the VBA project is protected, 0 otherwise.
Rob

 

[xlbo]

ah right - thought you needed help with the deleting of code
To be honest I don't think you can unlock a Project via VBA and if someone uses something other than VBA to break in, you're gonna be stuffed anyway. As far as I know, there is no test to see if a VBA project has been unlocked but I'm open to learning something new Rgds
~Geoff~

 

[xlbo]

Simon - done some digging and this:
ThisWorkbook.VBProject.Protection

should return a true or false to let you know if the project is protected HOWEVER I can think of no way of trapping the unprotect event.... Rgds
~Geoff~

 

[simoncpage]

thanks thats what I was looking at.....tricky one

 

[xlbo]

ooops - should've checked new postings b4 I posted Rgds
~Geoff~

 

[xlbo]

I s'pose it depends on how protected your workbook is
If the people you are sending to should not be changing anything ie merely looking at it, you could use the worksheet change event to check for worksheet and VBProject protection
ie if they change anything within the worksheet, they must've taken protection off...... Rgds
~Geoff~

 

[simoncpage]

I was hoping that to actually capture when the vba was unlocked since otherwise it can be unlocked and taken out before it has any effect.

I have tried some of the software out there for when you "forget" your password and they are very good! most of the time brute force isnt need to unlock them and they are simply picked straight out - even for 16+ digits with upper lower and number/symbol variations.

I guess its something that I am going to have to live with at least the coding hasnt got any comments (to save on size) so its does make it a little harder to workout but still...Im convinced there is a solution! I'll let you know what I do with this and post back when completed with a website link to it to have a look.

 

[simoncpage]

Geoff this doesnt work...what do I need to change this to?

In worksheet_Change
*****If activesheet.protected = false then*****
activeworkbook.close savechanges:=false
else
end if
end sub

thanks

Simon
ps almost finished now

 

[xlbo]

Gotta test the different states of protection:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveSheet.ProtectContents = False Then
MsgBox "Contents"
ElseIf ActiveSheet.ProtectDrawingObjects = False Then
MsgBox "Drawing Objects"
ElseIf ActiveSheet.ProtectScenarios = False Then
MsgBox "scenarios"
ElseIf ActiveSheet.ProtectStructure = False Then
MsgBox "structure"
ElseIf ActiveSheet.ProtectWindows = False Then
MsgBox "Windows"
Else
End If
End Sub

HTH Rgds
~Geoff~