Application.enableevents = false not working in workbook open
Ok, Its code I got from the helpful chap who answered my last question on
here that I have altered a bit. Here is the Open event code:
Option Explicit
Private Sub Workbook_Open()
Dim HWks As Worksheet
Dim cCtr As Long
Dim myRng As Range
Dim LBWks As Worksheet
Application.EnableEvents = False
Set HWks = Me.Worksheets("Hidden")
Set LBWks = Me.Worksheets("Submission Form") '<-- sheet with listbox
With HWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
With LBWks.OLEObjects("ListBox1").Object
..MultiSelect = fmMultiSelectMulti
..ColumnCount = 1
..ListStyle = fmListStyleOption
For cCtr = 0 To .ListCount - 1
..Selected(cCtr) = CBool(myRng.Cells(1).Offset(cCtr, 1).Value)
Next cCtr
End With
With HWks
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With
With LBWks.OLEObjects("ListBox2").Object
..MultiSelect = fmMultiSelectMulti
..ColumnCount = 1
..ListStyle = fmListStyleOption
For cCtr = 0 To .ListCount - 1
..Selected(cCtr) = CBool(myRng.Cells(1).Offset(cCtr, 1).Value)
Next cCtr
End With
With HWks
Set myRng = .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
End With
With LBWks.OLEObjects("ListBox3").Object
..MultiSelect = fmMultiSelectMulti
..ColumnCount = 1
..ListStyle = fmListStyleOption
For cCtr = 0 To .ListCount - 1
..Selected(cCtr) = CBool(myRng.Cells(1).Offset(cCtr, 1).Value)
Next cCtr
End With
With HWks
Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
End With
With LBWks.OLEObjects("ListBox4").Object
..MultiSelect = fmMultiSelectMulti
..ColumnCount = 1
..ListStyle = fmListStyleOption
For cCtr = 0 To .ListCount - 1
..Selected(cCtr) = CBool(myRng.Cells(1).Offset(cCtr, 1).Value)
Next cCtr
End With
Application.EnableEvents = True
End Sub
"Jacob Skaria" wrote:
Why dont you post your code..
--
Jacob
"oli merge" wrote:
Hi,
I have some code I want to run on opening a workbook that populates a
multiselect listbox with some values without triggering other macros that are
stored in events. Basically as the open workbook code populates the listbox,
some other code I have that is set to trigger on the listbox change event
that writes the values to some cells kicks in and puts all the values except
the first to blank before the open workbook code finishes.
I thought putting "Application.enableevents = false" at the start of the
openworkbook code and "Application.enableevents = true" at the end would stop
this, but its not working. After some reading I was wondering whether its
actually the worksheet trying to load the object that triggers the listbox
change event, not the workbook open event, and that this is running before
the workbook change?
Whats the best solution here, could I have a global variable that prevents
the listbox change event from doing anything until the workbook open event
has run?
|