1

Objective : Create a userform and take a user input, and then from user input put it in a list and when you click the list it automatically find it in the whole workbook.

Something Like this:

enter image description here

I saw this post: Match in whole workbook

And I created something out of that:

Public Sub CommandButton3_Click()

    Dim TempArray As Variant
    Dim RealArray()

    TempArray = Application.InputBox("Select a range", "Obtain Range Object", Type:=64)
    ArrayRows = UBound(TempArray)
    ReDim RealArray(1 To ArrayRows)
    For i = 1 To ArrayRows
        RealArray(i) = TempArray(i, 1)
    Next i

    MsgBox "The number if rows selected are " & ArrayRows
    ListBox1.List = RealArray
    ListBox1 Arraay:=RealArray

End Sub

Public Sub ListBox1_Click(ByRef Arraay() As Variant)
    Dim Sh As Worksheet
    Dim something As Range
    Dim ArrayRows As Long

    For Each Sh In ThisWorkbook.Worksheets

        With Sh.UsedRange
            For i = 1 To ArrayRows
                Set something = RealArray.Find(What:=RealArray(i))
                If Not something Is Nothing Then
                    Do Until something Is Nothing
                        test = something.Value
                        Set something = .FindNext(something)
                    Loop
                End If
            Next i
        End With
        Set something = Nothing

    Next
End Sub

After creating this, I get an error regarding the second sub.

procedure declaration does not match description of event or procedure having the same name

2
  • Public Sub ListBox1_Click(ByRef Arraay() As Variant) It doesn't take any parameters. It should be ListBox1_Click() Commented Aug 12, 2016 at 4:22
  • @SiddharthRout But how do I pass the array between sub? isn't that is one of the method? sorry, I'm quite new to VBA Commented Aug 12, 2016 at 4:29

1 Answer 1

1

The Listbox click event doesn't take any parameters.

Private Sub ListBox1_Click()

End Sub

If you want to pass an array between sub then you can so it this way

Dim MyArray() As Variant

Public Sub CommandButton3_Click()
    '~~> Initialize array
End Sub

Private Sub ListBox1_Click()
    '~~> Use array here
    '~~> Also put an error check if the array is initialized or not
End Sub
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.