format autoshape

[Guest]

hi,

I have some rectangle autoshapes in my spreadsheet that are transparent.
Although it looks cool, users cannot click on the cells behind it, so I have
been requested to get rid of the transparent autoshapes. Can anyone ell me
how to still have these transparent boxes in front of the cells while still
allowing the user to click on the cells?

Thanks in advance,
geebee

 

[Guest]

they will click on the shapes, not the cells.

 

[Guest]

If the box is only one cell in size, maybe you could assign a macro that
selects the cell below it.

 

[Bob Umlas]

You COULD try something like this which will make the shape disappear for 1
second, so a 2nd click (first runs the macro) can select the cell
underneath: (You have to assign the shape (any/all of them to the same
procedure) to Disappear
Dim AppCall
Sub Disappear()
AppCall = Application.Caller
ActiveSheet.Rectangles(AppCall ).Visible = False
Application.OnTime Now + 1 / 86400, "vis" '1 second later...
End Sub

Sub Vis()
ActiveSheet.Rectangles(AppCall ).Visible = True
End Sub

Bob Umlas
Excel MVP

 

[Bob Umlas]

Better:
Assign the shape(s) to "Disappear":
Public appcall
Sub Disappear()
appcall = Application.Caller
ActiveSheet.Rectangles(appcall).Visible = False
End Sub

Then, have a worksheet_SelecitonChange event to do this (so that the 1
second only delay no longer plays a part:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
ActiveSheet.Rectangles(appcall).Visible = True
End Sub

Bob Umlas
Excel MVP

 

[Peter T]

I don't understand the problem though it seems others do. If a rectangle's
fill is transparent it should be possible to select cells behind or through
the transparent area

at drawing object level -
activesheet.rectangles(1).interior.colorindex = xlnone

or as a shape -
activesheet.shapes(1).fill.visible = msofalse

However if it has a textframe then it's not possible to select 'through' the
transparent area.

Regards,
Peter T

 

[Guest]

I'm just going on what the OP stated:

Although it looks cool, users cannot click on the cells behind it, so I have
been requested to get rid of the transparent autoshapes.

 

[Peter T]

Perhaps the OP's rectangles have Transparency 100% rather than no-fill.
Visually the same though only the latter allows selection under the
rectangle.

If the OP wants to make all rectangles transparent yet allow select cells
through the transparent area simply -

Activesheet.ActiveSheet.Rectangles.ShapeRange.Fill = msoFalse
(no need to change transparency)

Following toggles threes states -

Sub SeeThroughShapes(nState As Long)
Dim s As String
Select Case nState
Case 0: s = "select through"
Case 1: s = " transparent but not select through"
Case 2: s = "solid"
End Select

On Error Resume Next
Set shr = ActiveSheet.Rectangles.ShapeRange
If shr Is Nothing Then Exit Sub
With shr.Fill
.Visible = CBool(nState)
.Transparency = Abs(CBool(nState < 2))
End With
MsgBox s, , "nState " & nState
End Sub

Sub test2()
Static n&
SeeThroughShapes n
n = n + 1
If n > 2 Then n = 0

End Sub

Regards,
Peter T

 

[Peter T]

Typo -

If the OP wants to make all rectangles transparent yet allow select cells
through the transparent area simply -

Activesheet.ActiveSheet.Rectangles.ShapeRange.Fill = msoFalse

forgot the .Visible -

Activesheet.ActiveSheet.Rectangles.ShapeRange.Fill.Visible = msoFalse

Peter T