Progress bar while adding data from Userform ( while macro runs )
Hello everyone, as i am new to excel vba i need some help for running a progress bar while i add data from userform. When i click add button progress bar should run till the "adding data" code runs. And finally want to show a massage box too say that process is successful. Below is the simple code i use to add data from userform to excel worksheet.
Thank you for your help in advance
Please Login or Register to view this content.
Last edited by AliGW; 07-31-2022 at 09:45 AM. Reason: Code tags added.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...-runs.1212153/)
Administrative Note:
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. As you are new here, I shall also do this for you today.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
How long does this sub take to run that you actually need a progress bar?
Unless the called sub (ItemCode) takes a very long time to run (we have no idea on that because you haven't posted that code or a sample workbook with your code included) then the sub you show above should run pretty quickly and would probably be over before the end user even wondered about how far along it was.
Attach a desensitised version of your file and we can help further, but I feel there's little more we can do to help without more information.
BSB
It won't take much time. As you seen in the code, its only 4 entries also saving the workbook. And i am sorry i don't know how to calculate time
Last edited by AliGW; 08-01-2022 at 01:04 AM. Reason: Please DON'T quote unnecessarily!
I wasn't actually asking for details on exactly how long it takes, I was making the point of why do you need to bother with a progress bar when the code will run in mere moments. Likelihood is the code will be over and done with before anyone even notices the progress bar so it all seems a bit of a waste of effort to me.
BSB
Well yes. It will finish soon. But i want to try a progress bar with that cause i like to know the coding to this type of data entry. means i like to insert a progress bar for the big entries in the future. Can you help me please...?
Attached is a zipped VBA form. It's a simple progress bar with 3 boxes on it.
The first is the background colour for the progress bar, the second is the progress bar itself and the third is a transparent window to use for placing text in the progress bar. The progress bar is designed to run in a loop with a loop counter, I haven't specifically tailored it for your code. The following code can be used to update it but will need editing to fit:
To initialise:
To move the progress bar:Please Login or Register to view this content.
Please Login or Register to view this content.
To close the progress bar:
Please Login or Register to view this content.
Last edited by BellyGas; 08-01-2022 at 05:53 AM.
to be able to interrupt your running code with a progress bar can be fraught with problems.
this is mainly because every system runs at varying speeds and needs complex code to control.
the approach I take is to split my ops between the timing update (the majority of code can run at the beginning then the progress bar just becomes a cosmetic addition to your app.
Please Login or Register to view this content.
Torachan,
Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.
Thank you very much for your code. I am looking for something like this. But sorry as i am new to coding world i don't know where to place it and how to place it in my code. Could you please correct my coding as suits to your progressbar..?
Please Login or Register to view this content.
Also i am getting an error saying "out of memory" when trying to import the form through Excel 2016. Why is that?
@charly1988, are you replying to me (torachan) , there is nothing in my code that would cause "out of memory"
upload your actual file - I will then put the code in the right place for you.
yes. Thank you very much for your help. And also believe me im having trouble when opening the form. Could you please send me the form with excel file too..?
everything is in the file attached to post #8 - what is it that you cannot do.
upload the file you are trying to load the progress bar to - otherwise this develops into a 'guessing game'
here i attach my file
So as you can see in the attached file, when i click add button in the userform, a progress bar has to run while data has been added to the sheet.
I attached my file in the #14 postOriginally Posted by torachan
the form is not working. Couldn't open. Getting an error, saying that "out of memory"Attached is a zipped VBA form. It's a simple progress bar with 3 boxes on it.
The first is the background colour for the progress bar, the second is the progress bar itself and the third is a transparent window to use for placing text in the progress bar. The progress bar is designed to run in a loop with a loop counter, I haven't specifically tailored it for your code. The following code can be used to update it but will need editing to fit:
To initialise:
To move the progress bar:Please Login or Register to view this content.
Please Login or Register to view this content.
To close the progress bar:
Please Login or Register to view this content.
your sheet has data saved at rows 162300 , 16230, 1623, 162 maybe causing you problems
omy thats true... i haven't notice that. thank you very much for notifying me. i deleted those data in my sheet right now. did you correct my coding for include a progressbar?your sheet has data saved at rows 162300 , 16230, 1623, 162 maybe causing you problems
attached has progress bar added - it activates when you 'ADD' a new line of data.
I have converted your data to use a 'structured table' - this is self maintaining - you have no need to interrogate size as it will expand/contract as you add/delete data.
Never add extra blank rows to a table as this will confuse the row counter.
thank you very very much... this is what im exactly looking for. thank you again.....you are so amazing.
glad to have helped - thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks