Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Freeze Cells from scrolling?

0

Hello,

I was wondering if possible.

To freeze some cells that not ment to be for scrolling.

For example on Testmain2 we have on L9 picture under it with object picture

So now i like to freeze the L9 Till L 20 from scrolling and keep the image on his place.

I like to make the only scroll availibe for D:K 

So the picture will stay when we scroll down or up.

Thanks

Answer
Discuss

Discussion

Mmm. To be clear, is your idea to have different pictures for different selections (e.g Employees selected from cell G2)?

(If so, it would be easy to lift the picture into frozen rows at the top of that page) 
John_Ru (rep: 6102) Nov 28, '20 at 3:25 pm
Hello John_Ru
Yep that's the idea :)
So in the next part we are going to load data under it so every row/cell will be filled with data from the selected employee with the different image
GhostofWanted (rep: 46) Nov 28, '20 at 3:32 pm
So will you filter the rows on TESTMAIN2 to display data about only one employee (selected from G2)? 
John_Ru (rep: 6102) Nov 28, '20 at 3:37 pm
Yeah i think that's what we wanna do.
So any employee we select will get there data from on other sheet
to fill out D:K on sheet testmain2
GhostofWanted (rep: 46) Nov 28, '20 at 4:08 pm
What you ask might be possible to give the impression that the image isn't moving (via the scroll event somehow) but I haven't tried. It's getting late here so I'll try to look tomorrow (if you get no answer).

As I said, the simple, easy thing is to put the picture in the top rows where you can have the normal frozen panes (set manually or via VBA).

Don't forget to respond please to my last discussion point on the validation thread.
John_Ru (rep: 6102) Nov 28, '20 at 4:21 pm
Have selected your anwer on the previous question.

Also i have updated my workbook
with the sheet where we want to get the data from
Like we talked about here above ago.
And thanks already John_Ru
GhostofWanted (rep: 46) Nov 28, '20 at 5:00 pm
Thanks. Will look at updated file tomorrow 
John_Ru (rep: 6102) Nov 28, '20 at 5:25 pm
Just a thought to share while studying your project:- Hidden columns TESTMAIN2!A:B are sure to give you a headache sooner or later, and in many different ways, as you automate your project. Therefore it's better to get rid of them sooner rather than later. If the information you intend to store there affects the current session, store the data in VBA memory. If they need to be saved, create a hidden worksheet for that purpose. You may also consider using CustomDocumentProperties. But in my experience the need for such storage always evaporates as the project progresses. Therefore elaborate preparations are just a waste of time. Make such preparations in a way and in a place where they are easy to delete.
Variatus (rep: 4889) Nov 28, '20 at 7:25 pm
Add to Discussion

Answers

0

Regardless of whether you place your image in a Shape, an ActiveX control or a UserForm, it has to be repositioned on the screen when the scroll occurs. Unfortunately, Excel doesn't have an event that takes note of the Scroll event. Therefore there is no direct trigger that might cause the image to be moved.

It's possible to harness the Selection_Change event to this task but the resulting presentation isn't nice. The picture would scroll out of view and then reappear in its proper position only after the user clicks somewhere else. This sequence is probably self-defeating because the purpose of the picture is to assist the user in finding the correct click.

With the last possibility of meeting your requirement thereby removed there are two alternatives.

  1. Place the table D10:K20 in a modeless user form. Hide the scroll bars on the worksheet and enable scrolling of the table in the user form.
  2. Move the image to the top left of your form where it wouldn't be affected by scrolls due to the top part of the screen being frozen in place.

The programming involved in realising the first idea is substantial. Filling data into a form's controls takes much greater effort than filling them into a spreadsheet cell. Moreover, the number of rows in the form must be flexible, meaning controls must be created on the fly. All that promises a slightly sluggish process, not as smooth as pure Excel.

However, if the task were mine to implement I would be wary of using a modeless form. Basically, when a modal user form is shown it takes control of VBA until it's closed, when control referts to Excel. A modeless form stays open all the time. I neither know how control is divided between the two contestants - perhaps based on what is clicked, perhaps limited in scope - nor do I know what you require. In summary, you would be correct in saying that it's not a viable proposition.

That makes everything simple. Only one remaining option, and it's not difficult to implement.

Discuss

Discussion

Moring Variatus,

Userforms i like to ignore that option. Because the people at my work
Don't wanna use userforms anymore they just want sheets.. Why no idea
So that's why i rewrite my workbook.
we can add the images in the cell's like John_Ru has said before
Instead of preventing the scrolling option of the image.
Then i properly need to enlarge the cells to show the images not to big ofcourse :)
And variatus to answer your previous question
column A:B will be removed later :) we are just trying some options
Because for me it's also a little hard to write everything with formulas ect...
But we are here to learn from the best teachers around here :D
GhostofWanted (rep: 46) Nov 29, '20 at 2:13 am
Hllo Ghost,
I'm glad to see that we appear to have been able to advance your project a little.
Variatus (rep: 4889) Nov 29, '20 at 5:06 am
I have updated my project again.
Only the images into the cell i need to do somehow
And the tempory cell in A:B i also need to replace somehow
Let me know if we doing good so far ;)

Thanks
GhostofWanted (rep: 46) Nov 29, '20 at 5:15 am
You're doing beautifully! I'm looking forward to your next question :-)
Variatus (rep: 4889) Nov 29, '20 at 5:17 am
Aaah thank you variatus
Glad we are doing great because all of all the help of you guys :D
GhostofWanted (rep: 46) Nov 29, '20 at 5:24 am
Need some help please,
Updated file again.
Seems when i select Mike Floye in G2
It loads his data but i get on D10 field3 the image location and the row under it
It loads correct?
Any idea please
GhostofWanted (rep: 46) Nov 29, '20 at 5:51 am
Can't answer here: Forum rules. Please ask a new question.
Variatus (rep: 4889) Nov 29, '20 at 5:53 am
Add to Discussion
0

To Freeze Cells from scrolling follow this:

Select View > Freeze Panes > Freeze First Column.

            The faint line that appears between Column A and B shows that the first column is frozen.

Freeze the first two columns

Select the third column.

Select View > Freeze Panes > Freeze Panes.

Freeze columns and rows

Select the cell below the rows and to the right of the columns you want to keep visible when you scroll.

Select View > Freeze Panes > Freeze Panes.

Hope this helps.

Discuss
0

Simple solution:

Select the cell below the rows and to the right of the columns, you want to keep visible when you scroll. Select View > Freeze Panes > Freeze Panes.

Regards,
Jerry

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login