Help with a VBA code

Closed

amedotero Posts 7 Registration date Thursday 29 November 2012 Status Member Last seen 5 December 2012 - 3 Dec 2012 à 14:42
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 6 Dec 2012 à 07:49

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 responses

Réponse 1 / 6

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
3 Dec 2012 à 20:16

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"

Réponse 2 / 6

TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
4 Dec 2012 à 10:35

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

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
4 Dec 2012 à 11:06

Trowa, i also did what you did. I missed to state that. :)

TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
4 Dec 2012 à 11:20

Good to hear, thought I might have missed something.

Take care.

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
4 Dec 2012 à 13:48

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

Réponse 3 / 6

amedotero Posts 7 Registration date Thursday 29 November 2012 Status Member Last seen 5 December 2012
4 Dec 2012 à 13:34

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.

Réponse 4 / 6

amedotero Posts 7 Registration date Thursday 29 November 2012 Status Member Last seen 5 December 2012
4 Dec 2012 à 14:08

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!

Réponse 5 / 6

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
4 Dec 2012 à 14:21

did you try the sample book ? Did you face same issue ?

Réponse 6 / 6

amedotero Posts 7 Registration date Thursday 29 November 2012 Status Member Last seen 5 December 2012
4 Dec 2012 à 16:35

yep.. I just did it and I'm having the same issue. This problem is driving me crazy!
thanks for the feedback

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
4 Dec 2012 à 22:28

whats is exact error msg text?

amedotero Posts 7 Registration date Thursday 29 November 2012 Status Member Last seen 5 December 2012
5 Dec 2012 à 11:31

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

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
6 Dec 2012 à 07:49

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.

Similar discussions

lava hard reset & factory reset

How to unlock Gameloft games

Samsung mobiles Nitro racing code

Using VBA Code to extract data from a website

How to code unlock ninja up for intex phone

Similar discussions

Subscribe To Our Newsletter!