1

I want a MsgBox to appear when cell A1 is clicked. I want it to appear even if A1 is already active when it is clicked:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.EnableEvents = False

  If Target.Row = 1 And Target.Column = 1 Then
    MsgBox ("message")
  End If

  Application.EnableEvents = True
End Sub

This code works only if cell A1 is not already selected when I click on it. Currently the message box does not appear in this case.

Is there a way to fix this?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Ans
  • 1,212
  • 1
  • 23
  • 51
  • Would double-click instead of click work for you? – ashleedawg Aug 24 '18 at 12:34
  • *"I want it to appear even if that cell is already active and is being clicked on"* This won't work because the event is called `SelectionChange` which means the selection must change to trigger the event. – Pᴇʜ Aug 24 '18 at 12:35
  • @ashleedawg It's better than nothing, even though the one-click is preferable – Ans Aug 24 '18 at 12:40
  • Why not just use a button? – Josh Eller Aug 24 '18 at 12:53
  • Take a look [here](https://stackoverflow.com/q/29758475/2165759) and [here](https://stackoverflow.com/q/47271141/2165759). – omegastripes Aug 24 '18 at 12:54
  • 1
    @Ans - note that in your code example, the two lines enabling & disabling events (with `Application.EnableEvents`) will have no effect and are unnecessary. This would only be needed if the code within the event will/might trigger *another* event. – ashleedawg Aug 24 '18 at 13:41
  • @Josh Eller What do you mean by that? Place a button with "A1 info" text near cell A1 or something? – Ans Aug 24 '18 at 14:26

1 Answers1

4

Your code is using Worksheet_SelectionChange which only fires when a different cell is selected (hence the name Selection Change).

Alternatively, if it's okay if your [unknown] goal is attained using double click or right click then there are other worksheet events that will help:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox Target.Address & " was double clicked"
    Cancel = True 'don't edit cell
End Sub

 

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox Target.Address & " was right clicked"
    Cancel = True 'don't open context menu
End Sub

Note that the code for these event procedures need to be placed in the worksheet module.

Edit: More creative ways

Click Event via PeekMessage API

  • If it must be a single click, there are "sneakier" ways to accomplish this, such as adding a Click event. This is not a built-in feature of Excel VBA, and thus, this method is not generally recommended.

    It involves checking for the WM_MOUSEMOVE message when a cell is mouse-clicked, which is accomplished by calling the PeekMessage API inside the Worksheet_SelectionChange event. More info and examples here.

Transparent command button

  • There could also be a round-about way to accomplish this using an ActiveX Command Button with no caption, with the BackStyle property set to frmBackStyleTransparent.

Neither of these methods have been tested and you might need to do some fancy coding to get them to work. Depending on how often the same cell will be clicked that is already selected (and therefore how that functionality is to you), you may want to simply re-think the layout of your worksheet.

For example, you could add an extra column and have the user click the cell next to the one with the value to activate your message box.

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105