PDA

View Full Version : [SOLVED] Userform open from one worksheet and referencing another worksheet



krackers
12-03-2019, 03:10 AM
Hoping someone might solve my dilemma after spending many hours searching the internet for a solution that works and trying umpteen alternatives that don't work!

I have a workbook that holds membership records for our charitable organisation. New member details are added to a worksheet named "Membership" using a userform. That userform currently opens from a command button embedded at the top of the "Membership" worksheet holding the data. When adding data, the details are displayed in a listbox at the foot of the userform and by highlighting a row in that listbox you can then delete the record or any record if needed. All works fine and as intended.

I want to move that button and have it embedded on a separate sheet along with other command buttons. Having moved the button, it all works fine except the delete record button. The code on that button is as below. I have tried altering the line referencing to the sheet named "Membership" in many ways but none seem to work. With the code below no error codes appear suggesting no syntax error but the record does not get deleted (although if I change the reference to "Sheet3" in place of Membership I get an "subscript out of range" error 9 msg). (As I say when embedded on the same sheet holding the records it works fine). I am a newbie at VBA so before I am left with no hair at all can someone assist or point me in the right direction please? Many thanks.

Code being used follows:

Private Sub CmdDelete_Click()
Dim i As Integer
If MsgBox("Are you sure you want to DELETE this record?", vbYesNo + vbQuestion, "Delete Record") = vbYes Then
For i = 1 To ThisWorkbook.Worksheets("Membership").Range("A65356").End(xlUp).Row
If Cells(i, 1) = ListBoxData.List(ListBoxData.ListIndex) Then
Rows(i).Select
Selection.Delete
End If
Next i
End If
Call UserForm_initialize
End Sub

p45cal
12-03-2019, 04:37 AM
try changing:
If Cells(i, 1) = ListBoxData.List(ListBoxData.ListIndex) Then
Rows(i).Select
Selection.Delete
End Ifto:

If ThisWorkbook.Worksheets("Membership").Cells(i, 1) = ListBoxData.List(ListBoxData.ListIndex) Then ThisWorkbook.Worksheets("Membership").Rows(i).Delete(remove the first End If)

In full and a bit shorter:
Private Sub CmdDelete_Click()
Dim i As Integer
If MsgBox("Are you sure you want to DELETE this record?", vbYesNo + vbQuestion, "Delete Record") = vbYes Then
With ThisWorkbook.Worksheets("Membership")
For i = 1 To .Range("A65356").End(xlUp).Row
If .Cells(i, 1) = ListBoxData.List(ListBoxData.ListIndex) Then .Rows(i).Delete
Next i
End With
End If
Call UserForm_initialize
End Sub

krackers
12-03-2019, 05:07 AM
A million thanks and yes that solved the problem. Most grateful for your help.