PDA

View Full Version : Solved: My Userform loading takes time



jammer6_9
07-18-2009, 01:30 AM
Is there anyway to make the loading of my userform faster. Is below code delays the loading of my userform? I have written the code just to get the current value of my checkboxes in the userform from my worksheet.


Private Sub UserForm_Activate()

Application.ScreenUpdating = False

Sheets("CALCULATOR").Select

'Chilled & Frozen Items

If Range("b10").Value <> "" Then Me.chkCF1.Value = True
If Range("b11").Value <> "" Then Me.chkCF2.Value = True
If Range("b12").Value <> "" Then Me.chkCF3.Value = True
If Range("b13").Value <> "" Then Me.chkCF4.Value = True
If Range("b14").Value <> "" Then Me.chkCF5.Value = True
If Range("b15").Value <> "" Then Me.chkCF6.Value = True
If Range("b16").Value <> "" Then Me.chkCF7.Value = True
If Range("b17").Value <> "" Then Me.chkCF8.Value = True
If Range("b18").Value <> "" Then Me.chkCF9.Value = True
If Range("b19").Value <> "" Then Me.chkCF10.Value = True
If Range("b20").Value <> "" Then Me.chkCF11.Value = True
If Range("b21").Value <> "" Then Me.chkCF12.Value = True
If Range("b22").Value <> "" Then Me.chkCF13.Value = True
If Range("b23").Value <> "" Then Me.chkCF14.Value = True
If Range("b24").Value <> "" Then Me.chkCF15.Value = True

If Range("b25").Value <> "" Then Me.chkCF16.Value = True
If Range("b26").Value <> "" Then Me.chkCF17.Value = True
If Range("b27").Value <> "" Then Me.chkCF18.Value = True
If Range("b28").Value <> "" Then Me.chkCF19.Value = True
If Range("b29").Value <> "" Then Me.chkCF20.Value = True
If Range("b30").Value <> "" Then Me.chkCF21.Value = True
If Range("b31").Value <> "" Then Me.chkCF22.Value = True
If Range("b32").Value <> "" Then Me.chkCF23.Value = True
If Range("b33").Value <> "" Then Me.chkCF24.Value = True
If Range("b34").Value <> "" Then Me.chkCF25.Value = True
If Range("b35").Value <> "" Then Me.chkCF26.Value = True
If Range("b36").Value <> "" Then Me.chkCF27.Value = True
If Range("b37").Value <> "" Then Me.chkCF28.Value = True
If Range("b38").Value <> "" Then Me.chkCF29.Value = True
If Range("b39").Value <> "" Then Me.chkCF30.Value = True

If Range("b40").Value <> "" Then Me.chkCF31.Value = True
If Range("b41").Value <> "" Then Me.chkCF32.Value = True
If Range("b42").Value <> "" Then Me.chkCF33.Value = True
If Range("b43").Value <> "" Then Me.chkCF34.Value = True
If Range("b44").Value <> "" Then Me.chkCF35.Value = True
If Range("b45").Value <> "" Then Me.chkCF36.Value = True
If Range("b46").Value <> "" Then Me.chkCF37.Value = True
If Range("b47").Value <> "" Then Me.chkCF38.Value = True
If Range("b48").Value <> "" Then Me.chkCF39.Value = True
If Range("b49").Value <> "" Then Me.chkCF40.Value = True
If Range("b50").Value <> "" Then Me.chkCF41.Value = True
If Range("b51").Value <> "" Then Me.chkCF42.Value = True
If Range("b52").Value <> "" Then Me.chkCF43.Value = True
If Range("b53").Value <> "" Then Me.chkCF44.Value = True
If Range("b54").Value <> "" Then Me.chkCF45.Value = True

If Range("b55").Value <> "" Then Me.chkCF46.Value = True
If Range("b56").Value <> "" Then Me.chkCF47.Value = True
If Range("b57").Value <> "" Then Me.chkCF48.Value = True
If Range("b58").Value <> "" Then Me.chkCF49.Value = True
If Range("b59").Value <> "" Then Me.chkCF50.Value = True
If Range("b60").Value <> "" Then Me.chkCF51.Value = True
If Range("b61").Value <> "" Then Me.chkCF52.Value = True
If Range("b62").Value <> "" Then Me.chkCF53.Value = True
If Range("b63").Value <> "" Then Me.chkCF54.Value = True
If Range("b64").Value <> "" Then Me.chkCF55.Value = True
If Range("b65").Value <> "" Then Me.chkCF56.Value = True
If Range("b66").Value <> "" Then Me.chkCF57.Value = True
If Range("b67").Value <> "" Then Me.chkCF58.Value = True
If Range("b68").Value <> "" Then Me.chkCF59.Value = True
If Range("b69").Value <> "" Then Me.chkCF60.Value = True


'Dry & Paper Items

If Range("b71").Value <> "" Then Me.chkDP1.Value = True
If Range("b72").Value <> "" Then Me.chkDP2.Value = True
If Range("b73").Value <> "" Then Me.chkDP3.Value = True
If Range("b74").Value <> "" Then Me.chkDP4.Value = True
If Range("b75").Value <> "" Then Me.chkDP5.Value = True
If Range("b76").Value <> "" Then Me.chkDP6.Value = True
If Range("b77").Value <> "" Then Me.chkDP7.Value = True
If Range("b78").Value <> "" Then Me.chkDP8.Value = True
If Range("b79").Value <> "" Then Me.chkDP9.Value = True
If Range("b80").Value <> "" Then Me.chkDP10.Value = True
If Range("b81").Value <> "" Then Me.chkDP11.Value = True
If Range("b82").Value <> "" Then Me.chkDP12.Value = True
If Range("b83").Value <> "" Then Me.chkDP13.Value = True
If Range("b84").Value <> "" Then Me.chkDP14.Value = True
If Range("b85").Value <> "" Then Me.chkDP15.Value = True

If Range("b86").Value <> "" Then Me.chkDP16.Value = True
If Range("b87").Value <> "" Then Me.chkDP17.Value = True
If Range("b88").Value <> "" Then Me.chkDP18.Value = True
If Range("b89").Value <> "" Then Me.chkDP19.Value = True
If Range("b90").Value <> "" Then Me.chkDP20.Value = True
If Range("b91").Value <> "" Then Me.chkDP21.Value = True
If Range("b92").Value <> "" Then Me.chkDP22.Value = True
If Range("b93").Value <> "" Then Me.chkDP23.Value = True
If Range("b94").Value <> "" Then Me.chkDP24.Value = True
If Range("b95").Value <> "" Then Me.chkDP25.Value = True
If Range("b96").Value <> "" Then Me.chkDP26.Value = True
If Range("b97").Value <> "" Then Me.chkDP27.Value = True
If Range("b98").Value <> "" Then Me.chkDP28.Value = True
If Range("b99").Value <> "" Then Me.chkDP29.Value = True
If Range("b100").Value <> "" Then Me.chkDP30.Value = True

If Range("b101").Value <> "" Then Me.chkDP31.Value = True
If Range("b102").Value <> "" Then Me.chkDP32.Value = True
If Range("b103").Value <> "" Then Me.chkDP33.Value = True
If Range("b104").Value <> "" Then Me.chkDP34.Value = True
If Range("b105").Value <> "" Then Me.chkDP35.Value = True
If Range("b106").Value <> "" Then Me.chkDP36.Value = True
If Range("b107").Value <> "" Then Me.chkDP37.Value = True
If Range("b108").Value <> "" Then Me.chkDP38.Value = True
If Range("b109").Value <> "" Then Me.chkDP39.Value = True
If Range("b110").Value <> "" Then Me.chkDP40.Value = True
If Range("b111").Value <> "" Then Me.chkDP41.Value = True
If Range("b112").Value <> "" Then Me.chkDP42.Value = True
If Range("b113").Value <> "" Then Me.chkDP43.Value = True
If Range("b114").Value <> "" Then Me.chkDP44.Value = True
If Range("b115").Value <> "" Then Me.chkDP45.Value = True

If Range("b116").Value <> "" Then Me.chkDP46.Value = True
If Range("b117").Value <> "" Then Me.chkDP47.Value = True
If Range("b118").Value <> "" Then Me.chkDP48.Value = True
If Range("b119").Value <> "" Then Me.chkDP49.Value = True
If Range("b120").Value <> "" Then Me.chkDP50.Value = True
If Range("b121").Value <> "" Then Me.chkDP51.Value = True
If Range("b122").Value <> "" Then Me.chkDP52.Value = True
If Range("b123").Value <> "" Then Me.chkDP53.Value = True
If Range("b124").Value <> "" Then Me.chkDP54.Value = True
If Range("b125").Value <> "" Then Me.chkDP55.Value = True
If Range("b126").Value <> "" Then Me.chkDP56.Value = True
If Range("b127").Value <> "" Then Me.chkDP57.Value = True
If Range("b128").Value <> "" Then Me.chkDP58.Value = True
If Range("b129").Value <> "" Then Me.chkDP59.Value = True
If Range("b130").Value <> "" Then Me.chkDP60.Value = True

If Range("b131").Value <> "" Then Me.chkDP61.Value = True
If Range("b132").Value <> "" Then Me.chkDP62.Value = True
If Range("b133").Value <> "" Then Me.chkDP63.Value = True
If Range("b134").Value <> "" Then Me.chkDP64.Value = True
If Range("b135").Value <> "" Then Me.chkDP65.Value = True
If Range("b136").Value <> "" Then Me.chkDP66.Value = True
If Range("b137").Value <> "" Then Me.chkDP67.Value = True
If Range("b138").Value <> "" Then Me.chkDP68.Value = True
If Range("b139").Value <> "" Then Me.chkDP69.Value = True
If Range("b140").Value <> "" Then Me.chkDP70.Value = True
If Range("b141").Value <> "" Then Me.chkDP71.Value = True
If Range("b142").Value <> "" Then Me.chkDP72.Value = True
If Range("b143").Value <> "" Then Me.chkDP73.Value = True
If Range("b144").Value <> "" Then Me.chkDP74.Value = True
If Range("b145").Value <> "" Then Me.chkDP75.Value = True

'Operating Supplies

If Range("b147").Value <> "" Then Me.chk1.Value = True
If Range("b148").Value <> "" Then Me.chk2.Value = True
If Range("b149").Value <> "" Then Me.chk3.Value = True
If Range("b150").Value <> "" Then Me.chk4.Value = True
If Range("b151").Value <> "" Then Me.chk5.Value = True
If Range("b152").Value <> "" Then Me.chk6.Value = True
If Range("b153").Value <> "" Then Me.chk7.Value = True
If Range("b154").Value <> "" Then Me.chk8.Value = True
If Range("b155").Value <> "" Then Me.chk9.Value = True
If Range("b156").Value <> "" Then Me.chk10.Value = True
If Range("b157").Value <> "" Then Me.chk11.Value = True
If Range("b158").Value <> "" Then Me.chk12.Value = True
If Range("b159").Value <> "" Then Me.chk13.Value = True
If Range("b160").Value <> "" Then Me.chk14.Value = True
If Range("b161").Value <> "" Then Me.chk15.Value = True
If Range("b162").Value <> "" Then Me.chk16.Value = True

If Range("b163").Value <> "" Then Me.chk17.Value = True
If Range("b167").Value <> "" Then Me.chk18.Value = True
If Range("b168").Value <> "" Then Me.chk19.Value = True
If Range("b166").Value <> "" Then Me.chk20.Value = True
If Range("b167").Value <> "" Then Me.chk21.Value = True
If Range("b168").Value <> "" Then Me.chk22.Value = True
If Range("b169").Value <> "" Then Me.chk23.Value = True
If Range("b170").Value <> "" Then Me.chk24.Value = True
If Range("b171").Value <> "" Then Me.chk25.Value = True
If Range("b172").Value <> "" Then Me.chk26.Value = True
If Range("b173").Value <> "" Then Me.chk27.Value = True
If Range("b174").Value <> "" Then Me.chk28.Value = True
If Range("b175").Value <> "" Then Me.chk29.Value = True
If Range("b176").Value <> "" Then Me.chk30.Value = True
If Range("b177").Value <> "" Then Me.chk31.Value = True
If Range("b178").Value <> "" Then Me.chk32.Value = True

If Range("b179").Value <> "" Then Me.chk33.Value = True
If Range("b180").Value <> "" Then Me.chk34.Value = True
If Range("b181").Value <> "" Then Me.chk35.Value = True
If Range("b182").Value <> "" Then Me.chk36.Value = True
If Range("b183").Value <> "" Then Me.chk37.Value = True
If Range("b184").Value <> "" Then Me.chk38.Value = True
If Range("b185").Value <> "" Then Me.chk39.Value = True
If Range("b186").Value <> "" Then Me.chk40.Value = True
If Range("b187").Value <> "" Then Me.chk41.Value = True
If Range("b188").Value <> "" Then Me.chk42.Value = True
If Range("b189").Value <> "" Then Me.chk43.Value = True
If Range("b190").Value <> "" Then Me.chk44.Value = True
If Range("b191").Value <> "" Then Me.chk45.Value = True
If Range("b192").Value <> "" Then Me.chk46.Value = True
If Range("b193").Value <> "" Then Me.chk47.Value = True
If Range("b194").Value <> "" Then Me.chk48.Value = True

End Sub

mdmackillop
07-18-2009, 01:57 AM
I don't know if it will be any quicker, but here's an alternative coding, which can be adapted for all your controls.

For i = 1 To 60
Me.Controls("chkCF" & i).Value = Range("b" & i + 9).Value <> ""
Next

mdmackillop
07-18-2009, 02:30 AM
I don't find that loading 128 checkboxes takes any appreciable time. Try this test. The Fill 2 loads data into an array first, which runs more quickly. In this test I get results of 114 & 78.

jammer6_9
07-18-2009, 02:41 AM
Wow very perfect sample and less code. :thumb Thanks I will be working with this.


I don't find that loading 128 checkboxes takes any appreciable time. Try this test. The Fill 2 loads data into an array first, which runs more quickly. In this test I get results of 114 & 78.

jammer6_9
07-18-2009, 05:05 AM
mdmackillop solution you gave works perfect thanks.

This thread is solved...