UserForm - pointing control source to new sheet - long delay

[Tony Miller]

I have a userform with many textboxes on it.
The control sources are set using variables during the initialisation

The problem is when I point the userform to a new worksheet - same
structure different data .There is this long lag to show the form.The
form outline shows but the txtboxes & data are not visible for a long
time.
The only way is to throw a msgbox at the end of the code which seems
to bring the form to fully visible with all the data
I tried repaint but no luck

ANy idea why this happens only when the control source worksheet is
changed

Thanks

 

[GS]

I have a userform with many textboxes on it.
The control sources are set using variables during the initialisation

The problem is when I point the userform to a new worksheet - same
structure different data .There is this long lag to show the form.The
form outline shows but the txtboxes & data are not visible for a long
time.
The only way is to throw a msgbox at the end of the code which seems
to bring the form to fully visible with all the data
I tried repaint but no luck

ANy idea why this happens only when the control source worksheet is
changed

Thanks

VBA has to first evaluate every use of the variable ref to the new
sheet, then update everywhere that ref is used. It would be faster and
more efficient to close the userform and reopen after specifying the
ControlSource sheet.

Better yet is to implement using the 'New' statement and load the
userform into an object variable, have its Inialize event set up the
textboxes for each instance, and destroy the instance before changing
sheet refs.

<air code>
'..in a standard module's Declaration section
Dim fTemp As Object
Public wksTarget As Worksheet

'..in the same module
Sub Dostuff()
Set wksTarget = Sheets("sheet1name")
Set fTemp = New Userform1
'..do stuff

'..change sheets
Set fTemp = Nothing: Set wksTarget = Sheets("sheet2name")

'..create new instance of userform
Set fTemp = New Userform1
'..do more stuff

'cleanup
Set fTemp = Nothing: Set wksTarget = Nothing
End Sub

...where Userform1_Initialize sets up the ControlSource for the
textboxes to the sheet ref'd in wksTarget

 

[Jim Cone]

Also, a couple of DoEvents in there someplace might make a difference.
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .

 

[GS]

Jim Cone has brought this to us :

Also, a couple of DoEvents in there someplace might make a difference.
'---

Yeah, that's maybe an option if what's happening responds to the
statement. I find, though, that setting this property seems
unreasonably slow depending on how we go about it. (referring to
numerous controls on a form) I have userform wizards with well over 200
controls that load/display instantaneously and so unless there's
something in play that we don't know about here, it shouldn't take that
long using new instances each time because VBA doesn't have to do any
extra processing of existing variables if they're empty to begin with.
The performance hit comes (in this scenario) when trying to change the
object ref for the ControlSource AFTER it has been done already at
initial load.

As we know, VBA is terribly inefficient at a lot of things and IMO this
certainly can be one of those things!<g>

 

[Tony Miller]

VBA has to first evaluate every use of the variable ref to the new
sheet, then update everywhere that ref is used. It would be faster and
more efficient to close the userform and reopen after specifying the
ControlSource sheet.

Better yet is to implement using the 'New' statement and load the
userform into an object variable, have its Inialize event set up the
textboxes for each instance, and destroy the instance before changing
sheet refs.

  <air code>
  '..in a standard module's Declaration section
  Dim fTemp As Object
  Public wksTarget As Worksheet

  '..in the same module
  Sub Dostuff()
    Set wksTarget = Sheets("sheet1name")
    Set fTemp = New Userform1
    '..do stuff

    '..change sheets
    Set fTemp = Nothing: Set wksTarget = Sheets("sheet2name")

    '..create new instance of userform
    Set fTemp = New Userform1
    '..do more stuff

    'cleanup
    Set fTemp = Nothing: Set wksTarget = Nothing
  End Sub

..where Userform1_Initialize sets up the ControlSource for the
textboxes to the sheet ref'd in wksTarget

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks for your assistance Gary
In the end I revisited my loops and managed to trim them down which
improved the load speed by 50%
The form was getting unloaded before the new sheet was set so
it didnt seem to be the issue
It still was something weird as using a msgbox would ameliroate the
situation

 

[GS]

Tony Miller brought next idea :

Thanks for your assistance Gary
In the end I revisited my loops and managed to trim them down which
improved the load speed by 50%
The form was getting unloaded before the new sheet was set so
it didnt seem to be the issue
It still was something weird as using a msgbox would ameliroate the
situation

Thanks for the feedback!
As I stated in my previous post, how you go about it will affect
performance. Obviously your rework cleanup addressed that and has set
you further ahead.

Why the MsgBox behaves the way it does is because it forces a screen
repaint/refresh so it will display. What you may find, though, is that
what you see only applies to the visible part of the userform when the
msgbox displays.

FWIW
I've found I get the best performance using many controls by loading
the values from a wks by 'dumping' them into an array and processing
that rather than using ControlSource. It's no secret that VBA is
inherently slow when doing read/write to many cells. Processing data in
memory via arrays is orders of magnitude faster and more efficient. The
data can also be 'dumped' back into the wks in one step.

That said, it makes a good reason to design spreadsheets that process
large amounts of data to facilitate using memory instead of read/write
the sheet directly. Optionally, I'll store the values in a delimited
text file and dump that into an array. Of course, it depends on what
the project requires but it's always going to result in better
performance to NOT read/write many cells directly if avoidable!