MrsInspiration
New Member
- Joined
- Jan 18, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hello, anyone, I've been working on this for about 3 days.
I have a user form that I created that contains all of the information for a project record, except the unique ID (which is in column A). I cannot figure out how to get the information from the user form into the first empty row of the table. I've read the thread "Userform entering data in the last row instead of the first empty row from top" However, my table data doesn't start until A4. I know almost nothing about VBA and I've tried copying and reading different Macros and can't customize the one I need. Here is the attachment.
Here are the two VBA I'm trying to use:
Any help would be appreciated
I have a user form that I created that contains all of the information for a project record, except the unique ID (which is in column A). I cannot figure out how to get the information from the user form into the first empty row of the table. I've read the thread "Userform entering data in the last row instead of the first empty row from top" However, my table data doesn't start until A4. I know almost nothing about VBA and I've tried copying and reading different Macros and can't customize the one I need. Here is the attachment.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2 | A2 | =IF(F2<>"",CONCAT("000",1)," ") |
| A3 | A3 | =IF(F3<>"",CONCAT("000",$A2+1)," ") |
Here are the two VBA I'm trying to use:
VBA Code:
Private Sub Submit_Click()
Dim ProjectNo As String
Dim Initiator As String
Dim PRF As String
Dim AIMNo As String
Dim PROJECTNAME As String
Dim ProjectManager As String
Dim ProjectManagerID As String
Dim CPSM As String
Dim Design As String
Dim InteriorDesign As String
Dim Construction As String
Dim Catagory As String
Dim Priority As String
Dim Phase As String
Dim BldgNo As String
Dim BldgName As String
Dim Owner As String
Dim Stake1 As String
Dim Stake2 As String
Dim FundingSource As String
Dim BOR As String
Dim GTFI As String
Dim FYSTART As String
Dim FYCOMPLETION As String
Dim SCL As String
Dim TPB As String
Dim Comments As String
ProjectNo = ProjectNumText.Text
Initiator = InitiatorText.Text
PRF = PRFText.Text
AIMNo = AIMText.Text
PROJECTNAME = PROJECTNAMETEXT.Text
ProjectManager = ProjectManagerCombo.Text
CPSM = CPSMCombo.Text
Design = DesignCombo.Text
InteriorDesign = iDesignCombo.Text
Construction = ConstructCombo.Text
Catagory = CategoryCombo.Text
Priority = PriorityCombo.Text
Phase = PhaseCombo.Text
BldgNo = BldgNumCombo.Text
Owner = OwnerCombo.Text
Stake1 = Stake1Combo.Text
Stake2 = Stake2Combo.Text
FundingSource = FundingText.Text
BOR = BORCombo.Text
GTFI = GTFICombo.Text
FYSTART = StartText.Text
FYCOMPLETION = CompleteText.Text
SCL = SLCText.Text
TPB = TPBText.Text
Comments = CommentsText.Text
Dim ws As Worksheet
Dim tbl As ListObject
Dim r As Range
Set ws = Worksheets("Master Project Data Source").ActiveSheet
Set tbl = ws.ListObjects("MasterProjectTable")
'For r =
'Some kind of For Loop that I can't get working
'Dim newRow As ListRow
'Set newRow = tbl.ListRows.Add
'With newRow
' .Range(1).Value = Me.PROJECTNAMETEXT.Value
' .Range(2) = Initiator
' .Range(3) = PRF
' .Range(4) = AIMNo
' .Range(5) = PROJECTNAME
' .Range(6) = ProjectManager
' .Range(7) = CPSM
' .Range(8) = Design
' .Range(9) = InteriorDesign
' .Range(10) = Construction
' .Range(11) = Catagory
' .Range(12) = Priority
' .Range(13) = Phase
' .Range(14) = BldgNo
' .Range(15) = Owner
' .Range(16) = Stake1
' .Range(17) = Stake2
' .Range(18) = FundingSource
' .Range(19) = BOR
' .Range(20) = GTFI
' .Range(21) = FYSTART
' .Range(22) = FYCOMPLETION
' .Range(23) = SCL
' .Range(24) = TPB
' .Range(25) = Comments
End With
End Sub
