Update row selects first row of table instead of correct row

MrsInspiration

New Member
Joined
Jan 18, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have two spreadsheets. Data is entered into The Master Spreadsheet with a Userform (which you all helped me with).
The second spreadsheet mirrors the Master Table so that users cannot delete anything. However they can push a button to update any record they need to.
The Update userform is prepopulated with the selected row's information and once the user changes what they need to, the form should update the Master table and then update the mirrored Entry Form spreadsheet.
I've finally gotten the Update userform to populate the master spreadsheet however it keeps overwriting the header row instead of looking in in the master file id column and updating the correct row.
Again, I've worked on this for 3 days until I'm about to miss my deadline tomorrow (no pressure). Could anyone assist me? I've been pretty good about understanding what I'm reading but this one has me stumped. I have atteched a dummy copy of the workbook with the userforms and code. My apologies but I had to change and remove some information for security reasons.


VBA Code:
Private Sub CommandButton1_Click()

End Sub

Private Sub Label52_Click()

End Sub
Private Sub UpdateRecord_Click()

Dim ws1              As Worksheet
Dim ws2             As Worksheet

Dim rNumber      As String
Dim tbl             As ListObject
Dim i             As Integer

    Set ws1 = Worksheets("Master Project Data Source")
    Set tbl = ws1.ListObjects("MasterProjectTable")
    Set ws2 = Worksheets("Entry Form")
   
projrow = tbl.ListRows.Count
rNumber = RecordNumber.Text



With projrow
For i = 4 To projrow
If tbl.Range(i, 1).Value = rNumber Then

End If
Next
End With

With projrow

       tbl.Range(2) = Me.ProjectNumText.Text
        tbl.Range(3) = Me.InitiatorText.Value
        tbl.Range(4) = Me.PRFText.Value
       tbl.Range(5) = Me.AIMText.Value
        tbl.Range(6) = Me.PROJECTNAMETEXT.Value
        tbl.Range(7) = Me.ProjectManagerCombo.Text
       tbl.Range(9) = Me.CPSMCombo.Text
        tbl.Range(10) = Me.DesignCombo.Text
        tbl.Range(11) = Me.iDesignCombo.Text
        tbl.Range(12) = Me.ConstructCombo.Text
        '.Range(13) =  AssignedResourceID < control name not shown??
        tbl.Range(14) = Me.CategoryCombo.Text
        tbl.Range(15) = Me.PriorityCombo.Text
        tbl.Range(16) = Me.PhaseCombo.Text
        tbl.Range(17) = Me.BldgNumCombo.Text
        tbl.Range(18) = Me.BldgNameText.Text
        tbl.Range(19) = Me.OwnerCombo.Text
        tbl.Range(20) = Me.Stake1Combo.Text
        tbl.Range(21) = Me.Stake2Combo.Text
        tbl.Range(22) = Me.FundingText.Text
        tbl.Range(23) = Me.BORCombo.Text
        tbl.Range(24) = Me.GTFICombo.Text
        tbl.Range(25) = Me.StartText.Text
        tbl.Range(26) = Me.CompleteText.Text
        tbl.Range(27) = Me.SLCText.Text
        tbl.Range(28) = Me.TPBText.Text
        tbl.Range(29) = Me.CommentsText.Text
  

MsgBox " Project Added"
End With
End Sub

Private Sub UserForm_Activate()
   
    Dim ActiveR As Long
        ActiveR = ActiveCell.Row
     
RecordNumber.Value = Cells(ActiveR, 2).Value
ProjectNumText.Value = Cells(ActiveR, 3).Value
InitiatorText.Value = Cells(ActiveR, 4).Value
PRFText.Value = Cells(ActiveR, 5).Value
AIMText.Value = Cells(ActiveR, 6).Value
PROJECTNAMETEXT.Value = Cells(ActiveR, 7).Value
ProjectManagerCombo.Value = Cells(ActiveR, 8).Value
CPSMCombo.Value = Cells(ActiveR, 10).Value
DesignCombo.Value = Cells(ActiveR, 11).Value
iDesignCombo.Value = Cells(ActiveR, 12).Value
ConstructCombo.Value = Cells(ActiveR, 13).Value
CategoryCombo.Value = Cells(ActiveR, 15).Value
PriorityCombo.Value = Cells(ActiveR, 16).Value
PhaseCombo.Value = Cells(ActiveR, 17).Value
BldgNumCombo.Value = Cells(ActiveR, 18).Value
OwnerCombo.Value = Cells(ActiveR, 20).Value
Stake1Combo.Value = Cells(ActiveR, 21).Value
Stake2Combo.Value = Cells(ActiveR, 22).Value
FundingText.Value = Cells(ActiveR, 23).Value
BORCombo.Value = Cells(ActiveR, 24).Value
GTFICombo.Value = Cells(ActiveR, 25).Value
StartText.Value = Cells(ActiveR, 26).Value
CompleteText.Value = Cells(ActiveR, 27).Value
SLCText.Value = Cells(ActiveR, 28).Value
TPBText.Value = Cells(ActiveR, 29).Value
CommentsText.Value = Cells(ActiveR, 30).Value

'Me.ProjectNumText.Value = ""
'Me.InitiatorText.Value = ""
'Me.PRFText.Value = ""
'Me.AIMText.Value = ""
'Me.PROJECTNAMETEXT.Value = ""
'Me.ProjectManagerCombo.Value = ""
'Me.CPSMCombo.Value = ""
'Me.DesignCombo.Value = ""
'Me.iDesignCombo.Value = ""
'Me.ConstructCombo.Value = ""
'Me.CategoryCombo.Value = ""
'Me.PriorityCombo.Value = ""
'Me.PhaseCombo.Value = ""
'Me.BldgNumCombo.Value = ""
'Me.OwnerCombo.Value = ""
'Me.Stake1Combo.Value = ""
'Me.Stake2Combo.Value = ""
'Me.FundingText.Value = ""
'Me.BORCombo.Value = ""
'Me.GTFICombo.Value = ""
'Me.StartText.Value = ""
'Me.CompleteText.Value = ""
'Me.TPBText.Value = ""
'Me.CommentsText.Value = ""
'ProjectNumText.SetFocus

End Sub

Private Sub Frame1_Click()

End Sub
Private Sub Cancel_Click()

Unload PROJECTENTRYFORM

End Sub

Private Sub ComboBox5_Change()

End Sub

Private Sub ComboBox6_Change()

End Sub



Private Sub CPSM_Change()

End Sub

Private Sub Label1_Click()

End Sub

Private Sub APPROVALS_Click()

End Sub

Private Sub INITIATOR_Click()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub Label33_Click()

End Sub

Private Sub Label34_Click()

End Sub

Private Sub Label35_Click()

End Sub

Private Sub Label39_Click()

End Sub

Private Sub Label41_Click()

End Sub

Private Sub ProjectManagerList_Click()

End Sub

Private Sub Label44_Click()

End Sub

Private Sub Label46_Click()

End Sub

Private Sub Label47_Click()

End Sub

Private Sub PRORITY_Click()

End Sub

Private Sub SubmitUpdate_Click()

    Dim ws          As Worksheet
    Dim tbl         As ListObject
    Dim rowUpdate   As ListRow
    

    Set ws = Worksheets("Master Project Data Source")
    Set tbl = ws.ListObjects("MasterProjectTable")

    
        .ListRows(ActiveR).Range = Me.ProjectNumText.Text
        '.Range(3) = Me.InitiatorText.Value
        '.Range(4) = Me.PRFText.Value
        '.Range(5) = Me.AIMText.Value
        '.Range(6) = Me.PROJECTNAMETEXT.Value
        '.Range(7) = Me.ProjectManagerCombo.Text
        '.Range(9) = Me.CPSMCombo.Text
        '.Range(10) = Me.DesignCombo.Text
        '.Range(11) = Me.iDesignCombo.Text
        '.Range(12) = Me.ConstructCombo.Text
        '.Range(13) =  AssignedResourceID < control name not shown??
       ' .Range(14) = Me.CategoryCombo.Text
        '.Range(15) = Me.PriorityCombo.Text
        '.Range(16) = Me.PhaseCombo.Text
       ' .Range(17) = Me.BldgNumCombo.Text
        '.Range(19) = Me.OwnerCombo.Text
        '.Range(20) = Me.Stake1Combo.Text
        '.Range(21) = Me.Stake2Combo.Text
        '.Range(22) = Me.FundingText.Text
       ' .Range(23) = Me.BORCombo.Text
       ' .Range(24) = Me.GTFICombo.Text
        '.Range(25) = Me.StartText.Text
        '.Range(26) = Me.CompleteText.Text
       ' .Range(27) = Me.SLCText.Text
       ' .Range(28) = Me.TPBText.Text
       ' .Range(29) = Me.CommentsText.Text
    
 Unload ProjectUpdateForm
 
 End With
 End Sub
PROJECT MANAGEMENT DATABASE DESIGN-CHRIS SANDERS-original w FORM Experiment new code.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1
2
3 Master_File_IDProject No.InitiatorPRFAIM NoProject NameProject ManagerProject Manager IDCPSMDesignInterior DesignConstructionAssigned Resource IDCatagoryPriorityPhaseBldg No.Bldg NameOwnerStakeholder(1)Stakeholder(2)Funding SourceBORGTFIFY StartFY CompletionSCLTPBCommentsAttachments
4 
5
6
70004TestA. JonesdddGT onlyFY22
80005TestA. JonespppGT onlyFY22
9
10
11
Entry Form
Cell Formulas
RangeFormula
B4:AE11B4=MasterProjectTable
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:AECell Value=0textNO


PROJECT MANAGEMENT DATABASE DESIGN-CHRIS SANDERS-original w FORM Experiment new code.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
10MASTER PROJECT TABLE
2Assigned ResourceApprovalsAnticipatedEstimates
3 Master_File_ID55Column2002Signage for new GT brand03Project Manager ID04050607Assigned Resource IDGT only0809010Column1011012013014015016FY2212:00:00 AM017018019Attachments
4 
5 
6 
70004TestA. Jones00ddd00000GT only000000000FY2212:00:00 AM$0.00$0.000
80005TestA. Jones00ppp00000GT only000000000FY2212:00:00 AM$0.00$0.000
9 
10 
11 
Master Project Data Source
Cell Formulas
RangeFormula
A4:A11A4=IF([@[Signage for new GT brand]]<>"",ROW()-ROW(MasterProjectTable[#Headers]),"")
Named Ranges
NameRefers ToCells
Calibri='Master Project Data Source'!$A$3:$AD$11A4:A11



Again, Thank you in advance

1643931784654.png
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
See if this helps at all:
At the moment I have hard coded the number 4 for the record ID, this needs to be replaced with the record ID from the userform.
I have not put in any error trapping for if the record ID does not exist.
I have assumed the record ID is a 4 digit text field with leading zeroes.

I have only make changes to the 1 module UpdateRecord_Click

VBA Code:
Private Sub UpdateRecord_Click()

    Dim ws1             As Worksheet
    Dim ws2             As Worksheet
    
    Dim rNumber         As String
    Dim tbl             As ListObject
    Dim i               As Integer
    
    Dim projrow         As Long
    Dim rngTblBody      As Range

    Set ws1 = Worksheets("Master Project Data Source")
    Set tbl = ws1.ListObjects("MasterProjectTable")
    Set ws2 = Worksheets("Entry Form")

    Set rngTblBody = tbl.DataBodyRange
    rNumber = Format(4, "0000")      ' <---- The 4 needs to be replaced with source of the record ID
    projrow = Application.Match(ID, rngTblBody.Columns(1), 1)

    With rngTblBody      
        .Cells(projrow, 2) = Me.ProjectNumText.Text
        .Cells(projrow, 3) = Me.InitiatorText.Value
        .Cells(projrow, 4) = Me.PRFText.Value
        .Cells(projrow, 5) = Me.AIMText.Value
        .Cells(projrow, 6) = Me.PROJECTNAMETEXT.Value
        .Cells(projrow, 7) = Me.ProjectManagerCombo.Text
        .Cells(projrow, 9) = Me.CPSMCombo.Text
        .Cells(projrow, 10) = Me.DesignCombo.Text
        .Cells(projrow, 11) = Me.iDesignCombo.Text
        .Cells(projrow, 12) = Me.ConstructCombo.Text
        '.Cells(projrow,13) =  AssignedResourceID < control name not shown??
        .Cells(projrow, 14) = Me.CategoryCombo.Text
        .Cells(projrow, 15) = Me.PriorityCombo.Text
        .Cells(projrow, 16) = Me.PhaseCombo.Text
        .Cells(projrow, 17) = Me.BldgNumCombo.Text
        .Cells(projrow, 18) = Me.BldgNameText.Text
        .Cells(projrow, 19) = Me.OwnerCombo.Text
        .Cells(projrow, 20) = Me.Stake1Combo.Text
        .Cells(projrow, 21) = Me.Stake2Combo.Text
        .Cells(projrow, 22) = Me.FundingText.Text
        .Cells(projrow, 23) = Me.BORCombo.Text
        .Cells(projrow, 24) = Me.GTFICombo.Text
        .Cells(projrow, 25) = Me.StartText.Text
        .Cells(projrow, 26) = Me.CompleteText.Text
        .Cells(projrow, 27) = Me.SLCText.Text
        .Cells(projrow, 28) = Me.TPBText.Text
        .Cells(projrow, 29) = Me.CommentsText.Text
    End With

    MsgBox " Project Added"

End Sub
 
Upvote 0
Thank you Alex for your quick turn around and assistance.
I'm running into a Type mismatch error.

VBA Code:
Set ws1 = Worksheets("Master Project Data Source")
    Set tbl = ws1.ListObjects("MasterProjectTable")
    Set ws2 = Worksheets("Entry Form")

    Set rngTblBody = tbl.DataBodyRange
    rNumber = Format(RecordNumber.Text, "0000")      ' <---- The 4 needs to be replaced with source of the record ID
    
    projrow = Application.Match(rNumber, rngTblBody.Columns(1), 0)

1643993003189.png


What I believe may be happening is that the Master_File_ID column COLUMN 1 is formatted as a Special Number format in the table to accomodate for the formatting of the leading zeros. Specifically Portuguese (Angola).
Debugging shows that the rNumber shows as 0003 but the projrow is = to zero.

What are your thoughts?
 
Upvote 0
Do you know how to use match or vlookup in Excel ?
See if you can figure out what the value needs to be for it to find it. eg does just putting 3 work ?
Or type in = 3 = the cell reference. Does it show true ?
 
Upvote 0
Since you are in a different time zone, to keep it moving, if you can determine that ID in the spreadsheet it is actually a number
change the 2 lines that are currently there to these.
VBA Code:
Dim rNumber as Long
rNumber = Clng(RecordNumber.Text")

One way of checking that they are numbers in the spreadsheet if to change the formatting to show 2 decimal places. Any values that visually change are numbers and any that don't are text.
 
Upvote 0
Solution
Since you are in a different time zone, to keep it moving, if you can determine that ID in the spreadsheet it is actually a number
change the 2 lines that are currently there to these.
VBA Code:
Dim rNumber as Long
rNumber = Clng(RecordNumber.Text")

One way of checking that they are numbers in the spreadsheet if to change the formatting to show 2 decimal places. Any values that visually change are numbers and any that don't are text.
Alex Thank you so very much. I was able to make it work with this second solution. Everything that you sent me, I'm following up to make sure that I understand how to use it the next time. :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top