Prevent Worksheet Scrolling

    Hi Everyone,


    I have an excel sheet embedded in a Powerpoint presentation, but I have the problem that users are editing the sheet and accidently or unknowingly scrolling around. This results in the window resizing itself when the exit the object and messing up the appearance of the presentation. Is there a way of easily preventing scrolling (either via VBA or other)?


    I can obviously hide the scroll bars but that does not prevent the use of the mouse's scroll-wheel.


    I have also already hidden all unused columns and rows so that they cannot oversize the window.


    All thoughts appreciated.
    Thanks,
    Ian

    Re: Prevent User From Scrolling?


    Right click on the sheet name tab, choose View Code and in here paste.

    Code
    Private Sub Worksheet_Activate()
Application.Goto Range("A1"), True
Me.ScrollArea = ActiveWindow.VisibleRange.Address
''Set back to default
'Me.ScrollArea=""
End Sub

    Re: Prevent Worksheet Scrolling


    Do you have to embed the whole worksheet in Powerpoint? It makes for a big .ppt file.
    If it's just an area you want to display, without the ability to edit, instead of normal copy/pasting, after selecting the area to copy, try holding the shift key while choosing Edit from the drop down menus and a new option will appear - 'Copy picture...' which will open a dialogue box with a variety of options to experiment with. The results are good in Powerpoint and I've found Powerpoint responds/runs more quickly too.
    p45cal

    Re: Prevent Worksheet Scrolling


    Hi Pascal,


    The use is slightly more complicated than that.
    The spreadsheet needs to be editable and has certain validation acting on it to control the usetrs inputs. The data they enter is then captured and put into another database from a macro which reads the tables.


    The issue I have on scrolling is purely an aesthetic one, but if we can fix it, it will save my team from having to check that the tables are right before they are viewed in meetings.


    Dave - Your code is an ok idea but it needs to execute when the workbook is deactivated or to fix the scroll area when the workbook is activated. Any ideas? I have tried fixing the scroll area just to what I want to remain visible however this seems fine in a standard Excel workbook, I can still scroll past the range when I am in the embedded sheet in Powerpoint...
    very confusing...


    Ian

    Re: Prevent Worksheet Scrolling


    I've just tried this and found that the sheet_activate event is not triggered when the sheet in powerpoint is double-clicked to edit it. However, the selection_change event is triggered on changing the selection. When I tried it, this worked:

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Me.ScrollArea = "A1:F15"
End Sub

    That and removing the scroll bars should force the users to change the selection and thus trigger the event.
    For the scrollarea no longer to be restricted to users outside powerpoint you might get away with a Me.ScrollArea="" in the sheet_activate event!


    p45cal

    Re: Prevent Worksheet Scrolling


    I've got this working now;


    The main problem wasn't the fixing of the scrollarea but the fact I had all the unused rows hidden.


    It turns out if you have rows hidden then you can still scroll around and make most of the screen appear as just the greyness because effectively you're not scrolling to the area that isn't allowed...


    After unhiding the rows and then refixing the scrollarea by using a quick end(xlup) to find the last row (I know the last column) everything is now ok.


    Thanks for chipping in Dave and Pasacal.
    Ian

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login