How to refresh sheet while UserForm is open

[Jay]

In the click even of a button in my form, I am writing code to write
data to the excel sheet behind the form. However, this data is not
refreshed as the form is modal. The data on the sheet is only
refreshed once the form is closed. Is there a way to allow the user to
see the data written to the sheet while the form is still open?

I do not need continuous refresh - only at the end of my click
event...something like this....

Private Sub Btn_Click()

' code to write data to the sheet visible behind the form

' refresh the sheet so that the user can see what was written to the
sheet

' back to form - allow user to click another button etc.

End Sub

 

[Guest]

Jay,

Maybe try setting the userform to display as non-modal. If you don't want
the user to be able to make changes on the spreadsheet while the form is
visible, you can add this line to the Initialize event of the form:

Application.Interactive = False

Excel will block all keyboard and mouse input to the worksheets but not the
user form. When the user form terminates, make sure you turn it back on by
setting it back to True.

 

[Jim Cone]

Change Application.ScreenUpdating to True after writing the data.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Jay" <[email protected]>
wrote in message
In the click even of a button in my form, I am writing code to write
data to the excel sheet behind the form. However, this data is not
refreshed as the form is modal. The data on the sheet is only
refreshed once the form is closed. Is there a way to allow the user to
see the data written to the sheet while the form is still open?

I do not need continuous refresh - only at the end of my click
event...something like this....

Private Sub Btn_Click()
' code to write data to the sheet visible behind the form
' refresh the sheet so that the user can see what was written to the sheet
' back to form - allow user to click another button etc.
End Sub