Moving Shape

[K]

Hi all, I got Rectangle shape on my sheet and I want vba with which
shape should move on sheet whenever a user use active window scroll.
In other words when user move active window scroll up and down the
shape should also move with it and shape should always be appear or
visible in visible area of sheet. I tried doing search on this forum
but couldn't find any useful answer for my question. I know there are
lot of vba expert friends out there who know the sultion for my
question so can any friend help me on this. Many thanks in advance.

 

[Peter T]

Scrolling the window does not trigger any events (at least none exposed to
Excel vba).

You could move the shape, if necessary, the next time user selects a cell.

' in the sheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shp As Shape

On Error Resume Next
Set shp = Me.Shapes("Rectangle 1")
On Error GoTo errExit

If shp Is Nothing Then
Exit Sub
End If

With ActiveWindow
If Intersect(.VisibleRange, shp.TopLeftCell) Is Nothing Then
With .VisibleRange(2, 2)
shp.Left = .Left + 3#
shp.Top = .Top + 3#
End With
End If
End With

errExit:

End Sub

Change the name of the shape to suit and the default position relative to
the top left visible cell. Alternatively could position relative to the new
activecell.

Regards,
Peter T

 

[scattered]

Hi all,  I got Rectangle shape on my sheet and I want vba with which
shape should move on sheet whenever a user use active window scroll.
In other words when user move active window scroll up and down the
shape should also move with it and shape should always be appear or
visible in visible area of sheet. I tried doing search on this forum
but couldn't find any useful answer for my question.  I know there are
lot of vba expert friends out there who know the sultion for my
question so can any friend help me on this. Many thanks in advance.

Hi

I ran into this sort of problem recently and like Peter discovered
that there did not seem to be any good solution. In my case the
purpose of the rectangle was simply to trigger a macro when clicked.
For this purpose I found a workaround which is ok albeit not ideal. I
created a small modeless userform (one that allows you to use Excel
without needing to dismiss the form) with a command button (you could
use a shape in an image control to mimic yhe effect you want as much
as possible) and positioned the form where I wanted. The two crucial
property settings are StartUpPosition = 0 (manual) and ShowModal =
False. I used a combination of Workbook_Open and WorkSheet_Activate
event procedures to show the form (and WorkSheet_Deactivate to hide it
when going to a different page. There might be a way to prevent the
form from being closed other than by moving to a different sheet or
closing the workbook. In any event - the form does stay in place
during scrolling (although its appearance does change a bit when it
doesn't have the focus).

Another possibility is to create a floating toolbar with a button
which launches your macro. I went the form route since I was more
familar with them and wanted to easily control size and placement at
design time.

If you had another reason for wanting a floating rectangle this
suggestion probably doesn't help too much.

hth

-scattered

 

[K]

Hi

I ran into this sort of problem recently and like Peter discovered
that there did not seem to be any good solution. In my case the
purpose of the rectangle was simply to trigger a macro when clicked.
For this purpose I found a workaround which is ok albeit not ideal. I
created a small modeless userform (one that allows you to use Excel
without needing to dismiss the form) with a command button (you could
use a shape in an image control to mimic yhe effect you want as much
as possible) and positioned the form where I wanted. The two crucial
property settings are StartUpPosition = 0 (manual) and ShowModal =
False. I used a combination of Workbook_Open and WorkSheet_Activate
event procedures to show the form (and WorkSheet_Deactivate to hide it
when going to a different page. There might be a way to prevent the
form from being closed other than by moving to a different sheet or
closing the workbook. In any event - the form does stay in place
during scrolling (although its appearance does change a bit when it
doesn't have the focus).

Another possibility is to create a floating toolbar with a button
which launches your macro. I went the form route since I was more
familar with them and wanted to easily control size and placement at
design time.

If you had another reason for wanting a floating rectangle this
suggestion probably doesn't help too much.

hth

-scattered

Thanks Peter for you macro.

Thanks Scattered for you comments. Just have a question that how can
i create floating toolbar please can you give me any help on this and
also that is it possible for you to send me you macro the one you use
for userform. i'll be very greatful to you for this.