+ Reply to Thread
Results 1 to 2 of 2

Why does the click event get control?

  1. #1
    Lee Hunter
    Guest

    Why does the click event get control?

    I have the following code in the workbook_open event
    Private Sub Workbook_Open()
    On Error GoTo errhand
    ActiveWorkbook.Sheets(1).Select
    ActiveSheet.Codes.Width = 200
    ActiveSheet.Codes.ColumnCount = 2
    ActiveSheet.Codes.BoundColumn = 2
    ActiveSheet.Codes.ColumnWidths = "2 IN;.5IN"
    ActiveSheet.Codes.TextColumn = 1
    ActiveSheet.Codes.ListFillRange = "=Codes"
    ActiveSheet.Codes.Visible = False
    Exit Sub
    errhand: MsgBox "Error Number is " & Err.Number & " Error Description is " &
    Err.Description
    End Sub

    When the Codes "BoundColumn" Statement is executed, control passes to the
    Codes_Click Event. Codes is a combo box on sheet 1

    Why does this happen? How do I set the BoundCOlumn without giving up control?

    TIA
    Lee Hunter

  2. #2
    Jake Marx
    Guest

    Re: Why does the click event get control?

    Hi Lee,

    Your code is triggering the Click event (it triggered the Change event in my
    testing, but nonetheless), which you ascertained. To stop this from
    happening, you can use a global Boolean variable to track whether you want
    event code to "run" or not. Add this to a public module:

    Public gbDisableEvents

    Now, in your code:

    Private Sub Workbook_Open()
    On Error GoTo errhand
    gbDisableEvents = True
    ActiveWorkbook.Sheets(1).Select
    ActiveSheet.Codes.Width = 200
    ActiveSheet.Codes.ColumnCount = 2
    ActiveSheet.Codes.BoundColumn = 2
    ActiveSheet.Codes.ColumnWidths = "2 IN;.5IN"
    ActiveSheet.Codes.TextColumn = 1
    ActiveSheet.Codes.ListFillRange = "=Codes"
    ActiveSheet.Codes.Visible = False
    ExitRoutine:
    gbDisableEvents = False
    Exit Sub
    errhand: MsgBox "Error Number is " & Err.Number & " Error Description is " &
    _
    Err.Description
    Resume ExitRoutine
    End Sub

    Now, in your event handlers, just do this:

    Private Sub Codes_Click()
    If Not gbDisableEvents Then
    '/ your code
    End If
    End Sub

    --
    Regards,

    Jake Marx
    www.longhead.com


    [please keep replies in the newsgroup - email address unmonitored]

    Lee Hunter wrote:
    > I have the following code in the workbook_open event
    > Private Sub Workbook_Open()
    > On Error GoTo errhand
    > ActiveWorkbook.Sheets(1).Select
    > ActiveSheet.Codes.Width = 200
    > ActiveSheet.Codes.ColumnCount = 2
    > ActiveSheet.Codes.BoundColumn = 2
    > ActiveSheet.Codes.ColumnWidths = "2 IN;.5IN"
    > ActiveSheet.Codes.TextColumn = 1
    > ActiveSheet.Codes.ListFillRange = "=Codes"
    > ActiveSheet.Codes.Visible = False
    > Exit Sub
    > errhand: MsgBox "Error Number is " & Err.Number & " Error Description
    > is " & Err.Description
    > End Sub
    >
    > When the Codes "BoundColumn" Statement is executed, control passes to
    > the Codes_Click Event. Codes is a combo box on sheet 1
    >
    > Why does this happen? How do I set the BoundCOlumn without giving up
    > control?
    >
    > TIA
    > Lee Hunter




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1