Listbox problems

[ranswrt]

I have a procedure that starts out with:

Call stopautocalc
Worksheets("Estimates").Unprotect Password:="6573"
Worksheets("Estimates DB").Unprotect Password:="6573"
Worksheets("Cost Items DB").Unprotect Password:="6573"

****code****

Worksheets("Estimates").Protect Password:="6573"
Worksheets("Estimates DB").Protect Password:="6573"
Worksheets("Cost Items DB").Protect Password:="6573"
Call startautocalc

Call loadlistbox

End Sub

The code for stopautocalc and startautocalc are:

Sub stopautocalc()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub

Sub startautocalc()
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

The code for loadlistbox is:

Sub loadlistbox()
Dim est As String
Dim xcell As Range
Dim fcell As Range
Dim rng As Range
Dim a As String
Dim i As Long
Dim num As Long

Worksheets("Estimates").ListBox1.Clear
Set xcell = Range("estlnkdb")
Set rng = Range(xcell.Offset(0, 1), xcell.Offset(0, 130))
est = Range("currentestimate")
With rng
Set fcell = .Find(what:=est, LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByColumns)
End With

num = fcell.Offset(0, 1)
For i = 1 To num
a = fcell.Offset(i, 0)
With Worksheets("Estimates").ListBox1
.AddItem a
End With
Next

End Sub

I am having problems loading listbox1. When Call stopautocalc is before
unprotecting worksheets the listbox is not loaded. If I put Call
stopautocalc after the uprotectsheets the listbox is loaded. I would like to
have stopautocalc before unprotect sheets so the screen doesn't flash. How
can I make this work?
Thanks

 

[dmoney]

place these two lines at the end of your loadlistbox control

The problem is the screen is not updating.

call startautocalc
call stopautocalc


HTH dmoney

 

[ranswrt]

I tried what you suggested and that didn't work. I also tried calling a
procedure that would clear the listbox and that also didn't work. I thought
by calling startautocalc right before loadlistbox that the screen updating
should work. Would there be another problem why the listbox isn't updated?

 

[dmoney]

If your listbox code works then it is beyond me. I recommend ensuring that
the listbox code works on its own and if it does, re-post your question as I
am out of ideas

good luck

dmoney