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).

Thank you,

Neil

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

Answers


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?

Thanks,V


Could you not use the .SelectedText property of the combo box?

Set ws = UserForm(Me.CboIncomesPatch.SelectedText)
Set Incomes.Value = ws

Need Your Help

How to Display Best Sellers dynamic specific category?

magento magento-1.4 magento-1.5

how to display Best sellers dynmaic and specific category in magento 1.6.2.0 ?

jsoup html parser selecting wrong table

mysql jsoup

i need to parse http://developer.android.com/about/dashboards/index.html to get the data from the first table("platform versions") into my mysql db.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.