VBA to have certain sheets scroll simultaneously

Hi all,

I'm currently trying to figure out if there is a way to have several sheets (not all) move simultaneously.

e.g.

If I scroll within the sheet "China" to the following position


.ScrollColumn = 34
.ScrollRow = 85
ActiveWindow.Zoom = 100


then I want the code to move it to the same row and column position in sheet "China", "Korea", "HK Hub" etc. as well without having to select each of the sheets and doing it manually, so all sheets in the array will thus ALWAYS have the same window position and zoom, which would help me tremendously when comparing data and switching between sheets...

I found the below code to do that, unfortunately it does not seem to work. Maybe it's just an easy fix as I'm new to the macro world? Can someone have look?

Please Login or Register  to view this content.

You need to add back your names to the array...

Please Login or Register  to view this content.

That works!!! brilliant! Thanks!

The only slight problem I am experiencing (and maybe I am doing it wrong) is it only moves it to the same position if I go to the VBA window and run it manually every time. Is there a way to have it permanently run in the background so that it constantly updates the column/row position of all sheets in the array? I have pasted the code in the "ThisWorkbook" section, I suppose that is correct?

That's the nub of the problem. Excel has no Scroll event so the code can't run automatically - in any case scrolling all the 'linked' sheets every time one changes just eats resources and slows things down.

One possible solution is to only run the code when a worksheet is activated, and then only for the sheet that was activated but you need some sort of reference point - will have a look later, no access to Excel at the moment.

I'm glad to hear I am asking more interesting/ challenging questions than others I will have a lot more 'complex' VBA request in the future, as my company does not currently have a macro expert employed!

I'm impressed! That actually works! I'm experiencing a slight 1/3 of a second screen flickering, but that is minor...

I have pasted it into the "ThisWorkbook" section as I understand it needs the "workbook open" sub, but unfortunately I do already have some code in there (mainly combo boxes and password protection), can I just combine the two or how do I solve that problem? It obviously currently only works if I remove the other code.

Thanks for your help with this!!!

Just combine them - nothing in the Workboook_Open event I posted will interfere with anything you have done.

If you are not selecting any sheets with your current code then just add mine anywhere - if you are, then I guess you add it at the end to take account of any sheet switching in your code but I don't think it'll matter where it goes in the procedure.

does not currently have a macro expert employed

I'm available - always did want to get back to to see the changes

Thanks!! will try it out on Monday!

and interested in moving to Hong Kong ;-)? we are currently looking for someone!

....

So... just tried and don't seem to get it right when combining it with my other code in the workbook_open event. Still not very familiar with vba codes especially the 'Option Explicit' part. This part of the code seems to be interfering with my code as it shows me a "variable not defined error" message

Please Login or Register  to view this content.

Using 'Option Explicit' means you have to declare all variables before use. It is a way of ensuring you don't mistype a variable name, leading to errors. For example:

Please Login or Register  to view this content.

That is an infinite loop as iTemp never gets to be greater than 10.

You can either go back and declare all the variables or remove the Option Explicit statement. I'd recommend the first, you'll probably do the second

Originally Posted by cytop

You can either go back and declare all the variables or remove the Option Explicit statement. I'd recommend the first, you'll probably do the second

Since you are the expert and I want to learn how to write clean code, let's go for option 1 and declare all variables. Would I just have to add "

Please Login or Register  to view this content.

" to the option explicit code and then place that at the very top before the workbook_open event? Is there anything else in my other code that would need to be fixed?

Please Login or Register  to view this content.

Originally Posted by humdingaling

just curious
Synchronous scrolling (either full screen using dual monitor setup or side by side split) lets do this without all this VBA work required in the background
why don't you just use that?

Since I am trying to scroll simultaneously among different sheets within one (!) workbook, that won't work as far as I am concerned...?

Originally Posted by humdingaling

nope you can open the same workbook multiple times
the method to this just varies slightly depending on what version of excel you are using

Which would already be a problem, since this is a huge file that tends to crash even if just one is open...! The code works pretty well, so that would perfectly solve it, I just need to figure out how to combine it with my previous code in the "ThisWorkbook" section... Can you or anyone spot the mistake in my code above?

Sorry for the late reply. Just solved it myself!! It was an easy fix in the end, but since I'm still new to macros, it took me a while to figure it out.

Below code is the one I'm using now:

Please Login or Register  to view this content.

So, the code is working now, but I can notice a significant screen flickering when selecting a new sheet, is there a way to optimise this code further and reduce the flickering?