Dynamically load Userform Label and textbox if name elegible

[nigelog]

I would like to load names of personnel holding valid site permits on userform initialize taking data from a worksheet - using a piece of code I was looking at on this site earlier what changes would I have to make to load a text box beside each label.. desired output is in workbook attached. Any help appreciated

Please Login or Register  to view this content.

[Greg M]

Hi again,

Take a look at the dynamic UserForm in the attached workbook and see if it does what you need.

The UserForm uses the following code:

Please Login or Register  to view this content.

You'll see that the basic UserForm contains only the three Site labels (without meaningful captions) and a single Exit CommandButton - all of the other controls (Labels and TextBoxes) are generated and populated dynamically on the basis of the information contained in the Employee Data table on the Data worksheet,


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

Regards,

Greg M

[LJMetzger]

Hi nigelog,

I didn't look at Greg's solution, but here is something else for you to look at. I prefer to have most of my code in an ordinary code module. It is a matter of personal preference. It makes the UserForm code smaller, and allows me to find the UserForm event handlers sooner.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

In the UserForm Code Module:

Please Login or Register  to view this content.

In Ordinary Code Module Module1:

Please Login or Register  to view this content.

Lewis

[nigelog]

Thankyou both for your very accurate replies. I went with the former solution as I was more familiar with the layout. I have adapted the code to suit the 6 sites I require and only have one problem. I cant seem to invoke the "warning days" change of format to red. I have changed the number of days but as yet have found why not activating


SORTED - my fault - have incorporated all into main file menu and works perfectly. Many thanks again. Will use this format to sort a few other displays....

[Greg M]

Hi again,

Many thanks for your feedback and also for posting the final version of your workbook - looks impressive!

You're welcome - glad I was able to help.

Regards,

Greg M

[nigelog]

Hi Greg, used the auto upload code to negate the need for adding new names to original menu when updated. Just need now to create code to amend details or add new drivers to driver details sheet. Thanks for the pointers on this. Has been a worthwhile update of an old menu - have to also work out format issue with warnings date....

[Greg M]

Hi again,

Many thanks for posting this and also for your private message.

Your original UserForm is looking pretty fancy at this stage - well done!

It's been very interesting for me to follow how your projects have developed - most times I post a solution, get a quick thank-you reply (sometimes not even that! ), and that's the end of the story, so many thanks for keeping me updated.

Let me know if there's anything else you think I can help you with.

Best regards,

Greg M