Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I supress Ctrl-PgUp/Ctrl-PgDn? (Excel 97 SR2)

    Hi all

    ]'m working on an application that has extensive validation triggered by clicking on command buttons on each of several worksheets. However, the user can easily bypass this validation by using the keyboard shortcuts to move to another sheet e.g.: Ctrl-PgUp or Ctrl-PgDn.

    I'd be very grateful for any suggestions on how I might intercept these keystokes and prevent them from working?

    Many thanks

    NIgel Bell, UK

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I supress Ctrl-PgUp/Ctrl-PgDn? (Excel 97 SR2)

    Rightclick the sheet tab, choose view code
    from the top section of the righthand window (the code pane), select "Worksheet" from the lefthand dropdown.
    From the righthand dropdown select "Deactivate"

    You should now have something like this:

    Option Explicit

    Private Sub Worksheet_Deactivate()

    End Sub

    Between the lines above you can now type the name of the validation sub to ensure it gets run when the user tries to exit the sheet.

    To redirect the user to the sheet, use:

    Me.Activate
    inside the Worksheet_Deactivate module.

    If you need this behaviour for several sheets, it is simpler to use the ThisWorkbook module, using this event:

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    MsgBox Sh.Name
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I supress Ctrl-PgUp/Ctrl-PgDn? (Excel 97 SR2)

    This is another way

    ' At Open book

    Sub Auto_Open()
    Dim wshtItem As Excel.Worksheet
    Dim wshtFirst

    ' Select my first sheet when I open my Workbook.

    Sheets("FirstSheet").Visible = True
    Sheets("FirstSheet").Select

    wshtFirst = "FirstSheet"

    ' Hide all the sheet excepting previously selected
    '
    For Each wshtItem In ThisWorkbook.Worksheets
    If Not UCase(wshtItem.Name) = UCase(wshtFirst) Then
    wshtItem.Visible = xlSheetHidden
    End If
    Next wshtItem

    Set wshtItem = Nothing
    End Sub

    Then, you can make a small macro that by a button controls to that sheet you can go...


    ' Show select sheet

    Sub Show_OtherSheets()
    Dim ThisSheet
    ThisSheet = ActiveSheet.Name
    Sheets("OtherSheet_X").Visible = True
    Sheets("" & ThisSheet & "").Visible = False
    End Sub

    I hope this can help you.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I supress Ctrl-PgUp/Ctrl-PgDn? (Excel 97 SR2)

    Maybe, this thread can help you.

  5. #5
    New Lounger
    Join Date
    Mar 2001
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I supress Ctrl-PgUp/Ctrl-PgDn? (Excel 97 SR2)

    Thanks Servando

    I've implemented what you've suggested and it works great

    This is a wonderful mechanism for developer support

    With best wishes

    Nigel <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •