CODEWITHSUNDEEP
vbaexcelcomboboxuserform
nabilah
nabilah

Reputation: 211

Setting condition for the combo box in a user form

Hi currently i have a user form that look like this for adding of new projects: enter image description here

In the combo box, the user may select "Big Project", "Medium Project" and "Small Project" sizes. Each project sizes consist of a specific range in the sheet shown below: enter image description here

The user would add in the project details into the userform and select the combobox drop down list for the project sizes and the new project details will be added into the empty rows found under each specific project sizes. Unfortunately i am not sure how to do it do in term of ranging each project sizes in the combo box drop down list so that when the user already selected the project sizes and click on the add command button, the new project details will be added to the new row found on the selected project sizes. in Addition, i have some problem with the command add button that says 'Application-defined or object-defined error' on the codes shown below and not sure if the codes will help to add into the lastrow found on the specific project sizes. I am pretty lost in what to do for both the combobox and command add button.

Private Sub CommandAddButton1_Click()

lastrow = Sheets("Program status summary").Range("B").End(xlDown).Row 'shows the above mention error'

    Cells(lastrow + 1, "B").Value = TextBoxProjCode.Text
    Cells(lastrow + 1, "C").Value = TextBoxProjName.Text
    Cells(lastrow + 1, "D").Value = TextBoxSector.Text
    Cells(lastrow + 1, "E").Value = TextBoxObjective.Text
    Cells(lastrow + 1, "H").Value = TextBoxProjSponsor.Text
    Cells(lastrow + 1, "G").Value = TextBoxProjSponsorNew.Text
    Cells(lastrow + 1, "F").Value = TextBoxProjM.Text
    Cells(lastrow + 1, "T").Value = TextBoxRegulatory.Text
    Cells(lastrow + 1, "N").Value = TextBoxRiskLvl.Text
    Cells(lastrow + 1, "M").Value = TextBoxDatePar.Text
    Cells(lastrow + 1, "J").Value = TextBoxCostPar.Text
    Cells(lastrow + 1, "O").Value = TextBoxAffectCust.Text
    Cells(lastrow + 1, "Q").Value = TextBoxCustNonRetail.Text
    Cells(lastrow + 1, "P").Value = TextBoxCustRetail.Text
    Cells(lastrow + 1, "S").Value = TextBoxOutsourcingImp.Text
    Cells(lastrow + 1, "R").Value = TextBoxKeyStatus.Text
    Cells(lastrow + 1, "K").Value = TextBoxSchStart.Text
    Cells(lastrow + 1, "L").Value = TextBoxSchEnd.Text
    Cells(lastrow + 1, "V").Value = TextBoxRagSchedule.Text
    Cells(lastrow + 1, "U").Value = TextBoxRagFinancial.Text
    Cells(lastrow + 1, "W").Value = TextBoxRagBenefit.Text
    Cells(lastrow + 1, "I").Value = TextBoxCost.Text

Unload AddProject

End Sub

I really hope anyone could assist me with the combo box of the project sizes in inserting the range for each project sizes and the error on the add command button. Any help would be appreciated. Thank you.

Upvotes: 1

Views: 1133

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Private Sub CommandAddButton1_Click()
  Dim sh As Worksheet: Set sh = ThisWorkbook.Sheets("Program status summary")
  Dim emptyRow As Integer: emptyRow = 1 + sh.UsedRange.Find(ComboBoxProjSizes.Text).End(xlDown).row

  With sh
    .Cells(emptyRow, "A").Value = 1 + Application.Max(.Columns(1)) ' to generate a new identifier in column 1

    .Cells(emptyRow, "B").Value = TextBoxProjCode.Text
    .Cells(emptyRow, "C").Value = TextBoxProjName.Text
    .Cells(emptyRow, "D").Value = TextBoxSector.Text
    .Cells(emptyRow, "E").Value = TextBoxObjective.Text
    .Cells(emptyRow, "H").Value = TextBoxProjSponsor.Text
    .Cells(emptyRow, "G").Value = TextBoxProjSponsorNew.Text
    .Cells(emptyRow, "F").Value = TextBoxProjM.Text
    .Cells(emptyRow, "T").Value = TextBoxRegulatory.Text
    .Cells(emptyRow, "N").Value = TextBoxRiskLvl.Text
    .Cells(emptyRow, "M").Value = TextBoxDatePar.Text
    .Cells(emptyRow, "J").Value = TextBoxCostPar.Text
    .Cells(emptyRow, "O").Value = TextBoxAffectCust.Text
    .Cells(emptyRow, "Q").Value = TextBoxCustNonRetail.Text
    .Cells(emptyRow, "P").Value = TextBoxCustRetail.Text
    .Cells(emptyRow, "S").Value = TextBoxOutsourcingImp.Text
    .Cells(emptyRow, "R").Value = TextBoxKeyStatus.Text
    .Cells(emptyRow, "K").Value = TextBoxSchStart.Text
    .Cells(emptyRow, "L").Value = TextBoxSchEnd.Text
    .Cells(emptyRow, "V").Value = TextBoxRagSchedule.Text
    .Cells(emptyRow, "U").Value = TextBoxRagFinancial.Text
    .Cells(emptyRow, "W").Value = TextBoxRagBenefit.Text
    .Cells(emptyRow, "I").Value = TextBoxCost.Text
    .Rows(emptyRow+1).Insert
  End With
  Unload AddProject
End Sub

Upvotes: 1

Related Questions