Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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
share|improve this question

2 Answers

up vote 0 down vote accepted

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

share|improve this answer
Yes V that works. Thank you very much. – Neil May 2 at 10:42
would you please accept it as answer then? – Vikas May 2 at 15:26
Sorry - just accepted it. Did not realise I needed to do that. Thank you, Neil – Neil May 3 at 9:03
up vote 0 down vote

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

Set ws = UserForm(Me.CboIncomesPatch.SelectedText)
Set Incomes.Value = ws
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.