Excel Scroll Query

Wombat

Can't find an answer to this problem and wondered if anyone had a solution?

I have an Excel worksheet comprising of 11 columns by 67 rows, which is set to the ScrollArea via VBA code. There is other data held on the sheet outside this area but not visible to a user.

The problem relates to the use of the left & right scrollbar at the bottom of the window. If I click on the right-hand scroll arrow the sheet scrolls to the right until it reaches column 11. One more click to the right returns the view to column 1.

Likewise, if I left click and drag the scrollbar to the right, when I reach the rightmost end of the scrollbar and release the mouse button, the view jumps back to column 1 again and the bar returns to the left edge of the scrollbar area.

Is there a way of stopping this jumping back to column 1 when the rightmost edge of the scrollbar is reached?

TIV.

MaxOfLondon

It looks to me the scroll area is still controlled via VBA onScroll event.
The options are:
1. Rewrite code to stop wraping.
2. Turn off VBA.

The second one is probably not what you want...
I could possibly look into code for you. Let me know if required

Wombat

Thanks, Max.

No, turning off VBA is not an option.

I don't explicitly use an OnScroll command in VBA, just a ScrollArea. I wondered whether the main window left & right scrollbar was accessible to VBA, so that I could alter its OnScroll Property? If so, what is it known as in VBA-speak?

Chippy_Minton

I see what you're saying. See options 1, 3, 4, 5 on this thread for some possible solutions.

Another idea is to hide Excel's horizontal scroll bar (Tools - Options - View tab - untick Horizontal scroll bar) and then implement your own HSB with a UserForm containing a HSB. The UserForm is placed on the sheet using UserForm.Show and then you can use events in the UserForm to control the scrolling.

MaxOfLondon

Well, after some investigation it doesn't look very good. It appears to be a scaling bug or Microsoft's ‘feature’ within scrollbar used in Excel window. There are no VBA methods within Window class that would allow you to access scrollbar position to control its value though. The only way of doing this would be to setup system hook to monitor scrollbar clicks and position and alter it accordingly. I am not sure if that can be done via VBA as it is quite complex WinAPI that needs to be used. Perhaps it is possible but is it worth all the hassle?

Note that if you set your column widths of locked range so range fits to the window size the scrollbar will not jump to beginning.
Sorry I cannot be more help

Wombat

Thanks, Max.

Yes, I wondered if it was a bug. I found that if I remove the line:

Worksheets("Sheet1").ScrollArea = "A1:I68"

it doesn't jump. I'm not using locked cells and I can't make the columns any narrower to fit on the screen. So will have to play around with the ScrollArea to see if using different values has an effect.

Thanks also, Chippy. A UserForm could be an answer. Will play around with that as well.