Code runs fine from userform but no values entered on the worksheet

[ipbr21054]

I am using the code below.
I run the command button to send userform values to worksheet BUT no values get intered on the worksheet at all.
I also get no RTE
Do you maybe see why ?

Thanks

    Private Sub CommandButton1_Click()
    Dim i           As Integer
    Dim lastRow     As Long
    Dim wsGIncome   As Worksheet
    Dim arr(1 To 5) As Variant
    Dim Prompt      As String
  
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
  
    If ComboBox1.Value = "" Or TextBox2.Value = "" Or _
    TextBox3.Value = "" Or TextBox4.Value = "" Or TextBox5.Value = "" Then
    MsgBox "YOU MUST COMPLETE ALL THE FIELDS", vbCritical, "USERFORM FIELDS EMPTY MESSAGE"
    
    Exit Sub
    End If
  
         Application.ScreenUpdating = False

        With wsGIncome
            lastRow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
          
            With .Cells(lastRow, 14).Resize(, UBound(arr))
                .Value = arr
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Borders.Weight = xlThin
                .Interior.ColorIndex = 6
                
                .Cells(1, 1).HorizontalAlignment = xlLeft
            End With
         
            .Range("N4").Select
          
        End With

       Unload Me
        Application.ScreenUpdating = True
                  
         ActiveSheet.Sort.SortFields.Clear
         ActiveSheet.Sort.SortFields.Add Key:=Range("N1"), _
         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("N4:S38")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
    End With

    End Sub

 

[dmt32]

You have declared & sized an array to post to your range but do not appear to be populating it in your code

untested but see if this update resolves for you

    Private Sub CommandButton1_Click()
    Dim lastRow        As Long, i As Long
    Dim wsGIncome      As Worksheet
    Dim arr(1 To 5)    As Variant
    Dim Prompt         As String
   
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
   
    For i = 1 To UBound(arr)
        arr(i) = Choose(i, ComboBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
       
        If Len(arr(i)) = 0 Then
            MsgBox "YOU MUST COMPLETE ALL THE FIELDS", vbCritical, "USERFORM FIELDS Empty MESSAGE"
            Exit Sub
        End If
    Next i
   
    Application.ScreenUpdating = False
   
    With wsGIncome
        lastRow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
       
        With .Cells(lastRow, 14).Resize(, UBound(arr))
            .Value = arr
            .Font.Name = "Calibri"
            .Font.Size = 11
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 6
           
            .Cells(1, 1).HorizontalAlignment = xlLeft
        End With
       
        .Range("N4").Select
       
    End With
   
    Unload Me
    Application.ScreenUpdating = True
   
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("N1"), _
                                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("N4:S38")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
    End With
   
End Sub

Dave

 

[ipbr21054]

Hi,
That worked fine.
One thing i do notice is when i transfer from userform to worksheet.

Userform TextBox4 is a money value.
If i enter 12.34 in the TextBox i see it change to £12.34 when i transfer to worksheet which is fine.

However when i look in the cell on column Q i see £12.34 but also a green marker top left of the cell.
If i manualy type in the cell 12.34 i then see £12.34 without the marker.

Did i miss something in my code or maybe something was removed ?

 

[dmt32]

Did i miss something in my code or maybe something was removed ?

If data contained in a cell has an error and background error checking is enabled, then a green triangle is displayed in a cell. You can if required, disable it by adding this line of code at the end of your procedure.

Application.ErrorCheckingOptions.BackgroundChecking = False

Dave

 

[ipbr21054]

All done & now sorted.

Thanks