OnKey or KeyDown withing Worksheet?

[deeppurple247]

Hi,

I have this code which puts a top border around specific cells.

 
Worksheets("Sheet1").Activate
  'Can't select unless the sheet is active

If ActiveCell.Value <> Selection.Offset(1, 0) Then

ActiveCell.Borders.Item(xlEdgeBottom).Weight = xlMedium

Selection.Offset(1, 0).Select

Else: Selection.Offset(1, 0).Select
End If

It works fine when attached to a button but I want it to be assigned to either 'OnEnter' or 'Keydown' within the worksheet

Any ideas?

Thanks

Alex

[Carim]

Hi,

When manipulating Excel menus and keys, make sure to reinstate original function upon leaving (on desactivate event)

Application.OnKey "{Enter}", yourmacro

[deeppurple247]

How do you attach the code to the following ?

Application.OnKey "{Enter}", yourmacro

[Carim]

Sorry for not being clear enough ...
yourmacro stands for the name you have to your macro ...

1. Attaching is
Application.OnKey "{Enter}", thenameofyourmacro

2. Detaching is
Application.OnKey "{Enter}"

[deeppurple247]

Thats great thanks, but when I put...

Application.OnKey "{Enter}", "Macro1"

Application.OnKey "{Enter}"


The Macro doesn't work and pressing the {Enter} key simply moves the cursor down as normal?

[Carim]

Again sorry ...
I assumed you had already dealt with such an issue ...

You need an event macro at the workbook level or at the worksheet level to enable and disable the onkey instruction ...

Private Sub Worksheet_Activate()
Application.OnKey "{Enter}", "Macro1"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{Enter}"
End Sub

Just in case, worksheet events are to be stored in the respective sheet module