Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form / Field Arrays...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form / Field Arrays...
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form / Field Arrays...
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. -- --jkitzy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form / Field Arrays...
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" wrote: 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. -- --jkitzy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form / Field Arrays...
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... -- --jkitzy "Joel" wrote: 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" wrote: 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. -- --jkitzy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked excel field from access form field - help required. | Excel Programming | |||
Active X Controls Tabbing from Form Field 2 Form Field | Excel Discussion (Misc queries) | |||
Reading Access Form Field and Copy to Excel Field | Excel Programming | |||
Copy form field value to another form (calendar) | Excel Programming | |||
How do I set up excel to tab from field to field in a form? | Excel Discussion (Misc queries) |