Update row selects first row of table instead of correct row

MrsInspiration · Feb 3, 2022

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
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE
1
2
3 Master_File_ID Project No. Initiator PRF AIM No Project Name Project Manager Project Manager ID CPSM Design Interior Design Construction Assigned Resource ID Catagory Priority Phase Bldg No. Bldg Name Owner Stakeholder(1) Stakeholder(2) Funding Source BOR GTFI FY Start FY Completion SCL TPB Comments Attachments
4
5
6
7 0004 Test A. Jones ddd GT only FY22
8 0005 Test A. Jones ppp GT only FY22
9
10
11
Entry Form
Cell Formulas
Range Formula
B4:AE11 B4 =MasterProjectTable
Dynamic array formulas.
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
B:AE Cell Value =0 text NO

PROJECT MANAGEMENT DATABASE DESIGN-CHRIS SANDERS-original w FORM Experiment new code.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

0

MASTER PROJECT TABLE

2

Assigned Resource

Approvals

Anticipated

Estimates

3

Master_File_ID

55

Column2

0

02

Signage for new GT brand

03

Project Manager ID

04

05

06

07

Assigned Resource ID

GT only

08

09

010

Column1

011

012

013

014

015

016

FY22

12:00:00 AM

017

018

019

Attachments

4

5

6

7

0004

Test

A. Jones

0

ddd

0

GT only

0

FY22

12:00:00 AM

$0.00

0

8

0005

Test

A. Jones

0

ppp

0

GT only

0

FY22

12:00:00 AM

$0.00

0

9

10

11

Master Project Data Source

Cell Formulas
Range	Formula
A4:A11	A4	=IF([@[Signage for new GT brand]]<>"",ROW()-ROW(MasterProjectTable[#Headers]),"")

Named Ranges
Name	Refers To	Cells
Calibri	='Master Project Data Source'!$A$3:$AD$11	A4:A11

Again, Thank you in advance

Alex Blakenburg · Feb 4, 2022

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

MrsInspiration · Feb 4, 2022

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)

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?

Alex Blakenburg · Feb 4, 2022

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 ?

Alex Blakenburg · Feb 4, 2022

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.

MrsInspiration · Feb 5, 2022

Alex Blakenburg said:
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.

Alex Blakenburg · Feb 5, 2022

Thanks for letting me know. Glad I could help

Update row selects first row of table instead of correct row

MrsInspiration

New Member

Excel Facts

Alex Blakenburg

MrExcel MVP

MrsInspiration

New Member

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

MrsInspiration

New Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Cell Formulas
Range	Formula
B4:AE11	B4	=MasterProjectTable
Dynamic array formulas.

Update row selects first row of table instead of correct row

MrsInspiration

New Member

Excel Facts

Alex Blakenburg

MrExcel MVP

MrsInspiration

New Member

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

MrsInspiration

New Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock