J
Joe Mathis
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
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