Coding Excel User Form

[Baiano42]

So I just learned that I can create a user form and build it to the desired setup, but I don't know how to assign functions or macros to make it work. Here is what I have now.
24736
Could someone help direct me to be able to set it up to do something along the lines of this macro:

Sub Delete_Rows_User_Input()Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "Delete Rows"
Set InputRng = Application.Selection
Set InputRng = Application.Intersect(Range("$A$1:$O$1000"), Range("$A$1:$J$1000"))
'But rather than just have one input, I'd like to be able to select multiple inputs, where if the table has either "A" or "B" in any cell within the table. (Hence why I'm making the user form)
DeleteStr = Application.InputBox("Select Valuse to Delete", xTitleId, Type:=2)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
'a problem when running this macro, is it gives an error code when I click cancel on the normal inputbox, so could the user form be designed to go through an error handler and move to a 'save' part of the macro rather than just exit sub?
DeleteRng.EntireRow.Delete
End Sub

Thank you for any assistance!!!

Cheers!

[Baiano42]

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:

24745


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

[Bob Phillips]

Can you post the full workbook?

[Baiano42]

24746
@XLD: Please find the WB attached above.
For reference, this is what I'm hoping to be able to make it do:


Set InputRng = Application.SelectionSet InputRng = Application.Intersect(Range("$A$1:$O$1000"), Range("$A$1:$J$1000"))
'But rather than just have one input, I'd like to be able to select multiple inputs, where if the table has either "A" or "B" in any cell within the table. (Hence why I'm making the user form)
DeleteStr = Application.InputBox("Select Valuse to Delete", xTitleId, Type:=2)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If

[Bob Phillips]

I thought you wanted to get the range address from the userform textbox? If you are deleting anything with a set value in it, you don't need a usrerform, or any sort of user input.

[Baiano42]

I'd like to make it where the textbox would select the user imputed range, and then, using that range, delete the rows in the range designated by the user's selected ComboBox entries. In the example WB I sent, Columns A-J are the range I need, and make it where the user could delete either just Green or everything from Green, Red, Blue, and Yellow.

[Bob Phillips]

Try this

[Baiano42]

@xld, That is certainly better! The code is much cleaner and it now is partially working rather than nothing working. As is, it is currently only deleting the values from the last ComboBox value. Eg. If ComboBox: Top 1-3 are filled, it will only delete for the values in combobox 3, and do nothing to the values in 1 or 2. I adjusted it to the following case and I think that I got it working as desired, I will play with it a little more and let you know when I get up tomorrow:

If Top4.Value <> vbNullString Then
DeleteRows Top4.Value
DeleteRows Top3.Value
DeleteRows Top2.Value
DeleteRows Top1.Value
ElseIf Top3.Value <> vbNullString Then

DeleteRows Top3.Value
DeleteRows Top2.Value
DeleteRows Top1.Value
ElseIf Top2.Value <> vbNullString Then

DeleteRows Top2.Value
DeleteRows Top1.Value
ElseIf Top1.Value <> vbNullString Then

DeleteRows Top1.Value
Else

[Bob Phillips]

Sorry, that was dumb of me. This is a better way of writing it.


Private Sub RunButton_Click()

If Top1.Value = vbNullString And Top2.Value = vbNullString And Top3.Value = vbNullString And Top4.Value = vbNullString Then

MsgBox "Kindly enter at least one Top value"
Else

If Top1.Value <> vbNullString Then DeleteRows Top1.Value
If Top2.Value <> vbNullString Then DeleteRows Top2.Value
If Top3.Value <> vbNullString Then DeleteRows Top3.Value
If Top4.Value <> vbNullString Then DeleteRows Top4.Value
End If
End Sub