vba to find a match cell on separate file to upload data on that same row

[brianv]

Ive always appreciated the assistance of you all, and i need a little more assistance:

In the past only 1 person in our office is responsible for uploading new jobs or correcting/updating existing job milestones in our PM job log. However, we've gotten to the point, where this is too much for one person to manage and we want each PM to correct/update their project milestones.

We created a "Dashboard" file to gather data from a massive table and display it. The dashboard displays various project management milestones dates & costs. The data within the cells of the Dashboard are populated based on the Job Number entered in G4. All the other cells displaying the project data use an index formula to lookup the data from its respective row from the table. The table is quite large A12:FT500 (yes FT) with Column B being the Job Number. (technically the table data is not formatted as a 'Table' just so you know, no reason why)

The Dashboard is new and separate file vs the data file (PM Dashboard.xls & PM Flow Log Ver3.xlsm).

So now that we are viewing the data, we may need to update/correct that data, so I've created button on the Dashboard that opens a UserForm, and that Userform populates its text boxes based on the data within the Dashboard, the PM can then edit those fields (textboxes) that need correcting and then using a "Submit" command button, upload that corrected data back into the PM Flow Log.

The intent is for the VBA to open the PM Flow Log, find the row in Column B that matches the cell in G4 of the Dashboard or "Textbox1" of the userform and then upload the corrected data into their respective cells and close its again. So the PM Flow Log is never held open.

In addition, Im trying to figure out how to properly format % in certain textboxes. I been able to figure out how to display $ values, but a % is stumping me slightly.

So I could really use some help on 2 things:
1- Write those lines for code to "find" that matching row for use to upload the data.
2- How do I format a textbox to display as a %. I could get it to work with whole numbers like 5.0% but not fraction percentages like 5.5%


This is what i have if i were to search for the last row and upload the data.. but how do i modify to find the matching row.

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

ChDir _
       "M:\Engineering\Project Logs\PM Flow Log Ver3.xlsm" _
        ).RunAutoMacros Which:=xlAutoOpen

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'upload the data to the table
ws.Cells(iRow, 1).Value = "Yes"
ws.Cells(iRow, 2).Value = Me.TextJobNumber.Value
ws.Cells(iRow, 3).Value = Me.TextSiteID.Value
ws.Cells(iRow, 4).Value = Me.TextApproved.Value
' etc.... more upload data values

Sheets("Active").Select
Range("A65535").End(xlUp).Offset(1, 0).Select

'Copy 1st Blank Row after Data and Paste/Insert
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

Unload me
End Sub

Regarding Q2: this is what i have for the % format? but this will display 5% but not 5.5%

Private Sub TextRetention_Change()
        TextRetention.Value = Format(Val(TextRetention.Value) / 100, "#0.00%")
        TextRetention.SelStart = 1
    
End Sub

Is it possible to modify & apply this to more than 1 specific textbox?

Thanks in advance for your assistance...

Brian