Good morning all,
I was able to update the UserForm to something a little more simple, as well as get it coded to open/close, and update the ComboBoxes as desired. I have outlined the "Run" button in a way that I think the macro would need to be set, but I don't know how to set the "CellRange" TextBox as a range, nor do I know how to get the delete range part working either. Guidance would be greatly appreciated. See update form and code below:
Private Sub FinishButton_Click()
'Close form
Unload Me
End Sub
'Here is where I would need to Run the form. I was thinking that by using the If/Then/Else
'I could make it where it there was nothing in Top1, I would get an error message (Which that works),
'if noting is in top 2, it would just delete what's in top 1, and the same for top 3 & 4.
Private Sub RunButton_Click()
CellRange = DeleteTops.CellRange.Value
Top1 = DeleteTops.ComBox1.Value
Top2 = DeleteTops.ComBox2.Value
Top3 = DeleteTops.ComBox3.Value
Top4 = DeleteTops.ComBox4.Value
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
If Top4 <> "" Then
Else
If Top3 <> "" Then
Else
If Top2 <> "" Then
Else
If Top1 <> "" Then
Else
MsgBox "Kindly enter Top"
End If
End If
End If
End If
End Sub
Private Sub UserForm_Initialize()
CellRange.Value = "$A$1:$J$1000"
'Fill ComboBoxes with unique top values.
On Error Resume Next
Dim myCollection As Collection, cell As Range
Set myCollection = New Collection
Dim myCollection2 As Collection
Set myCollection2 = New Collection
Dim myCollection3 As Collection
Set myCollection3 = New Collection
Dim myCollection4 As Collection
Set myCollection4 = New Collection
With ComBox1
.Clear
For Each cell In Range("I2:I" & Cells(Rows.count, 1).End(xlUp).Row)
If Len(cell) <> 0 Then
Err.Clear
myCollection.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
With ComBox2
.Clear
For Each cell In Range("I2:I" & Cells(Rows.count, 1).End(xlUp).Row)
If Len(cell) <> 0 Then
Err.Clear
myCollection2.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
With ComBox3
.Clear
For Each cell In Range("I2:I" & Cells(Rows.count, 1).End(xlUp).Row)
If Len(cell) <> 0 Then
Err.Clear
myCollection3.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
With ComBox4
.Clear
For Each cell In Range("I2:I" & Cells(Rows.count, 1).End(xlUp).Row)
If Len(cell) <> 0 Then
Err.Clear
myCollection4.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
End Sub