Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Shape
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Shape
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 "K" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Shape
On Oct 29, 4:49*am, K wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Shape
On Oct 29, 1:55*pm, scattered wrote:
On Oct 29, 4:49*am, K wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
disable shape resizing or moving | Excel Programming | |||
Moving a shape with text inside without editing text | Excel Programming | |||
my curser changed from arrow shape to a cross shape???? | New Users to Excel | |||
Deleting a shape and the cell contents the shape is in. | Excel Programming | |||
Deleting a shape and the cell contents the shape is in. | Excel Programming |