VBA - copy text from Combobox in User from and paste in loop onto excel worksheet
I am new to excel VBA and I have tried to adapt some of the solutions found on Stack Overflow, but I'm still stuck!
I am trying to use the text of a selected option in a combobox in a userform to paste rows of this selected text into an excel worksheet.
The code below is meant to select the currently active userform's combobox value "CboIncomesPatch" and paste this value into cell "M8" of the activesheet and then loop this pasting until the counter clocks up to the numeric total number of units has been reached. The total number of units is a textbox value "TxtNumberOfUnits" for example "15". 15 of text in the combobox should be pasted in Cell "M8" then all of the subsequent rows "M9","M10" etc. The range that the ComboBox uses is hard-coded and all options are names of people that are all string (no numbers).
The error occurs at .SelectedItem on the below code. The loop works when I use it for Text Boxes excluding the selecting the combobox value part of the code.
Please let me know if I can provide more information here to assist your answer(s).
Sub Incomes_Patch() Dim Counter As Integer Dim ws As UserForm Dim Total As Integer Dim Incomes As String Set ws = UserForm(this.CboIncomesPatch.GetItemText(Me.CboIncomesPatch.SelectedItem)) Set Incomes.Value = ws Total = TxtNumberOfUnits.Value Counter = 0 Application.ActiveSheet.range("M8").Select Do While Counter <= Total If Counter = Total Then Exit Do ActiveCell.Value = Incomes + Counter ActiveCell.Offset(1, 0).Select Counter = Counter + 1 Loop End Sub
Please find below the correct code to do this:
Sub Incomes_Patch() Dim Counter As Integer 'Dim ws As UserForm Dim Total As Integer Dim Incomes As String 'I am not sure why you wanted the form object, you need the combobox value right? you can directly get it from Combobox. 'Set ws = UserForm(this.CboIncomesPatch.GetItemText(Me.CboIncomesPatch.SelectedItem)) Incomes = Me.CboIncomesPatch.Text ' selected item doesn't work in Combo, so you need to use Text Total = TxtNumberOfUnits.Text Counter = 0 Application.ActiveSheet.Range("M8").Select Do While Counter <= Total If Counter = Total Then Exit Do ActiveCell.Value = Incomes & Counter ActiveCell.Offset(1, 0).Select Counter = Counter + 1 Loop End Sub
Does it help?
Could you not use the .SelectedText property of the combo box?
Set ws = UserForm(Me.CboIncomesPatch.SelectedText) Set Incomes.Value = ws