CODEWITHSUNDEEP
excelvba
Pramod Pandit
Pramod Pandit

Reputation: 121

How to select cells behind the shapes in spreadsheet?

I have a spreadsheet which have Dotted lines as a shapes.When i try to select the cells behind those shapes (highlighted in attached picture), these shapes come in the way.Is there a way to put those shapes behind the cells or select the cells without obstruction from shapes so that i can put data on the cells behind the shape ?

Some of the ideas that i have come up with are:

Highlighted area cells are the cells i need

Upvotes: 0

Views: 2819

Answers (4)

excelCoder321
excelCoder321

Reputation: 36

Here's the hide/unhide shape solution. This code will fetch the cursor's x-y coordinate position, very briefly hide the shape (or picture) to select the cell under your cursor, and then immediately unhide the shape. The shape might flash away for a split second (especially for larger shapes), but it's not too bad. I'm using this to work under translucent pictures.

Just edit the shape names in the code, and assign the SelectUnderShape macro to your shape.

    Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) _
       As Long
    Type POINTAPI
       X As Long
       Y As Long
    End Type
    Sub SelectUnderShape()
       Dim pos As POINTAPI
       GetCursorPos pos
       ActiveSheet.Shapes("your_shape/picture_name").Visible = False
       Application.Wait Now + 0.00000001
       ActiveWindow.RangeFromPoint(pos.X, pos.Y).Select
       ActiveSheet.Shapes("your_shape/picture_name").Visible = True
    End Sub

Upvotes: 1

Manoj Kumar
Manoj Kumar

Reputation: 11

SelectUnderShape macro provided by John Simmons worked. Thanks a lot for the post.

It took a while to get it running, but it works.

For the novice, find the name of the object in your worksheet and substitute it in the VBA code, in both places. Use Shift Arrow keys to select a Range from the first cell clicked.

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42256

  1. You cannot put the shapes behind the cells.
  2. You can select cells behind the shape, but avoiding to chick on the shape. Click nearby and move on the sheet using Arrow keys, as @Gary's Student mentioned in his answer.
  3. Since you mentioned VBA like the question tag, you can use VBA to put data, without selecting the cell.

  4. I do not understand the necessity of these dotted lines shapes, but since we are in the VBA section, I would suggest a code like the following one, which creates cells range dotted border, avoiding, in this way, to use shapes:

    Sub CreateDottedlinesBorder() Dim sh As Worksheet, rng As Range Set sh = ActiveSheet Set rng = sh.Range("A1:D16") With rng With .Borders(xlEdgeLeft) .LineStyle = xlDashDotDot .Weight = xlThin End With With .Borders(xlEdgeTop) .LineStyle = xlDashDotDot .Weight = xlThin End With With .Borders(xlEdgeBottom) .LineStyle = xlDashDotDot .Weight = xlThin End With With .Borders(xlEdgeRight) .LineStyle = xlDashDotDot .Weight = xlThin End With .Borders(xlInsideVertical).LineStyle = xlNone .Borders(xlInsideHorizontal).LineStyle = xlNone .Copy End With rng.Offset(, rng.Columns.Count).PasteSpecial Paste:=xlPasteFormats End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

The easiest is select (click-on) an adjacent cell and then"slide" into the cell you want using the ARROW keys.

(You can use the Name Box to make sure you are in the right place)

Upvotes: 1

Related Questions