victorel21
New Member
- Joined
- Jul 8, 2021
- Messages
- 25
- Office Version
- 365
- Platform
- Windows
Hi, I am making a combo box that allows the user to delete rows based on data in columns.
I have a code that prepares the combo box below it creates a list of unique values from a column. Problem is once I delete the lines and hide the form, when I open the form again a blank option appears and the first letter doesnt show. Even if I dont delete anything just rerunning the code creates the error. I have uploaded images.
I have a code that prepares the combo box below it creates a list of unique values from a column. Problem is once I delete the lines and hide the form, when I open the form again a blank option appears and the first letter doesnt show. Even if I dont delete anything just rerunning the code creates the error. I have uploaded images.
VBA Code:
Sub prepare_delete_by_status()
Set Wks = ThisWorkbook.Worksheets("Classification")
Set RngBeg = Wks.Range("C2")
col = RngBeg.Column
Set RngEnd = Wks.Cells(Rows.Count, col).End(xlUp)
Set Entries = New Collection
ReDim Items(0)
For row = RngBeg.row To RngEnd.row
Set cell = Wks.Cells(row, col)
On Error Resume Next
test = Entries(cell.Text)
If Err = 5 Then
Entries.Add index, cell.Text
Items(index) = cell.Text
index = index + 1
ReDim Preserve Items(index)
End If
On Error GoTo 0
Next row
index = index - 1
Descending = False ' Set this to True to sort in descending order Z-A.
ReDim Preserve Items(index)
Do
Sorted = True
For j = 0 To index - 1
If Descending Xor StrComp(Items(j), Items(j + 1), vbTextCompare) = 1 Then
temp = Items(j + 1)
Items(j + 1) = Items(j)
Items(j) = temp
Sorted = False
End If
Next j
index = index - 1
Loop Until Sorted Or index < 1
Form4.ComboBox1.List = Items
Form4.ComboBox1.Text = Items(0)
x = Form4.ComboBox1
Sheets("Classification").Activate
With Sheets("Classification")
Form4.TextBox2 = Cells(.Range("C:C").Find(x, Lookat:=xlWhole).row, 4)
End With
With WorksheetFunction
Form4.TextBox3 = Format(.CountIfs(Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox4 = Format(.SumIfs(Sheets("Classification").Range("G:G"), Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox5 = Format(.SumIfs(Sheets("Classification").Range("H:H"), Sheets("Classification").Range("C:C"), x), "#,##0.00")
End With
End Sub
Attachments
Last edited by a moderator: