Make "group" of shapes static while scrolling?

[PrimePorkchop]

I have a group of shapes that I have created into a "navigation" payne... I'd like to keep it in a particular spot on the screen even when you scroll down

I found an example from google searching for accomplishing this with a graph... But I could not figure how to do it for a group

[vba_php]

I have a group of shapes that I have created into a "navigation" payne

what do you mean by that? can you post a screenshot of this "group" you speak of? shapes are part of one of the many object collections in excel. in general, a lot of that stuff slides with the grid when new matrix elements are injected into a sheet (e.g. - rows, cols). the one thing that comes to mind here is freezing the frame where the group is located, but I doubt that will work. I don't think freeze frames gives an option to freeze any given area. but it's been a while since I've done it.

[PrimePorkchop]

It's a bunch of rectangle shapes "Grouped" into something called "Group 8" (auto-named by Excel)

[vba_php]

I tested all of this, and it seems to work fairly well. although it might not be perfect. this is about all I have to offer you, although I'm sure there is another solution out there somewhere:

https://www.google.com/search?q=excel+group+of+shapes

https://www.google.com/search?q=exce...hile+scrolling

https://www.google.com/search?q=exce...up+keep+static

SOLUTION FOUND:
https://www.mrexcel.com/board/thread...lling.1053202/

[PrimePorkchop]

I forgot to mention, any solution with "Freeze Frames" default through excel simply won't work for my worksheet setup. I'm starting to think this might only be doable with VBA?

[vba_php]

well no doubt you probably can, but guess is that you don't want a ""freeze frames"" solution, it might take quite a bit of research to figure out. I think I've assisted all I can here. but I wish you luck with it.

[Greg M]

Hi there,

As the attached link to a Chip Pearson web page shows, detecting scrolling (via scrollbars or mouse wheel) in a worksheet window is definitely NOT for the faint-hearted!

http://www.cpearson.com/excel/DetectScroll.htm

A very much second-best approach enables you to detect a "scroll" caused by the PgUp or PgDn keyboard keys. Using this approach you can position a shape (which may be a group of shapes) at a specific location within the active window.

The following code is inserted in the VBA CodeModule of the worksheet which contains the shape to be moved. The code positions the shape relative to the top-left cell of the active window, and may be offset from this position as required:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[PrimePorkchop]

Greg - this works almost perfectly - in order to get the "Group" to snap back into place, relative to the position on the screen, I have to "click" in another cell... simply scrolling alone does not keep the "Group" in position.

I wonder if there's a way to tell excel to do this when scrolling happens, instead of clicking?

[Greg M]

Hi again,

Thanks for your feedback. I did say that my approach was very much "second best".

The best way to respond to:

I wonder if there's a way to tell excel to do this when scrolling happens, instead of clicking?

is to quote from the web page I mentioned in my previous post,

Using a technique called "subclassing", you can detect when the user scrolls in a worksheet window. Subclassing is a complicated topic, and will be described here only in the most general, oversimplified, terms.

When Chip Pearson describes something as "complicated" you can be sure of one thing - it is DEFINITELY complicated!

I took a quick look at what would be involved and came to the conclusion that I would attempt it only if I really needed it for my own purposes - so far, I don't need it. Sorry


Regards,

Greg M

[p45cal]

Consider putting the buttons on a modeless userform.
In the attached is such a userform.
If it doesn't appear then switch to Sheet1 from another sheet.
I've not bothered positioning it etc., but it remains where you leave it and allows you to edit the sheet normally while it's still showing. Yes you can prevent the user from closing it and you can cause it to disappear when you switch to another sheet.

[PrimePorkchop]

Hi again,

Thanks for your feedback. I did say that my approach was very much "second best".

The best way to respond to:

is to quote from the web page I mentioned in my previous post,

When Chip Pearson describes something as "complicated" you can be sure of one thing - it is DEFINITELY complicated!

I took a quick look at what would be involved and came to the conclusion that I would attempt it only if I really needed it for my own purposes - so far, I don't need it. Sorry


Regards,

Greg M

I completely overlooked that part of the site Greg, my apologies! Thank you for all the time you spent trying to help out, I surely do appreciate it!!

[Greg M]

Hi again,

Many thanks for that further feedback.

Sorry I wasn't able to help more.

Regards,

Greg M

[Greg M]

Hi p45cal,

That seems like a really neat approach which never even occurred to me.

It just goes to show that there's always more than one way to skin a cat when you're using Excel!

Regards,

Greg M

[PrimePorkchop]

Consider putting the buttons on a modeless userform.
In the attached is such a userform.
If it doesn't appear then switch to Sheet1 from another sheet.
I've not bothered positioning it etc., but it remains where you leave it and allows you to edit the sheet normally while it's still showing. Yes you can prevent the user from closing it and you can cause it to disappear when you switch to another sheet.

The userform doesn't have the ability to create the same style I wanted, but that's okay - I'd rather have the functionality over the aesthetics. This works brilliantly. Thank you!

[p45cal]

The userform doesn't have the ability to create the same style I wanted

There are many ways the userform's appearance can be changed (and it's controls).
I noticed that you had a picture of your shapes, but that's gone - if you re-inState that pic I may be able to go some way to copying it.
Eg. a pic can be put on a button, or even just as a picture by itself - you can even control what happens depending where within the button or picture the mouse button is clicked. With some jiggery-pokery you can also change the shape of the overall userform (circle, triangle, rhomboid).
Here's a pic of a button with my own picture on it:
Attachment 697719[IMG]697720[/IMG]