Consulting

Results 1 to 2 of 2

Thread: Call shortcut only in specific column

  1. #1
    VBAX Newbie
    Joined
    Sep 2020
    Posts
    1
    Location

    Lightbulb Call shortcut only in specific column

    Hi,

    I don't know how to create in VBA my own shortcut which will call only when my active cell is in specific column.

    I use Application.OnKey mothod but it works only for all workbook.

    For example: when my active cell is in column F i want run macro by pressing ENTER. In other column ENTER shoud works normalny

    Thans in advance !

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Say you have the line executed once somewhere:
    Application.OnKey "~", "blah"
    and blah is a sub such as:
    Sub blah()
    If ActiveCell.Column = 6 Then
      MsgBox "Hi there!"
    End If
    End Sub
    in a standard code-module.
    That will almost work - the bugbear being that pressing the Enter key when not in column F doesn't quite behave normally.
    If you don't want to accept that then in the code-module of the sheet that you want this to work on, put this code:
    Private Sub Worksheet_Activate()
    If ActiveCell.Column = 6 Then Application.OnKey "~", "blah"
    End Sub
    
    Private Sub Worksheet_Deactivate()
    Application.OnKey "~"
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Column = 6 Then 'you can be more choosy here and even use Target.
      Application.OnKey "~", "blah"
    Else
      Application.OnKey "~"
    End If
    End Sub
    Which will disable the shortcut when you move away from the sheet and also when the activecell is not in column 6 (F).
    It will also enable the shortcut when you move to that tab and the active cell happens already to be in column 6.
    Also, if you use this second scenario you can probably do without the If clause in blah.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •