Form / Field Arrays...

[jkitzy]

I'm not sure if I can do this or not, and I couldn't quite find the solution
here so I thought I'd ask...

I have a form with multitab controls for bits of info. There are about 100
or so total fields (some text, some combo, some checkboxes) across the many
tabs.

I'm using a combobox to select a record from my spreadsheet (there are about
600, and I know ultimately Access would be a better solution but I'm stuck
with Excel). I then assign a variable to hold the row number of the record so
when we make changes, they can go to the right place.

My problem... And I'm no expert here...

I'd rather not code each field to get it's value from the master combobox
control, nor would I rather code each "After Update" event to store the
value. That's a lot of code. And there must be a better way.

I have a naming convention for my controls (f000) and they're numbered based
on the column they come from, so when I assign my value to, say, f001, it's
from row(myCust), column 1. Make sense?

What I'd like to do is have any of these f000 fields assigned their value by
the myCust row and f000 column, but without having to put that indivdually
for each item.

::sigh - I don't even know if that's clear::

Anyone have a good idea to do this? I know I'm probably just having a brain
lapse and there's an obvious solution I'm overlooking.

Thanks for any help!

--jak

 

[Joel]

try something like this for a check box which has true or false values. the
control method can be used for comboboxes, Listboxes, Textboxes, ...

For i = 1 To 100

UserForm1.Controls("F" & Format(i,"#000")).Value = True

Next i


This allows you to access a control by name such as "F001". The format
statement add the two leading zeroes to the number i.

 

[jkitzy]

Joel,

Thanks! That's terrific. That does let me load the values as desired... I
knew it had to be, like, a couple of simple lines of code!

Any ideas on a short way of reversing the process? So that when I make a
change, I don't have to have code to write the new value in each cell?

For instance, when I change the value of f001, I want it to write the new
value to myCust, 1. Ditto f050. BUT, I'd rather not put code into each
AfterUpdate event. I know I could put an update button, and that may be the
solution, but if I can automate it without the data-entry user pressing a
button, that'd be preferable.

 

[Joel]

The Controlsource of the Combobox will put the index of the selected item.
You can also fill the combobox with Rowsource ( ListRange if you are using a
box on the worksheet).

 

[jkitzy]

Ah! Of course. Thanks again for you help. Nothing like a short time frame to
make one forget all the easy stuff... Thanks again. You saved me from
creating a lot of sloppy, sloppy code...