Why does the click event get control?

[Lee Hunter]

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

 

[Jake Marx]

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]