Dynamic userform not updating

[RobVerheyden]

Hi guys,

New to the forum, not to programming in vba
Unfortunatly I'm completely stuck on the following issue (and getting quite desperate...):

I have a code which will dynamically add a userform which should act as a progressbar.
I can only add the userform dynamically through my code unfortunatly as this excel comes with a macro from another program.

The issue I'm having is that I can update the caption and the width of the label as needed (I checked in the immediate window and this is done without a problem) but the userform isn't updating so it will always just show "0% completed" and the "progress bar" doesn't run...

I open the userform modeless in an attempt to run the rest of my code while displaying my progressbar...

The code is redacted but the important part are here:

Please Login or Register  to view this content.

I hope someone will find what I'm doing wrong, I've seen thousands of posts regarding a similar issue but all those solutions didn't work (repaint, DoEvents, put in sleep for a few milliseconds,...)

UPDATE:

Maybe worth mentioning, I forgot this before, my code works fine if I run the code 'manually', sub by sub...

[Keebellah]

You LOAd the userform in your macro code and then SHOW it and update while your macro runs.
Don't forget to Unload it after the macro is completed.
Hope it makes sense to you

[RobVerheyden]

You LOAd the userform in your macro code and then SHOW it and update while your macro runs.
Don't forget to Unload it after the macro is completed.
Hope it makes sense to you

Hi Keebellah,

Thank you for your reply, seems like I redacted a bit too much I have the Unload function there, but I took it out my appologies... I changed the code in the original post now, thank you for the info

[Keebellah]

Good to hear. Happy coding

[RobVerheyden]

Good to hear. Happy coding

wait, to be clear, its not working yet I had the code in the original, just deleted it in my redacted code

[Keebellah]

Here's a sample I found and redacted to my needs. Try the code for the commandbutton1, I think this you could use for your macro

[RobVerheyden]

Hi,

This code is 99% the same as mine, the only thing I didn't have (anymore) was the application.wait function.
I tried adding it again but it didn't work...

I think an important thing to keep in mind is that I have to dynamically create the userform...
In the sample the forms are already created in the Excel before running the code...

If I run manually (so my form is first created) it does work, so I guess it really has something to do with the fact that the userform isn't properly "loaded" into excel...

[rorya]

Why do you have to create the userform itself in code?

[RobVerheyden]

Why do you have to create the userform itself in code?

Without going TOO technical :

Our software creates a new excel file with data each time a report is run.
We have the option to add a macro to our excel which will be run the first time a new excel-file is opened.

We do this because a lot of our clients don't have the "technical skills" to even remember to click on a button each time after they opened the excel file for example...

UPDATE: Trust me though, if there was another way I would use it

Regards,
Rob

[Keebellah]

The reason to not create the form in code is so that you can use it for more than one ocassion and don't have to retype (copy) the same code over and over again.
Just one form one reference and fill it, that's the idea, I've got a project with many loops and processing and only one form and just the code to update the progress bar.

[RobVerheyden]

The reason to not create the form in code is so that you can use it for more than one ocassion and don't have to retype (copy) the same code over and over again.
Just one form one reference and fill it, that's the idea, I've got a project with many loops and processing and only one form and just the code to update the progress bar.

I get that, but we unfortunately don't have this possibility

[Keebellah]

How about usign an xlam file instead, it loads in memory (not necessarily have to save it as addin) and will make all macros (and userforms) available at all times

[rorya]

So all this code is added to the new file at runtime?

[RobVerheyden]

How about usign an xlam file instead, it loads in memory (not necessarily have to save it as addin) and will make all macros (and userforms) available at all times

Truth be told, I've tought about this but it's not an option... There is no way of putting this .xlam file on a location that all users of the code could access...
And it's no option to make this available on multiple location for the sake of ease of use.

The idea would be to have the progress bar run from the excel file generated by the program... It's the only way in this case unfortunately...

regards,
Rob

[RobVerheyden]

So all this code is added to the new file at runtime?

yeah, it's added in the program which generates the excel file.
so it's not that we have to write it again each time but it is generated each time...

regards,
Rob

[rorya]

So your program generates an excel file, adds code to it and that code then adds the userform at run-time? If so, why can't you just add the userform before you add the code?

[RobVerheyden]

Because the program doesn't have that option, the program generates an excel file with one module, in that module we can type code, in sheet1 of the excel we can put the data we want.
Those are the only two options

Trust me when I say the only option is to create the UserForm dynamically, I only have access to macro in order to do something in Excel...

Regards,
Rob

[rorya]

Then I suggest you amend the planning code to take the form as an object parameter, and add a function to loop through the Userforms collection, testing each one's name and returning the one you want. Or just use the status bar or a temporary worksheet as the progress indicator.

[Greg M]

Hi there,

The attached workbook might offer some assistance - it displays a progress indicator created from Shapes rather than a UserForm.

The workbook uses the following code:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[RobVerheyden]

Hi,

Unfortunately this results in a similar problem...
The Shape is made visible but then it will not update...
If I don't delete the shape afterwards it will show 100% completed but yeah I know that then...

I've tried adding a DoEvents in the code but it doesn't seem to do much...

Regards,
Rob

[Keebellah]

Just to understand. This program that creates the Excel file:
1- is this macro embedded file or just a plain xlsx file?
2 - The location for this file is this a common location, one that all users can access?
3- The filename for this file is it always the same or is the name let's say a timestamp or a date, what does the filename look like? I may assume that there is a pattern to the naming of this file.

If 1 is true then point 2 must be true
If point 1 is true can extra macro code be added on-the-fly?
whatever point 3 that will help me think of what I could maybe put together.

BTW: This 'program' that creates the report file, which one is it?

[RobVerheyden]

Hi,

1. The program creates a .xlsm file
2. The location does not matter, I can access it and it doesn't work for me... (The location is not relevant since everyone can save the file wherever they want (different info in the file).
3. Same as 2, filename doesn't matter

I manage to add extra code "on the fly" as in, I can put the macro in before creating the report, but I managed to add a macro to the form "on the fly"

The program which creates the report is called Protime (a T&A software), it's the company I work for...
I know for a fact you won't be able to find any more info online then the one I can Provide you with

Kind regards
Rob

[Keebellah]

So it is not the program, but you yourself that adds the vba code?

[RobVerheyden]

I'm not sure I get exactly what you are asking but in a way...

So, I write the code inside a coding module in the program, the program then creates an excel file including my code as a macro which is run automatically.

I hope this answers your question?

Kind regards,
Rob

[Keebellah]

I think it's clear.
I think you need to apply the progress bar to the SatusBar, I don't see the option to create a userform and all that code to include progress bars.
I'll use the code in your first post and see if I can help from there. I've got an idea but its easier to write as code than to explain.

[RobVerheyden]

I think it's clear.
I think you need to apply the progress bar to the SatusBar, I don't see the option to create a userform and all that code to include progress bars.
I'll use the code in your first post and see if I can help from there. I've got an idea but its easier to write as code than to explain.

Hi, the thing is... I have the code that creates the form and everything...
It also works if I create the form (via the macro) first and then manually start the macro again from the point after that...

It's only when I want to do everything at once that excel fails me...

Kind regards,
Rob

[Keebellah]

Clear, will read the code and see if I can one way or another reproduce it here

[Kyle123]

Does this work for you?

Please Login or Register  to view this content.

[RobVerheyden]

Unfortunately the solution with passing it as an object doesn't seem to work either...

I'm starting to fear it might not be possible at all what I'm trying to get done...
The problem is that all the values are put correctly (which I can see in the immediate window) but the form doesn't update visually while running the code... The DoEvents and/or .repaint aren't doing what I expected them to do...

regards,

[Keebellah]

I think I found something,let me test it,
Have to go out now but will pick it up later

[Kyle123]

It works fine for me, exactly how are you running it/what steps do I need to go through to replicate your problem.

Here's what I did, put the code in Module1 and then simply ran PTMain

[Keebellah]

I Made some modifications to the original code you posted.

Please Login or Register  to view this content.

Give it a run

[Keebellah]

Hi Ron, You probably haven't had the time to look at the macro yet or it's because it's almost weekend
I've placed the macro plus an addition for a button which you could probably use as well
It's in the attached file with will run PT_Main when it's opened and then you can press the button START PLANNING to manually trigger that routine.
The Userform is created on-the-fly and is deleted again after wards.
I added some comments in the VBA code explaining one or two things
Have a fine weekend