on 11/30/2011, Tony Miller supposed :
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

--
Garry

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