Unwanted cell added when sending userform to worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I am using the code below.
I run the code make a selection from ComboBox1,& add values to TextBox2,3 & 4
TextBox values 2,3 & 4 and sent to cell columns U,V & W which is corect.
The issue is column Y generates something.

Please see attached screen shot

Rich (BB code):
    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)
       
        If Len(arr(i)) = 0 Then
            MsgBox "YOU MUST COMPLETE ALL THE FIELDS", vbCritical, "ADD CUSTOMERS NAME MESSAGE"
            Exit Sub
        End If
    Next i
   
    Application.ScreenUpdating = False
   
    With wsGIncome
        lastRow = .Cells(.Rows.Count, "U").End(xlUp).Row + 1
       
       With .Cells(lastRow, 21).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
            Application.ErrorCheckingOptions.BackgroundChecking = False
       End With
           .Range("U5").Select
       
    End With
   
    Unload ADDNAME
    Application.ScreenUpdating = True
   
    With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=Range("U4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("U4").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
   
End Sub



And also

Rich (BB code):
   Private Sub ComboBox1_Click()
   Me.TextBox2.Value = Sheets("G INCOME").Range("U" & Me.ComboBox1.ListIndex + 3).Value
   Me.TextBox3.Value = Sheets("G INCOME").Range("V" & Me.ComboBox1.ListIndex + 3).Value
   Me.TextBox4.Value = Sheets("G INCOME").Range("W" & Me.ComboBox1.ListIndex + 3).Value
   End Sub
 

Attachments

  • EaseUS_2023_05_29_16_26_07.jpg
    EaseUS_2023_05_29_16_26_07.jpg
    19.1 KB · Views: 19

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Generates what, Null? Your choice count is 4, your upper boundary is 5, thus you have one more evaluation than you have choices. That will return Null for the 5th evaluation. I expect that this is also affecting your range resize. Did you step through the code (F8) and test/watch your variables? That's troubleshooting 101.
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top