userform index match from another workbook

Hi there,

In the absence of further information, this seems a fairly convoluted way of going about things.

I imagine that the caption (not value) of the Label on your UserForm is set before the UserForm is displayed (i.e. the Label caption does not change while the UserForm is visible). If this is the case, then the value to be displayed in the TextBox is known (i.e. can be calculated) before the UserForm is displayed - would it not be more straightforward to populate the TextBox by calculating its value via your formula on the worksheet (as before) and then passing that value to the UserForm via a Property before the UserForm is displayed?

Maybe I've totally misunderstood what you have in mind!

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

Regards,

Greg M

I have arranged that each textbox is numbered the same as its corresponding label label2=textbox2 through to label23=textbox23

instead of loading each and every reference can I write code in such a way that for each textbox2-23 is the index of label2-23 where the required info is label content in column a and the required textbox content is in column b
any pointers appreciated

Hi again,

Thanks for your reply.

I think the first thing you'll need to do is to rename the Labels and TextBoxes on your UserForm with appropriate names, e.g. lblHaxChem_01, txtHazChem_01, lblMoffet_01, txtMoffet_01 etc. It will be a bit of a pain to rename them all, but I think that's the only way you'll be able to achieve what you want.

When you've done that, you need to create an array which takes all of its data values from your database worksheet, and then pass that array to the UserForm (before the UserForm is displayed).

The UserForm should contain code which scans through the array of data values and then assigns those values to the various TextBoxes.

The above process isn't trivial, but it's more tedious than complicated, and the end result will be a well-designed UserForm which justifies the effort you put into creating it.

I can probably help you out if you're having difficulty with any of the above steps.

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

Regards,

Greg M

Hi again,

Our last posts obviously crossed each other!

I think you'll find that future modifications to your UserForm will be handled more easily if you rename the Labels/TextBoxes in groups (HaxChem, Moffet etc.) rather than simply using sequential numbering - this comment is born out of long experience of changing UserForms!

Please keep me posted.

Regards,

Greg M

Hi again,

Just another comment.

Good coding practice emphasises the use of encapsulation - in essence this means that UserForms should not need to "know" anything about their associated worksheets. For this reason, all of the values required by the UserForm should be passed directly to it (e.g. by using a data array as mentioned previously) rather than by allowing the UserForm to access the worksheets. Using this approach means that the UserForm code will be unaffected by any future changes to the layout of data worksheets.

Regards,

Greg M

Hi again,

Ok, thanks for that.

Your project seems interesting! Please keep me posted re your progress and let me know if there's anything you think I can help you with.

Hope the weather in Cork is a bit warmer than it is in Dublin

Regards,

Greg M

@Greg
Freezing here too

this would not have been my preference to display the data but what they want....



I am looking at ways to populate the textboxes (a very simple index/match in normal circumstances) but can find nothing either with referencing an array from a label content or any offset search method using the label content.

I need to keep in mind that the main purpose of this is as an alert of impending expiry dates so it has to be able to highlight when within 30 days

Think I need to try to change the title of the thread as becoming overlooked

Hi again,

Take a look at the attached workbook and see if we're going in the right direction.

I've set the no of "Warning Days" to 300 in the following code so that you can see the effect - obviously you'll probably need to set this to a more realistic value:

Please Login or Register  to view this content.

Unfortunately you'll probably have to increase the size of all of the TextBoxes in order to be able to read their contents properly


Hope this helps - as always, please keep me posted.

Regards,

Greg M

Greg you are a gentleman
You did a fair bit of tidying...
Perfect job and I'll see what I can do with regard to display - might just create a dbl_click larger form.. let me have a look for a while... ta

Ok, thanks for that - good to know we're heading in the right direction!

Keep me posted,

Greg M

The new structure of the code has given me a lot to think about - while increasing textbox sizes and adjusting layout I noticed dates in US format - trying to find where to change format to "dd/mm/yyyy" for consistency.. Thanks Again Greg

workbook with name problem

Hi again.

Sent you a private message - will look at this when I have access to Excel later this week.

Regards,

Greg M

Workbook with problem......

Hi again,

Take a look at the attached version and see if I've got things right.

As far as I could make out, the problem (Type Mismatch error?) was nothing to do with the name itself, but rather to do with the due date associated with that name. The due date cell contained a value of 31/09/2017, but as there are only 30 days in September, that value was being read as a string value (not as a date) - hence the Type Mismatch error when the code tried to assign a string value to a variable which was defined as a Date.


To make the overall code a little more consistent, I've moved the code from the UserForm_Terminate routine to the CloseForm routine in the standard VBA CodeModule - the code associated with opening the UserForm is contained in that CodeModule, so it's a bit more consistent to have the code associated with closing the UserForm in that CodeModule also.


Hope this helps - as always, please keep me posted.

Regards,

Greg M

Thanks Greg... how the small things catch you. I am having to add further userforms to this project and I am persueing the idea of loading the labels and textboxes dynamically at initialise of the driver elegible. I think this would negate having to create the form and add new label/textboxes in the future. See thread

http://www.excelforum.com/showthread.php?t=1171252

Once i get a start I can adapt then to make all of the userforms the same

Many thanks Greg....