Help with a VBA code

[Closed]
Report
Posts
7
Registration date
Thursday November 29, 2012
Status
Member
Last seen
December 5, 2012
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I'm trying to configure the DEL key to clear the contents and Unlocked the cell (if Locked). I create the module:

Public Sub MyDelKey()
ActiveCell.Locked = False
ActiveCell.ClearContents
End Sub

and then on the Workbook_Open event I tried to call the module:

Private Sub Workbook_Open()
Application.OnKey "{DELETE}", "MyDelKey"
End Sub

I got an error message saying that is unable to run the macro. I check the settings and I'm allowing all the macros to run. Any help will be appreciated;

Thanks in advance!

6 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Worked for me. what version of excel you are using on. I tried on 2003

I copied your code "Public Sub MyDelKey() " in a new module
and "Private Sub Workbook_Open() " in "ThisWorkbook"
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Amedotero,

You are trying to unlock a cell while the sheet is still protected. Don't know how Rizvisa was able to make it work.

First unlock sheet > unlock cell > clear contents > lock cell > lock sheet.
Public Sub MyDelKey()
ActiveSheet.Unprotect
ActiveCell.Locked = False
ActiveCell.ClearContents
ActiveCell.Locked = True
ActiveSheet.Protect
End Sub

Best regards,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Trowa, i also did what you did. I missed to state that. :)
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Good to hear, thought I might have missed something.

Take care.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
See this file
http://speedy.sh/3JdrQ/amedotero..xlsm

I tested it on Win 7 / office 2010
On delete, it should put "test" in the cell


This was my code that i added in a Module
Public Sub MyDelKey()
    With ActiveSheet
        .Unprotect
        With ActiveCell
            .Locked = False
            .ClearContents
            .Value = "test"
        End With
        .Protect
    End With
End Sub


This was code in "ThisWorkbook"
Private Sub Workbook_Open()
    Application.OnKey "{DELETE}", "MyDelKey"
End Sub
Posts
7
Registration date
Thursday November 29, 2012
Status
Member
Last seen
December 5, 2012

thanks for the comments.

@rizvisa1 I'm using excel 2010

@ TrowaD
Thanks for the correction but still have the same problem. Excel just doesn't run the macro MyDelKey. That's the main problem I have.
Posts
7
Registration date
Thursday November 29, 2012
Status
Member
Last seen
December 5, 2012

the problem I'm having is that excel can't get to the code. I'm unable to test it because I got an error message that says "Cannot run macro 'MyDelKey'. Macro may not be available or all macros may be disable"
I went to Macro Security and enable all macros but still have the problem. Don't know what to do next.
Thanks!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
did you try the sample book ? Did you face same issue ?
Posts
7
Registration date
Thursday November 29, 2012
Status
Member
Last seen
December 5, 2012

yep.. I just did it and I'm having the same issue. This problem is driving me crazy!
thanks for the feedback
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
whats is exact error msg text?
Posts
7
Registration date
Thursday November 29, 2012
Status
Member
Last seen
December 5, 2012

"Cannot run the mocaro C:\User\969678\Desktop\OtProject.xlsm'!MyDelKey'.The macro may not be available on this workbook or all the macros may be disable"

that's the message. I might try it at home maybe my company' policy has disabled the use of macros. Could that be the reason? If so, why I'm able to change settings on Macro security?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
well that would be easy to test. Why not just run the macro manually to see if it clears the active cell or not. The error msg seems to suggest to me that either macro is not available in active workbook module. But you said that you have downloaded and tested the sample book that I uploaded. I was able to delete the content in that workbook and you said you got an error doing same.