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

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