• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fixed position of Grouped shapes (Textbox, pictures, etc.) while scrolling the screen

inddon

Member
Hello There,

I have a grouped shapes which consists of textboxes and pictures.
I would like the following:
1. The Grouped Shapes to stay intact while scrolling the screen.

I tried enabling the property of Don't move with cells, in vain. Also, came across a YouTube video where this is done using UserForm. I would prefer using shapes on the worksheet.

I have attached the sample workbook for your reference.

Look forward to hearing from you

Regards,
Don
 

Attachments

p45cal

Well-Known Member
I haven't done it with scrolling (you might want to try http://www.cpearson.com/EXCEL/DetectScroll.htm) but the attached has an annoying habit of moving the group to the bottom right of the selected cell(s).
There's an alternative version; just change the name of the macro to be:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
and deactivate the other one by prefixing its name with any letters, eg.
Private Sub qqqWorksheet_SelectionChange(ByVal Target As Range)
Only one can be active at any time.
 

Attachments

inddon

Member
I haven't done it with scrolling (you might want to try http://www.cpearson.com/EXCEL/DetectScroll.htm) but the attached has an annoying habit of moving the group to the bottom right of the selected cell(s).
There's an alternative version; just change the name of the macro to be:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
and deactivate the other one by prefixing its name with any letters, eg.
Private Sub qqqWorksheet_SelectionChange(ByVal Target As Range)
Only one can be active at any time.

Hello There,

Thank you for your solution, it looks good. I think I can use it for a different requirement.

Please find attach the modified sample for your reference. Before the Freeze Pane, is a Label. When the user scroll's up or down with mouse or key board, the Grouped Text Boxes should always stay below the Label. Thus making it visible at all times.
If the user is scrolling the window and the first visible row is 25 then it should display the box at row 25

I came across the below information:
https://social.msdn.microsoft.com/Forums/en-US/a6cff632-e123-4190-8556-d9f48af8fe9a/identify-first-visible-row-of-scrolled-excel-worksheet?forum=isvvba

Using the above link, and your solution is it possible to combine the two.?
1. The Label's Top and Left positions are fixed
2. First visible row can be found by the above link


Regards,
Don
 

Attachments

p45cal

Well-Known Member
I came across the below information:
https://social.msdn.microsoft.com/Forums/en-US/a6cff632-e123-4190-8556-d9f48af8fe9a/identify-first-visible-row-of-scrolled-excel-worksheet?forum=isvvba

Using the above link, and your solution is it possible to combine the two.?
1. The Label's Top and Left positions are fixed
2. First visible row can be found by the above link
I was already using the ActiveWindow.visiblerange in the 'alternative' solution in my first response. You can modify it to keep the shapes in line with the label in the frozen section:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Shapes("Group 9")
  .Top = ActiveWindow.VisibleRange.Top
  .Left = Shapes("Label1").Left
End With
End Sub
See attached.
Again, it happens not on scrolling but on selection.
I foresee problems when someone selects a cell and the shapes end up on top of the selected cell.
 

Attachments

Marc L

Excel Ninja
I moved it above the freeze pane, half way through. When I scroll it, it cuts the remaing part of the the Group
If well moved and pane as tall than the necessary height needed (very basic logic indeed)​
so just raise the height of the first 3 rows (to 48 for example) then no issue like on my side …​
 

inddon

Member
I was already using the ActiveWindow.visiblerange in the 'alternative' solution in my first response. You can modify it to keep the shapes in line with the label in the frozen section:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Shapes("Group 9")
  .Top = ActiveWindow.VisibleRange.Top
  .Left = Shapes("Label1").Left
End With
End Sub
See attached.
Again, it happens not on scrolling but on selection.
I foresee problems when someone selects a cell and the shapes end up on top of the selected cell.
Thank you for your solution
 

inddon

Member
If well moved and pane as tall than the necessary height needed (very basic logic indeed)​
so just raise the height of the first 3 rows (to 48 for example) then no issue like on my side …​
Thank you Marc for your response. In p45cal solution and a bit tweek, I got it working.

Regards,
Don
 
Top