Could you check my code, userform to worksheet please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,136
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.

Code:
    [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]
 

Forum statistics

Threads
1,078,309
Messages
5,339,410
Members
399,302
Latest member
Swiftymj

Some videos you may like

This Week's Hot Topics

Top