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

they will click on the shapes, not the cells.

--
Regards,
Tom Ogilvy


"geebee" wrote:

> 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
>

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

--
Regards,
Tom Ogilvy


"geebee" wrote:

> 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
>

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

"geebee" <(E-Mail Removed)> wrote in message
news41BA010-8274-4B4B-924D-(E-Mail Removed)...
> 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
>

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

"geebee" <(E-Mail Removed)> wrote in message
news41BA010-8274-4B4B-924D-(E-Mail Removed)...
> 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
>

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

"geebee" <(E-Mail Removed)> wrote in message
news41BA010-8274-4B4B-924D-(E-Mail Removed)...
> 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
>

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.

--
Regards,
Tom Ogilvy

"Peter T" wrote:

> 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
>
> "geebee" <(E-Mail Removed)> wrote in message
> news41BA010-8274-4B4B-924D-(E-Mail Removed)...
> > 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
> >
>
>
>

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


"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:0C49D971-7FB5-47C3-A82F-(E-Mail Removed)...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Peter T" wrote:
>
> > 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
> >
> > "geebee" <(E-Mail Removed)> wrote in message
> > news41BA010-8274-4B4B-924D-(E-Mail Removed)...
> > > 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
> > >
> >
> >
> >

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


"Peter T" <peter_t@discussions> wrote in message
news:#(E-Mail Removed)...
> 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
>
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:0C49D971-7FB5-47C3-A82F-(E-Mail Removed)...
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Peter T" wrote:
> >
> > > 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
> > >
> > > "geebee" <(E-Mail Removed)> wrote in message
> > > news41BA010-8274-4B4B-924D-(E-Mail Removed)...
> > > > 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
> > > >
> > >
> > >
> > >
>
>