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" wrote in message
...
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

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" wrote in message
...
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

Yes, that should do it.

--
Regards,
Tom Ogilvy

"Joe Mathis" wrote in message
...

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" wrote in message
...
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

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

"Joe Mathis" wrote in messag
..
Thank you, Tom
I think what I will do is create the max number of text boxes and onl
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
Would the syntax be: "stfary(i, 6) = .Cell.Row" with respect to th
code below
Thanks
Jo
----- Tom Ogilvy wrote: ----
A user can't edit a listbox. They could select an item (row) in
listbo
and you could populate textboxes with the values from that selection
The
the user could do the editing in the textboxes. ( or if you want t
restrict entries to a list, you could use a single column combobo
(for eac
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 t
you
array and record the row of the source data. Then use that t
contro
writing back
--
Regards
Tom Ogilv
"Joe Mathis" wrote in messag
..
Hello everyone
I have a very large data table that stores project informatio
(20+
columns wide and dynamic in length. I have built a user form, fo
editin
purposes, to pull data from certain cells based on the project numbe
entered. I have adapted code from this forum (I believe from To
Oligvy)
that pulls the data quite well. The project number can appea
multiple time
and most of the data is constant, within the project, except for
column
that contain staff members and hours assigned. In the above mentione
cod
(shown below) I create an array to hold the variables from the
columns.
have tested the code by writing the values to a blank sheet to chec
fo
data integrity. Now is my dilemma....
I am trying to figure out the best way to present these variable
to th
user for editing. There can be 1 to 10 staff members present on
give
project. My first thought is to send them to a list box for the use
t
edit. I don't know how to code this and then write the data back t
th
original cells
Any thoughts?
Thanks, Jo
Private Sub ProjNumEditButton_Click(
Dim stfary(10, 5
Me.ProjNumEditTextBox.BackColor = RGB(153, 153, 153
Me.ProjNumEditTextBox.Locked = Tru
i =
With Worksheets("Source Data"
For Each Cell In .Range("e2:e500"
If Cell.Text = ProjNumEditTextBox.Value The
ProjectStatusTextBox.Value = .Cells(Cell.Row, 1).Valu
ProjectNameTextBox.Value = .Cells(Cell.Row, 4).Valu
ProjNumTextBox.Value = .Cells(Cell.Row, 5).Valu
BudgetRemTextBox.Value = .Cells(Cell.Row, 7).Valu
ABTextBox.Value = .Cells(Cell.Row, 8).Valu
TMTextBox.Value = .Cells(Cell.Row, 9).Valu
PMTextBox.Value = .Cells(Cell.Row, 10).Valu
stfary(i, 1) = .Cells(Cell.Row, 11).Valu
stfary(i, 2) = .Cells(Cell.Row, 12).Valu
stfary(i, 3) = .Cells(Cell.Row, 13).Valu
stfary(i, 4) = .Cells(Cell.Row, 14).Valu
stfary(i, 5) = .Cells(Cell.Row, 15).Valu
i = i + 1
End If
Next
End With

Yes, I didn't notice the period/full stop preceding Cell. It shouldn't be
the

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

--
Regards,
Tom Ogilvy

Joe Mathis wrote in message
...
Tom,

Sorry to trouble you, but the syntax

stfary(i, 6) = .Cell.Row

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,

Joe

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

Yes, that should do it.

--
Regards,
Tom Ogilvy

"Joe Mathis" wrote in message
...
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" wrote in
message
...
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