Multi-column ListBox. Multiple bound columns???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone,

I have a very large data table that stores project information (20+) columns wide and dynamic in length. I have built a user form, for editing purposes, to pull data from certain cells based on the project number entered. I have adapted code from this forum (I believe from Tom Oligvy), that pulls the data quite well. The project number can appear multiple times and most of the data is constant, within the project, except for 5 columns that contain staff members and hours assigned. In the above mentioned code (shown below) I create an array to hold the variables from the 5 columns. I have tested the code by writing the values to a blank sheet to check for data integrity. Now is my dilemma.....

I am trying to figure out the best way to present these variables to the user for editing. There can be 1 to 10 staff members present on a given project. My first thought is to send them to a list box for the user to edit. I don't know how to code this and then write the data back to the original cells.

Any thoughts??

Thanks, Joe

Private Sub ProjNumEditButton_Click()
Dim stfary(10, 5)

Me.ProjNumEditTextBox.BackColor = RGB(153, 153, 153)
Me.ProjNumEditTextBox.Locked = True

i = 1

With Worksheets("Source Data")
For Each Cell In .Range("e2:e500")
If Cell.Text = ProjNumEditTextBox.Value Then
ProjectStatusTextBox.Value = .Cells(Cell.Row, 1).Value
ProjectNameTextBox.Value = .Cells(Cell.Row, 4).Value
ProjNumTextBox.Value = .Cells(Cell.Row, 5).Value
BudgetRemTextBox.Value = .Cells(Cell.Row, 7).Value
ABTextBox.Value = .Cells(Cell.Row, 8).Value
TMTextBox.Value = .Cells(Cell.Row, 9).Value
PMTextBox.Value = .Cells(Cell.Row, 10).Value
stfary(i, 1) = .Cells(Cell.Row, 11).Value
stfary(i, 2) = .Cells(Cell.Row, 12).Value
stfary(i, 3) = .Cells(Cell.Row, 13).Value
stfary(i, 4) = .Cells(Cell.Row, 14).Value
stfary(i, 5) = .Cells(Cell.Row, 15).Value
i = i + 1
End If
Next
End With
 
A user can't edit a listbox. They could select an item (row) in a listbox
and you could populate textboxes with the values from that selection. Then
the user could do the editing in the textboxes. ( or if you want to
restrict entries to a list, you could use a single column combobox (for each
column of your data or selected columns) in lieu of a listbox. )

To keep track of the location of the data, you could add a column to your
array and record the row of the source data. Then use that to control
writing back.

--
Regards,
Tom Ogilvy



Joe Mathis said:
Hello everyone,

I have a very large data table that stores project information (20+)
Click to expand...
columns wide and dynamic in length. I have built a user form, for editing
purposes, to pull data from certain cells based on the project number
entered. I have adapted code from this forum (I believe from Tom Oligvy),
that pulls the data quite well. The project number can appear multiple times
and most of the data is constant, within the project, except for 5 columns
that contain staff members and hours assigned. In the above mentioned code
(shown below) I create an array to hold the variables from the 5 columns. I
have tested the code by writing the values to a blank sheet to check for
data integrity. Now is my dilemma.....
I am trying to figure out the best way to present these variables to the
Click to expand...
user for editing. There can be 1 to 10 staff members present on a given
project. My first thought is to send them to a list box for the user to
edit. I don't know how to code this and then write the data back to the
original cells.
 
Thank you, Tom.

I think what I will do is create the max number of text boxes and only make the ones visible that will be needed. I will use your advice and add a column to the array to record the row of the source data.

Would the syntax be: "stfary(i, 6) = .Cell.Row" with respect to the code below.

Thanks,

Joe

----- Tom Ogilvy wrote: -----

A user can't edit a listbox. They could select an item (row) in a listbox
and you could populate textboxes with the values from that selection. Then
the user could do the editing in the textboxes. ( or if you want to
restrict entries to a list, you could use a single column combobox (for each
column of your data or selected columns) in lieu of a listbox. )

To keep track of the location of the data, you could add a column to your
array and record the row of the source data. Then use that to control
writing back.

--
Regards,
Tom Ogilvy



Joe Mathis said:
Hello everyone,
Click to expand...
columns wide and dynamic in length. I have built a user form, for editing
purposes, to pull data from certain cells based on the project number
entered. I have adapted code from this forum (I believe from Tom Oligvy),
that pulls the data quite well. The project number can appear multiple times
and most of the data is constant, within the project, except for 5 columns
that contain staff members and hours assigned. In the above mentioned code
(shown below) I create an array to hold the variables from the 5 columns. I
have tested the code by writing the values to a blank sheet to check for
data integrity. Now is my dilemma.....user for editing. There can be 1 to 10 staff members present on a given
project. My first thought is to send them to a list box for the user to
edit. I don't know how to code this and then write the data back to the
original cells.
 
Yes, that should do it.

--
Regards,
Tom Ogilvy

Joe Mathis said:
Thank you, Tom.

I think what I will do is create the max number of text boxes and only
Click to expand...
make the ones visible that will be needed. I will use your advice and add a
column to the array to record the row of the source data.
 
Tom

Sorry to trouble you, but the syntax

stfary(i, 6) = .Cell.Ro

Gives me the following: Run-Time error '438' "Object dosen't support this property or method

Can you see anything I am missing

Thanks you kindly

Jo

----- Tom Ogilvy wrote: ----

Yes, that should do it

--
Regards
Tom Ogilv

make the ones visible that will be needed. I will use your advice and add
column to the array to record the row of the source data
 
Yes, I didn't notice the period/full stop preceding Cell. It shouldn't be
there:

stfary(i, 6) = .Cell.Row
should be
stfary(i, 6) = Cell.Row
 
Back
Top