ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,731
- Office Version
- 2007
- Platform
- Windows
Morning,
The code im using is shown below.
I have used a code from another userform where it had two comboboxes & now wish to use it on a worksheet where there is only one combobox.
Some info for you.
Worksheet called SUMMARY SHEET
Year for combobox is on worksheet called LIST which is in the same workbook.
I open the workbook then code checks cell A2 of which at present is empty so my userform is shown.
I click on the one drop down aroow on the combobox & make a year selection.
This is where i am now stuck.
After making the year selection i then press the commandbutton & it should then enter my selected year in cell A2
I actually see a RTE Could Not Find The Specified Object.
This is where i need some help plea, check / edit the code so the selected year is entered in the cell A2
My userfomr consists of Combobox with year range.
A Transfer Button to send that year range to the worksheet cell A2
A close userform button
Many thanks if you could assist
The code im using is shown below.
I have used a code from another userform where it had two comboboxes & now wish to use it on a worksheet where there is only one combobox.
Some info for you.
Worksheet called SUMMARY SHEET
Year for combobox is on worksheet called LIST which is in the same workbook.
I open the workbook then code checks cell A2 of which at present is empty so my userform is shown.
I click on the one drop down aroow on the combobox & make a year selection.
This is where i am now stuck.
After making the year selection i then press the commandbutton & it should then enter my selected year in cell A2
I actually see a RTE Could Not Find The Specified Object.
This is where i need some help plea, check / edit the code so the selected year is entered in the cell A2
My userfomr consists of Combobox with year range.
A Transfer Button to send that year range to the worksheet cell A2
A close userform button
Many thanks if you could assist
Code:
Private Sub TransferButton_Click()
Dim i As Integer
Dim ControlsArr As Variant, ctrl As Variant
Dim x As Long
For i = 1 To 1
With Me.Controls("ComboBox" & i)
If .ListIndex = -1 Then
MsgBox "MUST SELECT A YEAR", 48, "SUMMARY SHEET YEAR MESSAGE"
.SetFocus
Exit Sub
End If
End With
Next i
ControlsArr = Array(Me.ComboBox1)
With ThisWorkbook.Worksheets("SUMMARY SHEET")
For i = 0 To UBound(ControlsArr)
Select Case i
Case 1, 2, 4
.Cells(1, i + 2) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
Case Else
.Cells(1, i + 2) = ControlsArr(i)
ControlsArr(i).Text = ""
End Select
Next i
End With
ActiveWorkbook.Save
Application.ScreenUpdating = True
MsgBox "YEAR HAS BEEN INSERTED ON WORKSHEET", vbInformation, "SUCCESSFUL MESSAGE SUMMARY SHEET"
Unload SUMMARYSHEETYEAR
End Sub