MrsInspiration
New Member
- Joined
- Jan 18, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- 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.
Again, Thank you in advance
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
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 | 0 | ddd | 0 | 0 | 0 | 0 | 0 | GT only | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | FY22 | 12:00:00 AM | $0.00 | $0.00 | 0 | ||||||
8 | 0005 | Test | A. Jones | 0 | 0 | ppp | 0 | 0 | 0 | 0 | 0 | GT only | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | FY22 | 12:00:00 AM | $0.00 | $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