MishTaylor
New Member
- Joined
- May 4, 2021
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
Hi Everyone, I'm working on a project and am new to VBA. I've followed tons of tutorials online but I am stuck trying to delete a row in my table based on a selection made in a listbox within a user form.
The purpose of the userform is to collect data on reallocating medication that was taken off the production line. We package drugs into weekly strip medication and sometimes there is a change required so we take the strips off the line. I'm trying to gauge what the costs and labour impact is. Operations needs to be able to input drug names and quantities they want to reallocate but I want them to be able to delete a row for whatever reason they may have made an error. I also need the form to clear the comboboxes and then the filters those comboboxes are set up against - I can't seem to get the worksheet to delete or clear the range of the filters. Any help would be great! Thank-you!
This is what the userform looks like:
What I've done is added "ID" column ("A") to the table to use as the search criteria. This ID number pops up in "textboxemtpy" on the userform which I've hidden. I need to match that textbox value to the row in my "Reallocation_Data" table and delete the entire row. When a record is selected in the listbox, it populates in the comboboxes which sets off the advanced filters I'm using to get the drug information in the combobox drop downs. I need to be able to select a row, delete it from the table and listbox along with clear the comboxes and advanced filter range in another sheet.
' DELETE BUTTON for entire selected row in userform --------------(not working)-------------------------------------
Private Sub CommandButtonD_Click()
If Me.textboxempty.Value = "" Then
MsgBox "Select the record to delete"
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Reallocation_Data")
Set ws = ThisWorkbook.Sheets("Drugs_in_RTS")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.Match(CLng(Me.textboxempty.Value), sh.Range("A:A"), 0)
sh.Range("A" & selected_row).EntireRow.Delete
' clear comboboxes -----------------------------(is working)--------------------------------------------------------
Me.ComboBoxGen.Value = ""
Me.ComboBoxStr.Value = ""
Me.ComboBoxDIN.Value = ""
Me.TextBoxQty.Value = ""
Me.textboxempty.Value = ""
' Refresh Listbox------------------------------------------------------------------------------------------------
Call RefreshData
' clear filters--------------(isn't working)----------------------------------------------------------------------------------
ws.Sheets("Drugs_In_RTS").Range("I2:P1000").Clear
End Sub
The purpose of the userform is to collect data on reallocating medication that was taken off the production line. We package drugs into weekly strip medication and sometimes there is a change required so we take the strips off the line. I'm trying to gauge what the costs and labour impact is. Operations needs to be able to input drug names and quantities they want to reallocate but I want them to be able to delete a row for whatever reason they may have made an error. I also need the form to clear the comboboxes and then the filters those comboboxes are set up against - I can't seem to get the worksheet to delete or clear the range of the filters. Any help would be great! Thank-you!
This is what the userform looks like:
What I've done is added "ID" column ("A") to the table to use as the search criteria. This ID number pops up in "textboxemtpy" on the userform which I've hidden. I need to match that textbox value to the row in my "Reallocation_Data" table and delete the entire row. When a record is selected in the listbox, it populates in the comboboxes which sets off the advanced filters I'm using to get the drug information in the combobox drop downs. I need to be able to select a row, delete it from the table and listbox along with clear the comboxes and advanced filter range in another sheet.
' DELETE BUTTON for entire selected row in userform --------------(not working)-------------------------------------
Private Sub CommandButtonD_Click()
If Me.textboxempty.Value = "" Then
MsgBox "Select the record to delete"
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Reallocation_Data")
Set ws = ThisWorkbook.Sheets("Drugs_in_RTS")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.Match(CLng(Me.textboxempty.Value), sh.Range("A:A"), 0)
sh.Range("A" & selected_row).EntireRow.Delete
' clear comboboxes -----------------------------(is working)--------------------------------------------------------
Me.ComboBoxGen.Value = ""
Me.ComboBoxStr.Value = ""
Me.ComboBoxDIN.Value = ""
Me.TextBoxQty.Value = ""
Me.textboxempty.Value = ""
' Refresh Listbox------------------------------------------------------------------------------------------------
Call RefreshData
' clear filters--------------(isn't working)----------------------------------------------------------------------------------
ws.Sheets("Drugs_In_RTS").Range("I2:P1000").Clear
End Sub