VBA to edit/update cells within a large table from a common "Dashboard"

[brianv]

This is question #2 of 2 that I have for my "Dashboard" that I need assistance on. The other question will be posted separately.

I have a fairly large table "ProjectData" Active!B15:CZ150. Each row in B:B is the actual job number, but the numbers may not be consecutive and they include alpha characters as well, ex: 25AC5171, 25AD2230 or 25AS0578. Then the columns are a variety of project data info, from job name to close out dates. Obviously, this is in construction and as new job are added or archived the table gets bigger and smaller.

Well it has become very difficult to scroll through all that data in the table, so I am created a "Dashboard" to view all the data in one sheet:

Cell I4:O4 is a Data Validation List "_ProjectName" (Active!C15:C150), then cell R7 is a xlookup to get the project # from cells Active!B15:B150 and all the rest of the data shown in each cell throughout the Dashboard is based on a combination vlookup/hookup to get the data from the intersection of the job number and column title.

For example:
Cell F16 lists the 'Sold Value' of the project so its cross referencing the Job Number of R7 and the Column Title as shown above each cell data, in this case F15 so its looking at the date intersection in the 'ProjectData' table on the 'Active' sheet.
Formula is: =VLOOKUP($R$7,Active!$B$15:$CW$70,HLOOKUP(H15,Active!$B$13:$CW$15,2,FALSE),FALSE)

So as i change the job name, the job number updates and the cells all update. Works great.

But we will need to update and change cell data as the project progress's and since the table is getting larger and larger, its becoming to easy to change the wrong cell data manually. So i would like to be able to either click on each cell to open a userform or dialog box to open where the new data can be entered and then on "Update" click, the data is pushed to the correct cell in the table.

Something like this:

No i know how the create the Userform get data from a cell to display, and to then push data from a textbox and into a specific cell. By I don't what to write code and build a user from for every cell which is roughly 80-90 data points on the Dashboard.

It seems like excel/VBA is 'smart' enough that we should be able to have 1 userform that is dynamic and the data displayed in it is based on what ever cell i selected or on a combo list to update/change.

I have no clue where to even begin with this. I am not an expert on VBA, but i know enought to search and find most of the code i use and be able to manipulate it for what i need. But this one is way out of my knowlege.

Any assistance is very much appreciated!

Thanks
BV

 

[brianv]

Solved:
I created a UserForm to pull cell data from each location:

Then coded the following in the UserForm:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Dashboard")

'Retrive Data from PM Worsheet if there is any
    Me.TextJobNumber.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("R9").Value)
    Me.TextJobName.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("F10").Value)
    Me.TextSoldValue.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("F18").Value)
    TextSoldValue = FormatCurrency(TextSoldValue / 1)
    Me.TextSoldDate.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("H18").Value)
    Me.TextCompletionDate.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("I18").Value)
    Me.TextSalesman.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("J18").Value)
    Me.TextActive.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("K18").Value)
    If Range("L18").Value <> "" Then
    Me.TextConSigned.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("L18").Value)
    Me.TextCoI.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("M18").Value)
    Me.TextConExecuted.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("N18").Value)
    Me.TextSSSP.Value = CStr(ThisWorkbook.Sheets("Dashboard").Range("O18").Value)
    Me.TextPWage.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("P18").Value)
    Me.TextIntent.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("Q18").Value)
    Me.TextLD.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("R18").Value)
    Me.TextSystem.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("F22").Value)
    Me.TextPanel.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("H22").Value)
    Me.TextInstall.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("J22").Value)
    Me.TextProject.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("L22").Value)
    Me.TextPM.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("M22").Value)
    Me.TextPriority.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("N22").Value)
    Me.TextDsgnHrs.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("O22").Value)
    Me.TextPMHrs.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("P22").Value)
    Me.TextInstHrs.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("Q22").Value)
    Me.TextTechHrs.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("R22").Value)
End If

End Sub

Private Sub CmdEditSubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Active")

' Find the last row number in column A.
    'With Sheets("Active")
    Sheets("Active").Select
    iRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    'End With

' Find the value you are looking for
    lookupvalue = Worksheets("Active").Range("M6").Value
 
' Select the range you are looking in
    lookuprange = Worksheets("Active").Range("A1:A" & iRow)
 
' Find the first matching value, and return the row number
    FirstMatchRowNumber = WorksheetFunction.Match(lookupvalue, lookuprange, 0)

' Go to the applicable row.
    Worksheets("Active").Range("A" & FirstMatchRowNumber).Select

'copy the data to the database
ws.Cells(iRow, 11).Value = Me.TextCompletionDate.Value
ws.Cells(iRow, 12).Value = Me.TextActive.Value
ws.Cells(iRow, 14).Value = Me.TextSalesman.Value
ws.Cells(iRow, 15).Value = Me.TextPM.Value
ws.Cells(iRow, 16).Value = Me.TextConSigned.Value
ws.Cells(iRow, 17).Value = Me.TextCoI.Value
ws.Cells(iRow, 18).Value = Me.TextConExecuted.Value
ws.Cells(iRow, 19).Value = Me.TextSSSP.Value
ws.Cells(iRow, 20).Value = Me.TextPWage.Value
ws.Cells(iRow, 21).Value = Me.TextIntent.Value
ws.Cells(iRow, 22).Value = Me.TextLD.Value
ws.Cells(iRow, 23).Value = Me.TextSystem.Value
ws.Cells(iRow, 24).Value = Me.TextPanel.Value
ws.Cells(iRow, 25).Value = Me.TextInstall.Value
ws.Cells(iRow, 26).Value = Me.TextProject.Value
ws.Cells(iRow, 27).Value = Me.TextPriority.Value
ws.Cells(iRow, 28).Value = Me.TextDsgnHrs.Value
ws.Cells(iRow, 29).Value = Me.TextPMHrs.Value
ws.Cells(iRow, 30).Value = Me.TextInstHrs.Value
ws.Cells(iRow, 31).Value = Me.TextTechHrs.Value

    Range("B10").Select

'close the Edit Entry Form
Unload Me
   
    Sheets("Dashboard").Select
    Range("I6").Select

End Sub

So now i load the userform, edit the cells that i want to edit, the upload the changes, (or actually all data), find the specific job number in Column A, select the cell, then paste all the data back to the appropriate locations right of column A.


My only issue is textbox formating. If the cell is blank, "" or O, then i want the textbox to also be blank. Sicne most cells are date formated (m/dd/yy) then the text box is assuming a date an displays 01/01/00 @12am.

So i tried this in teh code:

Me.TextActive.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("K18").Value)
    If Range("L18").Value <> 0 Then
    Me.TextConSigned.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("L18").Value)

Any everything after that point goes blank:

Then modified it to:

Me.TextActive.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("K18").Value)
    If Range("L18").Value <> "" Then
    Me.TextConSigned.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("L18").Value)

Any it changed nothing:

Any suggestion?

Thanks
BV