Could you check my code, userform to worksheet please

[ipbr21054]

Hi,
The code is shown below.
I have a userform with Comboboxes
ComboBox1 is MONTH
ComboBox2 is YEAR

Month is to be inserted into cell A3

Year is to be inserted into cell C3

When i press my transfer button the YEAR isnt shown on the worksheet but the MONTH is entered into cell A3

Please can you check / advise where i went wrong.

Thanks

Private Sub TransferButton_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 2
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT BOTH OPTIONS", 48, "MONTH & YEAR TRANSFER MESSAGE"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2)
    
    With ThisWorkbook.Worksheets("G INCOME")
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(3, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(3, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i


    End With
    
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    
    MsgBox "Month & Year Have Been Updated", vbInformation, "SUCCESSFUL MESSAGE INCOME MONTH & YEAR"
    Unload INCOMEMONTHYEAR
End Sub

 

[AlphaFrog]

I didn't follow the need to assign the comboboxes to an array and then loop through them. If there are only two, write each combobox value directly to cells A3 and C3.

    [color=darkblue]With[/color] ThisWorkbook.Worksheets("G INCOME")
        .Cells(3, "A") = Me.ComboBox1.Value         [color=green]'Month (as text)[/color]
        .Cells(3, "C") = Val(Me.ComboBox2.Value)    [color=green]'Year[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=green]'    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2)[/color]
[color=green]'[/color]
[color=green]'    With ThisWorkbook.Worksheets("G INCOME")[/color]
[color=green]'        For i = 0 To UBound(ControlsArr)[/color]
[color=green]'         Select Case i[/color]
[color=green]'            Case 1, 2, 4[/color]
[color=green]'               .Cells(3, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))[/color]
[color=green]'            Case Else[/color]
[color=green]'               .Cells(3, i + 1) = ControlsArr(i)[/color]
[color=green]'               ControlsArr(i).Text = ""[/color]
[color=green]'         End Select[/color]
[color=green]'    Next i[/color]
[color=green]'[/color]
[color=green]'[/color]
[color=green]'    End With[/color]

 

[ipbr21054]

Hi,

Many thanks that worked great.