View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ranswrt ranswrt is offline
external usenet poster
 
Posts: 191
Default Listbox problems

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 a

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