![]() |
Updating text boxes on forms
I have a text box on a form that I'm using to keep the user updated on what
the macro is doing after clicking on 'OK' (the form stays open). So at different intervals during the running of the code, I use the command (or something like it): ufSelectRun.tbxStatusBar.Value = "downloading data..." where ufSelectRun is the userform and tbxStatusBar is the text box. However Excel seems to be quite selective about when it updates. So whilst the command may be the first line of code post variable declaration, the text box doesn't seem to update until the entire subroutine has finished. Does anyone know the mechanics of how forms update? How do I get it to show the updates at the right time? |
Updating text boxes on forms
hi
look up the DoEvents command in vb help. this command turns control over to the operating system to complete commands in it's cue (such as refresh the screen) then return control back to the macro. you might also look up the status bar function in vb help as an alternative to the use of a text box on the form. I prefer the status bar. regards FSt1 "Matt McQueen" wrote: I have a text box on a form that I'm using to keep the user updated on what the macro is doing after clicking on 'OK' (the form stays open). So at different intervals during the running of the code, I use the command (or something like it): ufSelectRun.tbxStatusBar.Value = "downloading data..." where ufSelectRun is the userform and tbxStatusBar is the text box. However Excel seems to be quite selective about when it updates. So whilst the command may be the first line of code post variable declaration, the text box doesn't seem to update until the entire subroutine has finished. Does anyone know the mechanics of how forms update? How do I get it to show the updates at the right time? |
Updating text boxes on forms
Problem with the status bar is that nobody ever notices it. A progress bar
is in the user's face. I use a modeless userform, update the displayed text (and also the length of the progress bar), then repaint the form. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "FSt1" wrote in message ... hi look up the DoEvents command in vb help. this command turns control over to the operating system to complete commands in it's cue (such as refresh the screen) then return control back to the macro. you might also look up the status bar function in vb help as an alternative to the use of a text box on the form. I prefer the status bar. regards FSt1 "Matt McQueen" wrote: I have a text box on a form that I'm using to keep the user updated on what the macro is doing after clicking on 'OK' (the form stays open). So at different intervals during the running of the code, I use the command (or something like it): ufSelectRun.tbxStatusBar.Value = "downloading data..." where ufSelectRun is the userform and tbxStatusBar is the text box. However Excel seems to be quite selective about when it updates. So whilst the command may be the first line of code post variable declaration, the text box doesn't seem to update until the entire subroutine has finished. Does anyone know the mechanics of how forms update? How do I get it to show the updates at the right time? |
Updating text boxes on forms
Jon,
Er... I have that creeping feeling that I'm in over my head. I'm not certain I've grasped why my code doesn't change the value of the textbox until the entire subroutine it's in is completed. Would making my userform modeless cause commands to be executed as written? Second stupid question: when you say repaint the form, what do you mean? Cheers, Matt "Jon Peltier" wrote: Problem with the status bar is that nobody ever notices it. A progress bar is in the user's face. I use a modeless userform, update the displayed text (and also the length of the progress bar), then repaint the form. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "FSt1" wrote in message ... hi look up the DoEvents command in vb help. this command turns control over to the operating system to complete commands in it's cue (such as refresh the screen) then return control back to the macro. you might also look up the status bar function in vb help as an alternative to the use of a text box on the form. I prefer the status bar. regards FSt1 "Matt McQueen" wrote: I have a text box on a form that I'm using to keep the user updated on what the macro is doing after clicking on 'OK' (the form stays open). So at different intervals during the running of the code, I use the command (or something like it): ufSelectRun.tbxStatusBar.Value = "downloading data..." where ufSelectRun is the userform and tbxStatusBar is the text box. However Excel seems to be quite selective about when it updates. So whilst the command may be the first line of code post variable declaration, the text box doesn't seem to update until the entire subroutine has finished. Does anyone know the mechanics of how forms update? How do I get it to show the updates at the right time? |
Updating text boxes on forms
hi
I think i addressed that in my first post. lace your code with doevents. that will refresh the screen. as to repaint..... me.repaint this will cause a refresh also but only for the form. this might work too. I haven't tested.... place either command after the form is suppost to change. as to the status bar...jon is right. few people do notice. but progress bars reguire extra forms with extra coding. and to me...extra hassel. status bar can be mixed with the code at statigic points and if you inform your users on how to use the file then status bar should be one of the lessons. we all have our preferences. more than one way to skin a elephant. regards FSt1 "Matt McQueen" wrote: Jon, Er... I have that creeping feeling that I'm in over my head. I'm not certain I've grasped why my code doesn't change the value of the textbox until the entire subroutine it's in is completed. Would making my userform modeless cause commands to be executed as written? Second stupid question: when you say repaint the form, what do you mean? Cheers, Matt "Jon Peltier" wrote: Problem with the status bar is that nobody ever notices it. A progress bar is in the user's face. I use a modeless userform, update the displayed text (and also the length of the progress bar), then repaint the form. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "FSt1" wrote in message ... hi look up the DoEvents command in vb help. this command turns control over to the operating system to complete commands in it's cue (such as refresh the screen) then return control back to the macro. you might also look up the status bar function in vb help as an alternative to the use of a text box on the form. I prefer the status bar. regards FSt1 "Matt McQueen" wrote: I have a text box on a form that I'm using to keep the user updated on what the macro is doing after clicking on 'OK' (the form stays open). So at different intervals during the running of the code, I use the command (or something like it): ufSelectRun.tbxStatusBar.Value = "downloading data..." where ufSelectRun is the userform and tbxStatusBar is the text box. However Excel seems to be quite selective about when it updates. So whilst the command may be the first line of code post variable declaration, the text box doesn't seem to update until the entire subroutine has finished. Does anyone know the mechanics of how forms update? How do I get it to show the updates at the right time? |
Updating text boxes on forms
FSt -
I think I'd tried DoEvents and it didn't work the way I wanted. Matt - Once you show the form, if it's modal, the code stops executing until the form is dismissed (unless you start other code from the form...). You do this at the beginning: ufSelectRun.tbxStatusBar.Value = "downloading data..." What you can do later, if the form's displayed modelessly, is change the textbox and repaint the form: With ufSelectRun .tbxStatusBar.Value = "processing data..." .Repaint End With I use this technique a lot, and clients like the changing feedback. To enhance it, I have two rectangles on the form (I use simple labels without text but with different backcolors). The left edges of the rectangles line up with each other. The top rectangle is initially width = zero, and as the process continues, I pass a fraction into the userform, and the top rectangle is stretched to that fraction of the bottom rectangle's width, obscuring part of the bottom rectangle. Sometimes the fraction complete is easy, like you've done x loops out of a total of y, so the fraction is x / y. Sometimes you don't know the fraction, so you make up something, like after step 1, show 10%, after step 2 show 30%, after step 3 show 35%, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "FSt1" wrote in message ... hi I think i addressed that in my first post. lace your code with doevents. that will refresh the screen. as to repaint..... me.repaint this will cause a refresh also but only for the form. this might work too. I haven't tested.... place either command after the form is suppost to change. as to the status bar...jon is right. few people do notice. but progress bars reguire extra forms with extra coding. and to me...extra hassel. status bar can be mixed with the code at statigic points and if you inform your users on how to use the file then status bar should be one of the lessons. we all have our preferences. more than one way to skin a elephant. regards FSt1 "Matt McQueen" wrote: Jon, Er... I have that creeping feeling that I'm in over my head. I'm not certain I've grasped why my code doesn't change the value of the textbox until the entire subroutine it's in is completed. Would making my userform modeless cause commands to be executed as written? Second stupid question: when you say repaint the form, what do you mean? Cheers, Matt "Jon Peltier" wrote: Problem with the status bar is that nobody ever notices it. A progress bar is in the user's face. I use a modeless userform, update the displayed text (and also the length of the progress bar), then repaint the form. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "FSt1" wrote in message ... hi look up the DoEvents command in vb help. this command turns control over to the operating system to complete commands in it's cue (such as refresh the screen) then return control back to the macro. you might also look up the status bar function in vb help as an alternative to the use of a text box on the form. I prefer the status bar. regards FSt1 "Matt McQueen" wrote: I have a text box on a form that I'm using to keep the user updated on what the macro is doing after clicking on 'OK' (the form stays open). So at different intervals during the running of the code, I use the command (or something like it): ufSelectRun.tbxStatusBar.Value = "downloading data..." where ufSelectRun is the userform and tbxStatusBar is the text box. However Excel seems to be quite selective about when it updates. So whilst the command may be the first line of code post variable declaration, the text box doesn't seem to update until the entire subroutine has finished. Does anyone know the mechanics of how forms update? How do I get it to show the updates at the right time? |
Updating text boxes on forms
hi again.
try the me.frech method. there is no right way or wrong way. there is only ways that work and ways that don't. and if it don't work....that must be the wrong way. sometimes programming is a trial and error method. believe me. been there, done that. i have used both the doevents method and the create form/me.refresh method and both work. if neither work...post your code. something ain't right. regards FSt1 regards FSt1 "Jon Peltier" wrote: FSt - I think I'd tried DoEvents and it didn't work the way I wanted. Matt - Once you show the form, if it's modal, the code stops executing until the form is dismissed (unless you start other code from the form...). You do this at the beginning: ufSelectRun.tbxStatusBar.Value = "downloading data..." What you can do later, if the form's displayed modelessly, is change the textbox and repaint the form: With ufSelectRun .tbxStatusBar.Value = "processing data..." .Repaint End With I use this technique a lot, and clients like the changing feedback. To enhance it, I have two rectangles on the form (I use simple labels without text but with different backcolors). The left edges of the rectangles line up with each other. The top rectangle is initially width = zero, and as the process continues, I pass a fraction into the userform, and the top rectangle is stretched to that fraction of the bottom rectangle's width, obscuring part of the bottom rectangle. Sometimes the fraction complete is easy, like you've done x loops out of a total of y, so the fraction is x / y. Sometimes you don't know the fraction, so you make up something, like after step 1, show 10%, after step 2 show 30%, after step 3 show 35%, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "FSt1" wrote in message ... hi I think i addressed that in my first post. lace your code with doevents. that will refresh the screen. as to repaint..... me.repaint this will cause a refresh also but only for the form. this might work too. I haven't tested.... place either command after the form is suppost to change. as to the status bar...jon is right. few people do notice. but progress bars reguire extra forms with extra coding. and to me...extra hassel. status bar can be mixed with the code at statigic points and if you inform your users on how to use the file then status bar should be one of the lessons. we all have our preferences. more than one way to skin a elephant. regards FSt1 "Matt McQueen" wrote: Jon, Er... I have that creeping feeling that I'm in over my head. I'm not certain I've grasped why my code doesn't change the value of the textbox until the entire subroutine it's in is completed. Would making my userform modeless cause commands to be executed as written? Second stupid question: when you say repaint the form, what do you mean? Cheers, Matt "Jon Peltier" wrote: Problem with the status bar is that nobody ever notices it. A progress bar is in the user's face. I use a modeless userform, update the displayed text (and also the length of the progress bar), then repaint the form. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "FSt1" wrote in message ... hi look up the DoEvents command in vb help. this command turns control over to the operating system to complete commands in it's cue (such as refresh the screen) then return control back to the macro. you might also look up the status bar function in vb help as an alternative to the use of a text box on the form. I prefer the status bar. regards FSt1 "Matt McQueen" wrote: I have a text box on a form that I'm using to keep the user updated on what the macro is doing after clicking on 'OK' (the form stays open). So at different intervals during the running of the code, I use the command (or something like it): ufSelectRun.tbxStatusBar.Value = "downloading data..." where ufSelectRun is the userform and tbxStatusBar is the text box. However Excel seems to be quite selective about when it updates. So whilst the command may be the first line of code post variable declaration, the text box doesn't seem to update until the entire subroutine has finished. Does anyone know the mechanics of how forms update? How do I get it to show the updates at the right time? |
Updating text boxes on forms
OK guys, many thanks, will get to work.
Matt |
All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com