Previous Topic Next Topic

Help with a VBA code

Closed
amedotero Posts 7 Registration date Thursday November 29, 2012 Status Member Last seen December 5, 2012 - Dec 3, 2012 at 02:42 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Dec 6, 2012 at 07:49 AM
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!
Related:

6 responses

Answer 1 / 6
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 3, 2012 at 08:16 PM
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"
0
Answer 2 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 4, 2012 at 10:35 AM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 4, 2012 at 11:06 AM
Trowa, i also did what you did. I missed to state that. :)
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 4, 2012 at 11:20 AM
Good to hear, thought I might have missed something.

Take care.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 4, 2012 at 01:48 PM
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
0
Answer 3 / 6
amedotero Posts 7 Registration date Thursday November 29, 2012 Status Member Last seen December 5, 2012
Dec 4, 2012 at 01:34 PM
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.
0
Answer 4 / 6
amedotero Posts 7 Registration date Thursday November 29, 2012 Status Member Last seen December 5, 2012
Dec 4, 2012 at 02:08 PM
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!
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 4, 2012 at 02:21 PM
did you try the sample book ? Did you face same issue ?
0
Answer 6 / 6
amedotero Posts 7 Registration date Thursday November 29, 2012 Status Member Last seen December 5, 2012
Dec 4, 2012 at 04:35 PM
yep.. I just did it and I'm having the same issue. This problem is driving me crazy!
thanks for the feedback
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 4, 2012 at 10:28 PM
whats is exact error msg text?
0
amedotero Posts 7 Registration date Thursday November 29, 2012 Status Member Last seen December 5, 2012
Dec 5, 2012 at 11:31 AM
"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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 6, 2012 at 07:49 AM
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.
0

Similar discussions

lava hard reset & factory reset
AVAY -
Har -

3 responses
Unlock code
Wwe -
Sahilkarate -

1 response