Excel VBA Userform Coding Issues

I've followed a tutorial on how to create a userform in Excel with 15 fiedls. However, I haven't been able to add two additional new fields without corrupting the code. The two new fields ('Project No.' and 'Customer') should be listed between the 'Status' and 'Project Name' Column. The 'Project No' should create numbers in an index or sequence for each project based on entry. For instance, my first project that I entered should be number 1000, then the next one should be 1001, and so on. The numbers should not repeat themselves. 
[Excel userform images.pdf](https://github.com/sarahthomas87/Project-Userform-Excel/files/14587614/Excel.userform.images.pdf)

[Excel userform images.pdf](https://github.com/sarahthomas87/Project-Userform-Excel/files/14587626/Excel.userform.images.pdf)

Here is the code that needs updating to have a total of 17 fields:
__________________________________________________________________________________________________________________________________

Private Sub cmdDelete_Click()
    Dim X As Long
        Dim Y As Long
        X = Sheets("WIP").Range("A" & Rows.Count).End(xlUp).Row
        
        If MsgBox("Are you sure you want to DELETE this project? ", vbYesNo + vbQuestion, "Question") = vbNo Then
        Exit Sub
        End If
        
        For Y = 7 To X
        If Sheets("WIP").Cells(Y, 2).Value = cmbSearch.Text Then
        Rows(Y).Delete
        
        End If
        Next Y
        
         '''''''''''Clear & Reset Boxes''''''''''''
        Me.cmbSearch.Value = ""
        Me.cmbStatus.Value = ""
        Me.txtProjectName.Value = ""
        Me.cmbEstimator.Value = ""
        Me.txtAddress.Value = ""
        Me.txtCity.Value = ""
        Me.cmbState.Value = ""
        Me.txtZip.Value = ""
        Me.cmbVACounty.Value = " "
        Me.cmbBonded.Value = ""
        Me.cmbWageScale.Value = ""
        Me.txtOriginalContract.Value = ""
        Me.txtEstimatedGross.Value = ""
        Me.txtBillings.Value = ""
        Me.txtIncurred.Value = ""
        Me.txtCostToComplete.Value = ""
        
    
        MsgBox "Project has been deleted.", vbInformation
        cmbStatus.SetFocus

End Sub

Private Sub cmdExit_Click()
        Unload Me
End Sub

Private Sub cmdReset_Click()
        Unload Me
        UserForm1.Show
End Sub

Private Sub cmdSave_Click()
        Dim sh As Worksheet
        Set sh = ThisWorkbook.Sheets("WIP")
        Dim le As Long
        LR = sh.Range("A" & Rows.Count).End(xlUp).Row
         
        ''''''''''Validation'''''''''''''
        If Me.cmbStatus <> "Open" And Me.cmbStatus <> "Completed" Then
        MsgBox "Select Project status from drop down.", vbCritical
        Exit Sub
        End If
        
        If Me.cmbEstimator = "" Then
        MsgBox "Please select an Estimator.", vbCritical
        Exit Sub
        End If
        
       If Me.cmbWageScale <> "No" And Me.cmbWageScale <> "Yes" Then
        MsgBox "Please indicate Wage Scale status.", vbCritical
        Exit Sub
        End If
       
       If Me.cmbState <> "MD" And Me.cmbState <> "VA" And Me.cmbState <> "DC" Then
        MsgBox "Please select a state.", vbCritical
        Exit Sub
        End If
        
        If Application.WorksheetFunction.CountIf(sh.Range("B:B"), Me.txtProjectName.Text) > 0 Then
            MsgBox "Name already exists! Select UPDATE option for current projects.", vbOKOnly + vbInformation, "Error"
            Exit Sub
        End If
        
       '''''''''''Add data om Excel Sheet''''''''''
       If MsgBox("Are you sure you want to save a NEW project? ", vbYesNo + vbQuestion, "Question") = vbNo Then
        Exit Sub
        End If
        
        With sh
            .Cells(LR + 1, 1).Value = Me.cmbStatus.Value
            .Cells(LR + 1, "B").Value = Me.txtProjectName.Value
            .Cells(LR + 1, "C").Value = Me.cmbEstimator.Value
            .Cells(LR + 1, "D").Value = Me.txtAddress.Value
            .Cells(LR + 1, "E").Value = Me.txtCity.Value
            .Cells(LR + 1, "F").Value = Me.cmbState.Value
            .Cells(LR + 1, "G").Value = Me.txtZip.Value
            .Cells(LR + 1, "H").Value = Me.cmbVACounty.Value
            .Cells(LR + 1, "I").Value = Me.cmbBonded.Value
            .Cells(LR + 1, "J").Value = Me.cmbWageScale.Value
            .Cells(LR + 1, "K").Value = Me.txtOriginalContract.Value
            .Cells(LR + 1, "L").Value = Me.txtEstimatedGross.Value
            .Cells(LR + 1, "M").Value = Me.txtBillings.Value
            .Cells(LR + 1, "M").Value = Me.txtIncurred.Value
            .Cells(LR + 1, "O").Value = Me.txtCostToComplete.Value
            .Cells(LR + 1, "P").Value = Application.UserName & "-" & Format(Now(), "MM/DD/YYYY, HH:MM AM/PM")

        End With
        
         '''''''''''Clear & Reset Boxes''''''''''''
            Me.cmbStatus.Value = ""
            Me.txtProjectName.Value = ""
            Me.cmbEstimator.Value = ""
            Me.txtAddress.Value = ""
            Me.txtCity.Value = ""
            Me.cmbState.Value = ""
            Me.txtZip.Value = ""
            Me.cmbVACounty.Value = " "
            Me.cmbBonded.Value = ""
            Me.cmbWageScale.Value = ""
            Me.txtOriginalContract.Value = ""
            Me.txtEstimatedGross.Value = ""
            Me.txtBillings.Value = ""
            Me.txtIncurred.Value = ""
            Me.txtCostToComplete.Value = ""
        
            Call Refresh_data
            
            MsgBox "Your NEW project has been successfully added.", vbInformation
            cmbStatus.SetFocus
            
         
            LR = Range("B" & Rows.Count).End(xlUp).Row
            Application.EnableEvents = False
            Range("B7:BB" & LR).Sort Key1:=Range("B7"), Order1:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            Application.EnableEvents = True
            

        
        End Sub



Sub Refresh_data()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("WIP")
    Dim le As Long
    LR = Sheets("WIP").Range("A" & Rows.Count).End(xlUp).Row
            
    If LR = 6 Then LR = 7
    With Me.ListBox1
            .ColumnCount = 15
            .ColumnHeads = True
            .ColumnWidths = "35,140,40,90,65,20,35,95,30,25,50,50,50,50,50"
            .RowSource = "WIP!A7:O" & LR
    End With
    
End Sub


Private Sub cmdSearch_Click()
    Dim X As Long
    Dim Y As Long
    X = Sheets("WIP").Range("A" & Rows.Count).End(xlUp).Row
    
    For Y = 7 To X
    If Sheets("WIP").Cells(Y, 2).Value = cmbSearch.Text Then
    cmbStatus = Sheets("WIP").Cells(Y, 1).Value
    txtProjectName = Sheets("WIP").Cells(Y, 2).Value
    cmbEstimator = Sheets("WIP").Cells(Y, 3).Value
    txtAddress = Sheets("WIP").Cells(Y, 4).Value
    txtCity = Sheets("WIP").Cells(Y, 5).Value
    cmbState = Sheets("WIP").Cells(Y, 6).Value
    txtZip = Sheets("WIP").Cells(Y, 7).Value
    cmbVACounty = Sheets("WIP").Cells(Y, 8).Value
    cmbBonded = Sheets("WIP").Cells(Y, 9).Value
    cmbWageScale = Sheets("WIP").Cells(Y, 10).Value
    txtOriginalContract = Sheets("WIP").Cells(Y, 11).Value
    txtEstimatedGross = Sheets("WIP").Cells(Y, 12).Value
    txtBillings = Sheets("WIP").Cells(Y, 13).Value
    txtIncurred = Sheets("WIP").Cells(Y, 14).Value
    txtCostToComplete = Sheets("WIP").Cells(Y, 15).Value
    
    
    End If
    Next Y

End Sub

Private Sub cmdUpdate_Click()
        Dim X As Long
        Dim Y As Long
        X = Sheets("WIP").Range("A" & Rows.Count).End(xlUp).Row
    
        
        ''''''''''Validation'''''''''''''
        If Me.cmbStatus <> "Open" And Me.cmbStatus <> "Completed" Then
        MsgBox "Select Project status from drop down.", vbCritical
        Exit Sub
        End If
        
        If Me.cmbEstimator = "" Then
        MsgBox "Please select an Estimator.", vbCritical
        Exit Sub
        End If
        
        If Me.cmbWageScale <> "No" And Me.cmbWageScale <> "Yes" Then
        MsgBox "Please indicate Wage Scale status.", vbCritical
        Exit Sub
        End If
        
        If Me.cmbState <> "MD" And Me.cmbState <> "VA" And Me.cmbState <> "DC" Then
        MsgBox "Please select a state.", vbCritical
        Exit Sub
        End If
        
        
        '''''''''''''Add Update Data to excel'''''''''''
        
        If MsgBox("Are you sure you want to UPDATE an existing project? ", vbYesNo + vbQuestion, "Question") = vbNo Then
        Exit Sub
        End If
        
    
        For Y = 7 To X
        If Sheets("WIP").Cells(Y, 2).Value = cmbSearch.Text Then
        Sheets("WIP").Cells(Y, 2).Value = txtProjectName
        Sheets("WIP").Cells(Y, 1).Value = cmbStatus
        Sheets("WIP").Cells(Y, 3).Value = cmbEstimator
        Sheets("WIP").Cells(Y, 4).Value = txtAddress
        Sheets("WIP").Cells(Y, 5).Value = txtCity
        Sheets("WIP").Cells(Y, 6).Value = cmbState
        Sheets("WIP").Cells(Y, 7).Value = txtZip
        Sheets("WIP").Cells(Y, 8).Value = cmbVACounty
        Sheets("WIP").Cells(Y, 9).Value = cmbBonded
        Sheets("WIP").Cells(Y, 10).Value = cmbWageScale
        Sheets("WIP").Cells(Y, 11).Value = txtOriginalContract
        Sheets("WIP").Cells(Y, 12).Value = txtEstimatedGross
        Sheets("WIP").Cells(Y, 13).Value = txtBillings
        Sheets("WIP").Cells(Y, 14).Value = txtIncurred
        Sheets("WIP").Cells(Y, 15).Value = txtCostToComplete
    
        End If
        Next Y
        
        '''''''''''Clear & Reset Boxes''''''''''''
        Me.cmbSearch.Value = ""
        Me.cmbStatus.Value = ""
        Me.txtProjectName.Value = ""
        Me.cmbEstimator.Value = ""
        Me.txtAddress.Value = ""
        Me.txtCity.Value = ""
        Me.cmbState.Value = ""
        Me.txtZip.Value = ""
        Me.cmbVACounty.Value = " "
        Me.cmbBonded.Value = ""
        Me.cmbWageScale.Value = ""
        Me.txtOriginalContract.Value = ""
        Me.txtEstimatedGross.Value = ""
        Me.txtBillings.Value = ""
        Me.txtIncurred.Value = ""
        Me.txtCostToComplete.Value = ""
        
        
        MsgBox "Project has been successfully updated.", vbInformation
        cmbStatus.SetFocus
        
         LR = Range("B" & Rows.Count).End(xlUp).Row
            Application.EnableEvents = False
            Range("B7:BB" & LR).Sort Key1:=Range("B7"), Order1:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            Application.EnableEvents = True

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        cmbSearch = ListBox1.Column(1)
        If cmbSearch.Text = ListBox1.Column(1) Then
        cmbStatus.Text = ListBox1.Column(0)
        txtProjectName.Text = ListBox1.Column(1)
        cmbEstimator.Text = ListBox1.Column(2)
        txtAddress.Text = ListBox1.Column(3)
        txtCity.Text = ListBox1.Column(4)
        cmbState.Text = ListBox1.Column(5)
        txtZip.Text = ListBox1.Column(6)
        cmbVACounty.Text = ListBox1.Column(7)
        cmbBonded.Text = ListBox1.Column(8)
        cmbWageScale.Text = ListBox1.Column(9)
        txtOriginalContract.Text = ListBox1.Column(10)
        txtEstimatedGross.Text = ListBox1.Column(11)
        txtBillings.Text = ListBox1.Column(12)
        txtIncurred.Text = ListBox1.Column(13)
        txtCostToComplete.Text = ListBox1.Column(14)

        End If
        
        
End Sub


Private Sub txtBillings_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        Me.txtBillings = Format(Me.txtBillings, "$#,##0.00")
End Sub

Private Sub txtCostToComplete_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        Me.txtCostToComplete = Format(Me.txtCostToComplete, "$#,##0.00")
End Sub

Private Sub txtEstimatedGross_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        Me.txtEstimatedGross = Format(Me.txtEstimatedGross, "$#,##0.00")
End Sub


Private Sub txtIncurred_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        Me.txtIncurred = Format(Me.txtIncurred, "$#,##0.00")
End Sub

Private Sub txtOriginalContract_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        Me.txtOriginalContract = Format(Me.txtOriginalContract, "$#,##0.00")
End Sub

Private Sub UserForm_Activate()
        cmbState.List = Array("MD", "VA", "DC")
        cmbBonded.List = Array("Yes", "No")
        cmbWageScale.List = Array("Yes", "No")
        cmbStatus.List = Array("Open", "Completed")
        cmbEstimator.List = Array("Kata", "Kayla", "Kerri", "Sawsan", "Tony", "Christian")
        Call Refresh_data
        
        Me.Left = Application.Left + (Application.Width - Me.Width) / 2
        
    
End Sub




Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Excel VBA Userform Coding Issues #1

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Excel VBA Userform Coding Issues #1

Description

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions