Consulting

Results 1 to 3 of 3

Thread: Userform open from one worksheet and referencing another worksheet

  1. #1

    Userform open from one worksheet and referencing another worksheet

    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

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,667
    try changing:
    If Cells(i, 1) = ListBoxData.List(ListBoxData.ListIndex) Then
    Rows(i).Select
    Selection.Delete
    End If
    to:
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    A million thanks and yes that solved the problem. Most grateful for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •