Unclickable shape

Question

I need to create a shape (say rectangle) in a given worksheet that will display in the said worksheet but will be totally inert to all action except VBA manipulation. Specifically it should not be selectable or even clickable in the sense that when the user clicks onto the shape, whatever is behind it (typically a cell) gets normally activated.

The property .visible=false seems to achieve this result, except that the object is, quite naturally, not visible to the user... Are there any means to achieve the same behaviour but leave the object visible?

Edit: I am actually trying to buid my own selection highlight, meaning that when I click on a cell, the corresponding column and row will be highlighted. The typical method for this is conditional formatting, but that will get messy easily. Direct event formatting obviously does not work either because that will mess up regular formatting.

So what came to my mind is some sort of transparent overlay - I think I could achieve this fairly reasonably with two rectangles that will dynamically change shape and position based on selection, however the problem is that if I want to click on any cell in the same column or row, it would be covered by the rectangle and I would click on that instead.

Answer 1

Edit: I am actually trying to buid my own selection highlight, meaning that when I click on a cell, the corresponding column and row will be highlighted. The typical method for this is conditional formatting

No it is not :) Here is something from an Add-In that I am creating for Excel.

To achieve what you want, we will use a very simple technique. But first you need to understand the logic behind this code.

Open MS Excel and then select a Column and then press the CTRL key and then select the Row to select the range (mimic the highlighter). It will look something like this

The same thing can be achieved using Range("B:B,4:4").Select

Once that is done, If we can somehow get the Selection to the relevant cell while keeping the highlighters on then our job will be done.

Logic:

1. Get the column name from the selection
2. Get the row from the selection
3. Construct a new address of the selection for example B:B,4:4
4. Select the new range
5. Activate (and not select) the original selection
6. Use the code at every click on the cell so we will use Worksheet_SelectionChange
7. Important note: Since we are using Worksheet_SelectionChange, we will have to switch off Events else the Activate (point 5 above) will fire Worksheet_SelectionChange again

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range
    Dim colName As String
    Dim RowName As String
    Dim adr As String

    On Error GoTo Whoa
    Application.EnableEvents = False

    Set aCell = Selection

    colName = Split(Cells(, aCell.Column).Address, "$")(1) & _
              ":" & _
              Split(Cells(, aCell.Column).Address, "$")(1)

    RowName = aCell.Row & ":" & aCell.Row

    '~~> Creating a range in this format B:B,4:4
    adr = RowName & "," & colName

    Range(adr).Select

    aCell.Activate

Whoa:
   Application.EnableEvents = True
End Sub

In Action:

Important Note:

When you want to work with multiple cells, or other stuff then switch off highlighter either using a button or simply by commenting out the code. Best to create a small Add-In to switch the highlighter on or off