Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms and stuff (2003)

    Please find attached a copy of a budget collection data sheet

    The sheet normally works quite well but once in a while the form that automatically appears when you select a blue cell fails to do so. When this happens, to get the workbook to work correctly again users need to close and re-open excel. Can somebody please assist me with fixing this?

    Also I would like to code the form so that, once it has appeared, pressing escape will unload it. Can you please advise how this can be done.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms and stuff (2003)

    Try changing the Worksheet_SelectionChange event procedure of the TP390 worksheet to

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'To create the supplier selection form
    On Error GoTo ExitHere
    Application.EnableEvents = False
    CurrentSupplierRow = ActiveCell.Row
    If Not Intersect(ActiveCell, Range("M1:M500")) Is Nothing And _
    Cells(ActiveCell.Row, 1) = 1 Then
    UserForm1.UserForm_Initialize
    UserForm1.Show
    End If
    ExitHere:
    Application.EnableEvents = True
    End Sub

    The easiest way to close the form when pressing Escape:
    - Create a command button cmdCancel on the form.
    - Set its Cancel property to True - this will make the Escape key act as if the user clicked the button.
    - Set it to close the form when clicked:

    Private Sub cmdCancel_Click()
    Unload Me
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms and stuff (2003)

    Thanks Hans that seems to have fixed the problem.

    For my information can you please advise what was going wrong, so that I know what to avoid in the future.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms and stuff (2003)

    The Worksheet_SelectionChange event procedure turns event processing by Excel off temporarily using the lines

    Application.EnableEvents = False

    at the beginning and

    Application.EnableEvents = True

    at the end. This is useful to avoid one event triggering another one, which could result in a cascade of events. But if the code fails for some reason, and if there is no error handling, the line

    Application.EnableEvents = True

    will not be executed, leaving Excel unresponsive to events. By adding error handling to the procedure, you ensure that the above line will always be executed, even if an error occurs. For this reason, it is a good idea to add error handling to all code that turns off EnableEvents.

Posting Permissions

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