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?

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?

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?

And the listbox change code is:


Private Sub ListBox1_change()

Dim HWks As Worksheet
Dim cCtr As Long
Dim myRng As Range

Set HWks = Worksheets("Hidden")

With HWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ActiveSheet.ListBox1
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr


End With
With HWks
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

With ActiveSheet.ListBox2
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With

With HWks
Set myRng = .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
End With

With ActiveSheet.ListBox3
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With


With HWks
Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
End With

With ActiveSheet.ListBox4
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With


End Sub


There are 4 version of this for 4 listboxes, each listbox change saves all 4
of the listboxes as you can see, which is probably totally unneccesary but I
just did it so I could copy and paste it quicker (and the run time of the
macro is not noticeable anyway).

Thanks!

Ok solved it.

It turns out enableevents doesnt work in this case (yet another suprise
difficulty with multiselect listboxes) so a global variable is a good way
around it as documented he

http://www.eggheadcafe.com/software/...bleevents.aspx

"oli merge" wrote:

And the listbox change code is:


Private Sub ListBox1_change()

Dim HWks As Worksheet
Dim cCtr As Long
Dim myRng As Range

Set HWks = Worksheets("Hidden")

With HWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ActiveSheet.ListBox1
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr


End With
With HWks
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

With ActiveSheet.ListBox2
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With

With HWks
Set myRng = .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
End With

With ActiveSheet.ListBox3
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With


With HWks
Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
End With

With ActiveSheet.ListBox4
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With


End Sub


There are 4 version of this for 4 listboxes, each listbox change saves all 4
of the listboxes as you can see, which is probably totally unneccesary but I
just did it so I could copy and paste it quicker (and the run time of the
macro is not noticeable anyway).

Thanks!

As a user, I would think it would be more natural to let me pick my choices and
then click a button that actually saves the choices.

Then I could make as many "mistakes" as I wanted without worrying. And as a
developer, that seems like a very reasonable approach to me.

oli merge wrote:

Ok solved it.

It turns out enableevents doesnt work in this case (yet another suprise
difficulty with multiselect listboxes) so a global variable is a good way
around it as documented he

http://www.eggheadcafe.com/software/...bleevents.aspx

"oli merge" wrote:

And the listbox change code is:


Private Sub ListBox1_change()

Dim HWks As Worksheet
Dim cCtr As Long
Dim myRng As Range

Set HWks = Worksheets("Hidden")

With HWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ActiveSheet.ListBox1
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr


End With
With HWks
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

With ActiveSheet.ListBox2
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With

With HWks
Set myRng = .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
End With

With ActiveSheet.ListBox3
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With


With HWks
Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
End With

With ActiveSheet.ListBox4
For cCtr = 0 To .ListCount - 1
myRng.Cells(1).Offset(cCtr, 1).Value = .Selected(cCtr)
Next cCtr
End With


End Sub


There are 4 version of this for 4 listboxes, each listbox change saves all 4
of the listboxes as you can see, which is probably totally unneccesary but I
just did it so I could copy and paste it quicker (and the run time of the
macro is not noticeable anyway).

Thanks!


--

Dave Peterson