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

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" wrote:

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

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" wrote:

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" wrote:

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

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

"ranswrt" wrote:

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" wrote:

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" wrote:

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