CODEWITHSUNDEEP
excelvbacombobox
nabilah
nabilah

Reputation: 211

Adding the combo box drop down list value to the new rows

Hi i know it might sound easy but since i am new to VBA thus i need some help in adjusting the user form vba codes below for the combo box drop down list value to be inserted in the new rows. Currently i have an add command button that will add all the input on the userform to the new rows found. The current problem i am facing now is the adjustment i need to made for the comboBox line ".Text" in the add command button for it to run smoothly. Below the the code that i have and i have commented on the line where i am facing a problem right now.

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 = ComboBoxRagSchedule.Text 'Not Sure what to change the word ".Text" to so that the value in the comboBox would appear in the cell'
    .Cells(emptyRow, "U").Value = ComboBoxRagFinancial.Text'Not sure what to change the word ".Text" to so that the value in the comboBox would appear in the cell'
    .Cells(emptyRow, "W").Value = ComboBoxRagBenefit.Text'Not sure what to change the word ".Text" to so that the value in the comboBox would appear in the cells )
    .Cells(emptyRow, "I").Value = TextBoxCost.Text
    .Rows(emptyRow + 1).Insert

  End With

  Unload AddProject

End Sub

Any help is much appreciated. Thank you :)

Upvotes: 0

Views: 1518

Answers (1)

Krzysztof Witczak
Krzysztof Witczak

Reputation: 512

Instead of .Text, try .Value, should insert current selected value to the cell, after running the macro. You can read some more about this here.

Upvotes: 1

Related Questions