Floating Drawing Object

[CLR]

Hi All.........
If someone would be so kind.....I'm trying to find out how to make a Drawing
Object, (specifically "Rectangle 1") float on the screen like some of the
annoying pop-ups do, so it will always be on-screen as the user scrolls up
and down the sheet. I know about the Freeze line, but don't want that in
this case.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[NickHK]

AFAIK you can't do that with a shape, but you can with Modeless Userform.
Then put whatever you want on the userform.

NickHK

 

[CLR]

Thanks NickHK..........
I'm not familiar with those yet......I'll have to do some research.
Thanks for the tip.

Vaya con Dios,
Chuck, CABGx3

 

[Ken Johnson]

Hi All.........
If someone would be so kind.....I'm trying to find out how to make a Drawing
Object, (specifically "Rectangle 1") float on the screen like some of the
annoying pop-ups do, so it will always be on-screen as the user scrolls up
and down the sheet. I know about the Freeze line, but don't want that in
this case.

TIA
Vaya con Dios,
Chuck, CABGx3

Hi Chuck,

This it pretty rough and probably just for a laugh...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.Shapes("Rectangle 1")
..Top = ActiveWindow.VisibleRange.Top + 100
..Left = ActiveWindow.VisibleRange.Left + 100
End With
End Sub


When the user scrolls the rectangle out of view it does move out of
view. However,
If the visible range is clicked then the rectangle reappears 100 points
from the top and left side.

Ken Johnson

 

[CLR]

That is OUTSTANDING Ken..........thanks a HEAP. I had a small problem of it
coming up behind other Drawing Objects but was able to figure out how to
bring it to the front and now life is good. It works perfectly, as you
described, and I really appreciate your help.

Vaya con Dios,
Chuck, CABGx3

 

[Ken Johnson]

That is OUTSTANDING Ken..........thanks a HEAP. I had a small problem of it
coming up behind other Drawing Objects but was able to figure out how to
bring it to the front and now life is good. It works perfectly, as you
described, and I really appreciate your help.

Vaya con Dios,
Chuck, CABGx3

Hi Chuck,

However, it is annoying when you want to select a cell that is
underneath Rectange 1, so I have made a version that prevents that
annoyance...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Box As Shape
Set Box = Me.Shapes("Rectangle 1")
If Selection.Left + Selection.Width _
+ Box.Width > Rows(1).Width Then
Box.Left = Selection.Left - Box.Width
Else: Box.Left = Selection.Left + Selection.Width
End If
If Selection.Top + Selection.Height _
+ Box.Height > Columns(1).Height Then
Box.Top = Selection.Top - Box.Height
Else: Box.Top = Selection.Top + Selection.Height
End If
Box.ZOrder msoBringToFront
End Sub

Ken Johnson

 

[CLR]

YES SIR!!!!!.........that is really really cool........thanks many bunches
Ken, you are a Prince of Programmers.

Vaya con Dios,
Chuck, CABGx3

 

[Ken Johnson]

Hi Chuck,

Glad you like it.
Thanks for those kind words, I'll pass them on to my superiors;-)

Ken Johnson

 

[CLR]

Indeed I do like what you have done for me...........and INDEED you are
worthy of the kind words, and more. Lots of folks can flip out a quick
answer, but your insight to my problem was keen. You responded in a timely
manner, and then you even took it a step further by actually trying out your
original code beyond your initial testing. Then, when discovering a
difficulty with it that would could turn out to be a user problem, you went
on to fix it and come up with a really neat working final piece and returned
to offer it in place of your original. I do sincerely appreciate your
efforts, your fine talents, and your character, all brought into play in
this instance. Many many thanks, and please stick around these newsgroups
as I will no doubt need more help in the future.

Vaya con Dios,
Chuck, CABGx3

 

[Ken Johnson]

Hi Chuck,

Thank you for those very kind words!

These newsgroups are easily the most effective way of improving one's
IT skills. My learning curve will go into a steep nose-dive should I
ever stop making use of them.

I look forward to reading you frequent replies to users' questions
and I admire your ability to find the simple solutions while I'm
struggling to get a clear view of the forest because the trees keep
getting in the way

Ken Johnson