Make Userform follow a specific cell

[gnaske]

Hi Forum.

I have a Userform (Lets call it Userform1).
I want to open this Userform1 on a specific cell / stick to a specific cell (Lets say P2).
I also what the userform to follow this cell P2, if I zoom In or Out.

Note - I use Checkboxes as zoom control in another Userform, which mean I can put the code under each Checkbox, when clicking at it.
- Checkbox1 click = 95% zoom
- Checkbox2 click = 100% zoom
- Checkbox3 click = 105% zoom
- And so on.

Is it possible to make?

Thank you in advance.
Ib

[karmapala]

I'm sorry as I'm not helping here.

Just want to give an idea..... if the user form is not an option,
(the user will always see the sheet with the user form in cell P2).
then maybe you can try to make the user form (in the sheet itself) with ActiveX controls.
Therefore, no need a code if you zoom in or zoom out the sheet, because the user form will follow respectively.

Something like this :
2021-02-16_20-03-24.gif

[torachan]

Try the attached - form locates to cell 'P2' and stays with it when zoomed by the option buttons.
torachan

[gnaske]

Hi Karmapale and Torachan.

It looks promising.

I'm making a schedule for "working".
The issue is, the workbook can be used by several Users, but all with different screen solutions.

I have one Userform with the zoom.
And another to select which week to open.
They are both ShowModal = False

The Userform to select the Week can be open, meanwhile I open the Zoom Userform.
When selecting another Screen Zoom, the Week Userform should be following the cell on the edge of sheme.

I'll try to put it all together in my workbook.
And if it works for me I'll mark this thread as Solved

Thank you very much.
Ib

[torachan]

why bother with two forms.
explore the use of the multi-page - makes for more compact coding and ease of variable handling.
torachan.

[gnaske]

Hi Torachan

I'm not that good in VBA coding, although I have a lot of good ideas, making different workbooks, with a lot of functions.

The Zoom Userform1 should only be used, when the User want to select another screen zoom.
The Week Userform2 will be open most of the time, to select and show another week in the scheme.

The last thing I need to figure out is, when I select a week, it should stick to another cell in the top of the scheme.
But I believe I can figure out to do that.

Look at the attached too.

[karmapala]

The sample attachment is using one activeX ComboBox object.
It doesn't use the "regular" user form because the ComboBox is in the sheet and always there.
Maybe you want to have a look.

Because I see in your sample WB you use freeze pane on cell A2,
so when you create the combobox on Sheet1,
put the combobox to row 1, any column.

This code is assuming :
1. all of your users know on how to zoom in/out
using the Excel zoom feature which is located on the bottom right of Excel app.
(or tell your user to zoom in/out, use ctrl + roll up/down on the mouse).
However, you can still use your userform1 button if you want.

2. the range between the row contain "week n" value to another row contain "week n + 1" value will always the same,
which in your example : week1 - blank 5 rows - week2 - blank 5 rows - week3 - blank 5 rows, and so on.

3. the row under the row contains "week n", must be 5 blank rows (as in your sample workbook).

4. when the user select the combobox (which contain week1, week2, week3, and so on),
the code will hide all the rest of the rows till the end and scroll the selected week to touch the freeze pane.

2021-02-20_00-31-21.gif

The combobox will stay on cell F1 when the user zoom in/out using the Excel zoom feature.

[gnaske]

Hi karmapala.

I already used the DropDown on the sheet, but changed my mind.
Instead I made a lot of "Icon Pictures" in row 1 to click on, which will open the different Userforms and run other commands.

Normally I would make my own Ribbon.
But I have a big issue, using Excel after Office 2010.

As you probably know, when opening several Excels in 2010, each Excel will have it's own VBA Editor.

All Excels after this, will open and share the codes in the same VBA Editor.
All workbooks is just listed above each other in the VBA Editor.

Which mean, if I make a code in "ThisWorkbook - Activate", it will run the code in all the other Workbooks I open.
And not only in the Workbook with the code.
And then it will just make a BUG.

And this is really killing me.
I love to make my own Ribbon and to make it work as well.

I can still do this and just open my Workbooks in 2010.
But if I make something for another person (As this I'm working at right now) and he use an Excel after 2010, it won't work.

I made a Master I always use to "Start-Up" with and then I can just add more buttons to the Ribbon.
I've attached this Master, for you to look at.

The Ribbon is made, using Andy Pope's free VBA Ribbon Editor.
- Download Here.
- Video tutorial Here.

Please note about this Attached
It was made in Danish, but I translated the most common functions.

When you open the VBA Editor, to see my codes, I'm not that good in Coding.
I believe some of the codes is not as it should had been made, but somehow it works anyway.
I learned it by "Learning by trying", so please bear with me.

If you open the attached in an Excel after 2010, then please close all other Excels first.
Otherwise it will just make BUG's.

Ib/Gnaske

[gnaske]

Just forgot.

You're of course free to use the Master if you want.
Maybe you can make some nice Workbooks for yourself.

And you can download the official Microsoft Office VBA Editor Here.
You just need to type the codes yourself, using this one.
Or you can use it as the Ribbon Editor for already made Excels, with a Custom Ribbon.

Ib

[gnaske]

And sorry, but forgot again.

There is some other Editors, but they are not for free.

I believe this one probably would solve my problems using Excels after 2010.
And I guess it's more "User Friendly" than Andy Pope's Editor as well.

I was about to buy it several years ago, but decided not too at that time, because I could use the free one from Andy.
I only used the Excel 2010 at that time and now I'm only making Ribbon for my own joy.

Download Here

Ib

[gnaske]

torachan

I copied all your codes and implemented them to the codes in my own Workbook.
First I couldn't make it work and didn't understood why.
The userform, which should stick to the cell X2, started up in the left top of the sheet - Position 0,0 (zero).

I tried everything, but nothing would work.
After looking at lot at the codes from you, I realized I used Application.ScreenUpdate = False in the first line of my own codes.
And when removing that line it worked perfectly.

Thank you to the both of you, for spending time with this issue.
Problem solve

[torachan]

thanks for the feedback that all is good.
torachan.