In an effort to try to not use named ranges on helper worksheets I would like to see if I can write to a 2D array and use the data in that to populate userforms. I have little knowledge of arrays and might be going the wrong way about it. This code will load two columns but not 3 or 4. I would also like to refer the columns by number rather than letter. Any pointers appreciated.
sample attached
Please Login or Register to view this content.
Last edited by nigelog; 02-02-2018 at 08:11 AM.
Nigel
Arrays and non-contiguous ranges don't really work well together.
For example, it's easy enough to populate an array with data from non-contiguous arrays, as you appear to be doing, but what happens when you want to write the data back? How do you determine which range/column the data is to go to?
What is it you are actually trying to do?
PS Are you thinking of having a 2-dimensional array where the first dimension is the value and the second the column?
If posting code please use code tags, see here.
See if this is how you wanted.
Please Login or Register to view this content.
@Don
in the sample userform the dynamic form gets its data from a named range tblEmployeeData3 from a helper worksheet "data", which in turn is loaded from the main data set. The form looks for dates in a heading column and loads the name and date if qualifies.
There are many forms in the workbook I use and each one needs a helper worksheet which has to be refreshed
I would like to load an array from each userform as activated and use the data from the array to load the form
@ jindon, Ill have a look
Last edited by nigelog; 02-02-2018 at 06:22 AM.
What are you trying to do?Originally Posted by nigelog
@ jindon... see above post
Why are you dynamically creating controls?
Why not have something like this?
A combobox with the headings from Data sheet.
A 2 column listbox for Name and Date.
When a value is selected from the combobox the listbox is populated with the data from the associated column(s) on Data.
@ Norie
these are alert sheets that are loaded when the workbook opens and have to resize, people here will not go looking for the information therefore the userform alerts them to imminent expiry dates for many different areas. To change the format now would create chaos. All I am trying to do is obviate the need for a helper sheet for each one as they are cluttering the workbook...
@ jindon
your code loads an array but how can I view or use the data in the array
Output on in a range to see the elements.
e.g
Still no idea about what you are trying to do, so I'll leave this to someone else.Please Login or Register to view this content.
thanks jindon, array format is how I imagined now want to see if I can use to load a form
I'm trying to use an array rather than a named range on a worksheet to use as the data for a userform
The array elements are in exactly the same order as the range that the userform code uses to populate itself and I am trying to use the array rather than a physical range on a worksheet
The original question has been answered so I will mark as solved
Last edited by nigelog; 02-02-2018 at 08:09 AM.
I used the array output on a temporarily created named range that was used to populate the form. This way I have no helper sheets and can create the named range from the array required for a particular form, all read from the main data set. 10 less worksheets. Thanks for the assistance Norie and jindon
Glad if that helped and thanks for the rep.
Hi Nigel,
Thanks for your message regarding the above, and sorry for the delay in replying - I haven't been online here recently.
See if the following code does what you need:
The highlighted code can be altered to suit your own requirements.Please Login or Register to view this content.
Hope this helps - as always, please let me know how you get on.
Regards,
Greg M
@ Greg
hi Greg, thanks for the input
that code does indeed load the array required, what has me stuck is the function mvaEmployee() that previously used the named range, I am trying to get it to read the created array so I have no requirement for helper sheets
Ive been messing with it but keeps telling the array vaEmployeeData data is empty although it is created(I changed the name due to the references in the userform code)Please Login or Register to view this content.
Hi again Nigel,
Try substituting the following code for the existing mvaEmployeeData function, and see if it does what you need:
The highlighted values may be altered to suit your own requirements.Please Login or Register to view this content.
Hope this helps - please keep me posted.
Best regards,
Greg M
Perfect Greg, thanks for the solution.
Hi Nigel,
Many thanks for the feedback and also for the Reputation increase - much appreciated!
You're very welcome - delighted to have been able to help.
Best regards,
Greg M
Looping takes forever.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks