Screen updating with textboxes/labels

[Shortz]

Hi everyone,

thanks in advance and down to business,

I've got a workbook and a userform in that workbook (Excel 2003) that will print certain number of sheets depending on the user's selection. That bit i can code fine. The user also inputs in some project information which is added / updated to certain parts of each sheet depending on the sheet.

My current problem is the updating of these sheets when the user inputs amended information into the userform. The problem i've been having is with sheets where the changes are made to textboxes and labels. cells update fine.


For example,

1. user inputs "I am awesome" into the userform, hits print (and makes selections) and all sheets update and print correctly.

2. the user then changes the userform information to "I am extremely awesome" and all the sheets update and print correctly except where the info is being put into textboxes or labels (they still retain the previous "i am awesome"). If you then hide the userform and go do each sheet manually (the ones with the textboxes & labels), the information is now correct.


I know this has something to do with the screen updating, i just can't figure out how to force the textboxes and labels to update BEFORE it is printed.

I've experimented with turning on screenupdating = true before the code to update the textboxes but that doesn't seem to work.

any help would be much appreciated.

An eg of the code i'm using is below

sheets(1).textbox1.value = userform1.projectinfo.value
sheets(1).label1.caption= userform1.projectnumber.value
the code below will update fine prior to printing
sheets(1).cells(1,1).value = userform1.projectinfo.value

[Bob Phillips]

I think a copy of the workbook would help.

[Shortz]

thanks for the reply i've attached a copy of the workbook, its not the complete one but there should be enough to work with.

My problems are with the "Cover sheet" and "site notes" & updating the textboxes/labels in them.
(http://www.vbaexpress.com/forum/)

[Sean.DiSanti]

without looking at your spreadsheet, probably just easiest to add code to update the relevant caption/text properties of the controls explicitly. you can put it in the code for your form, or even in the "SelectionChange"

[Shortz]

without looking at your spreadsheet, probably just easiest to add code to update the relevant caption/text properties of the controls explicitly. you can put it in the code for your form, or even in the "SelectionChange"

how would the code differ to update it explicitly?

[Sean.DiSanti]

in the change event of the text box where the change is being made; just add lines like:
Worksheets(1).Textbox1.Text = Userform1.Textbox1.Text
etc

[Shortz]

ok thanks, i just tried it, but it still doesn't update it before the sheets print.

Do i need to get the whole sheet to "reload" prior to printing?

[Sean.DiSanti]

just put the code to update those fields on the code of your print button; prior to printing.... that way it updates then prints?

[Shortz]

Still doesn't work. still seems based on whether the page reloads prior to printing/preview. Should i just add a pause code to repaint the sheet?

[Shortz]

Ok i think i need to find a way to force excel to update or reprint two worksheets once the textboxes have been updated. It seems that i needs to be reprinted otherwise it won't printscreen or print properly.

Is there a way to achieve this?

[Sean.DiSanti]

one second, i'm going to download your workbook and play with it

[Sean.DiSanti]

in your Sheet_Printer() you disable screen updating... make sure you're updating values before that line...

[Shortz]

Could you please test something for me?

Add some project info into the userform, then select cover sheet and one site notes then hit preview.

exit print preview, ctrl + m, then change the project data in the userform and hit preview again to see if it updates.

[Sean.DiSanti]

i'm not sure if it the file or my POS computer here at work, but when i open it, it crawls sooooooo slow

[Bob Phillips]

Keeps crashing my Excel so I can't do much with it.