• 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

I

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

  • Sample Book.xlsx
    12.4 KB · Views: 8
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

  • Chandoo42256Sample Book.xlsm
    22.2 KB · Views: 10
p45cal said:
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.
Click to expand...


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/F...-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

  • Chandoo42256Sample Book.xlsm
    27.5 KB · Views: 4
inddon said:
I came across the below information:
https://social.msdn.microsoft.com/F...-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
Click to expand...
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

  • Chandoo42256Sample Book-1.xlsm
    26.8 KB · Views: 13
inddon said:
I moved it above the freeze pane, half way through. When I scroll it, it cuts the remaing part of the the Group
Click to expand...
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 …​
​
 
p45cal said:
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.
Click to expand...

Thank you for your solution
 
Marc L said:
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 …​
​
Click to expand...
Thank you Marc for your response. In p45cal solution and a bit tweek, I got it working.

Regards,
Don
 
You must log in or register to reply here.
Back
Top