(e-mail address removed) was thinking very hard :
Hi Gary,
Thanks for the reply but I am afraid I could not understand you
clearly.Is there any way you give me more details?
thanks
Baha
Okay, I'll try this again!
It appears that you are providing textboxes for user input on a form.
There are 15 textboxes for each group of data. There are 38 groups.
This is my understanding so far!
Using only 15 textboxes (instead of 15x38 [570]) keeps things simple
and efficient to manage. Using a loop to iterate each value could also
keep things simple and efficient to manage.
Using a combobox so users can randomly select which group they want to
input data for is also simple and efficient to manage. When an item is
selected the textboxes are cleared (or populated with existing data for
the selected group), and the selection is loaded into a variable to
hold the current selected group.
Use an array to load each textbox value into,
'Declare module-level variables
Dim msaDataArray(16) As String, msCurrentGroup As String
ComboBox1_Change()
msCurrentGroup = Me.ComboBox1.Text
msaDataArray(0) = msCurrentGroup
End Sub
Use the Exit event to load each element of the array according to
texbox. For example, the code in textbox1 would be something like this:
TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
myDataArray(CInt(Me.TextBox1.Tag)) = Me.TextBox1.Text
'where "1" is the Tag property for textbox1
End Sub
Code for textbox2_Exit event...
myDataArray(CInt(Me.TextBox1.Tag) = Me.TextBox2.Text
'where "2" is the Tag property for textbox2
...and so on
Provide a button so the user can process the data for the selected
group after all data is input. When this button is clicked, have code
to process the array and clear its elements for the next set of data.
Optionally, you could include code to clear the array in the
ComboBax1_Change() event, as a means to 'reset' the textboxes and array
for/with values for the new selection.
*******************************************************
If populating textboxes with default or existing values
*******************************************************
This would require a means of storing/retrieving the data for each
group, which you may already have in place. If the textboxes are
'Bound' to their data sources then any changes will update the data
source. I don't suspect this is what you're doing so iterating the
array is probably the easiest way to store/retrieve the data. Using a
UDT might even be easier depending on how/where/if the data is stored.
In this scenario you would populate the textboxes with their respective
stored data, which also is loaded into the array. The idea here is to
read data into the array and from there populate the textboxes
according to their index in the array. Since VBA doesn't support
control arrays, it might be simpler to manage by coding for each
textbox directly from the array. Example:
TextBox1.Text = msaDataArray(1)
TextBox2.Text = msaDataArray(2)
...and so on
The exit event will update the array. Write the array back to the
storage to update that.
**************
Final Comments
**************
If the number of textboxes is variable then you can hide/show
accordingly if you create the max number expected at design time. This
can be managed in the ComboBox_Change() event.
If the data isn't stored anywhere, but just processed on the spot then
this concept of reusing the textboxes for each selected group is even
easier to implement.
All you need to do is track which group the current set of data belongs
to, and process accordingly!
HTH
