Please help..

[Mally1975]

I'm hoping someone out there will be able to help as I've been banging me head against this brick wall for some time..

What I need is a simple lookup form that the user of the spreadsheet can enter either the product number and/or keyword and perform a product search from the 'Catalogue' on the second sheet.. I appreciate that this is probably 're-inventing the wheel' here and I know that Excel can already do this, but what I need is an 'idiot-proof' (for want of a better word) lookup form where the user just sees the data they enter and they don't have to adjust or fiddle with anything (or accidently delete data).. My layout so far is this:

Sheet 1: Welcome page with buttons on to accomplish various tasks - but this is where I would like the search button and pop up form..
Sheet 2: Product Database - currently locked down to prevent editing etc.
Sheet 3: Possible pivot table with results? - Not 100% sure this is the right way to go about it..

Does anyone know if this is possible and what's the best way to go about it please?.. I would also appreciate help with the code if possible..

 

[pboltonchina]

This should give you a nudge in the right direction.

Private Function SheetExists(Sheetname As String) As Boolean
    ' Returns TRUE if a sheet exists in the active workbook
    Dim x As Worksheet
        On Error Resume Next
            Set x = ActiveWorkbook.Sheets(Sheetname)
                If Err = 0 Then SheetExists = True _
                Else SheetExists = False
End Function

Sub FindAllSheets()
    Dim Found As Range, WS As Worksheet, LookFor As Variant
        LookFor = InputBox("Enter value to find")
            
            If LookFor = "" Then Exit Sub
            
            '   Clear or Add a Results sheet
            If SheetExists("Search Results") Then
              Sheets("Search Results").Activate
              Range("A2").Select
              Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
              Selection.ClearContents
              Range("A1").Select
             Else
                Sheets.Add after:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Search Results"
            End If
            UserForm1.Show False
            UserForm1.Repaint
        Application.ScreenUpdating = False
           For Each WS In ActiveWorkbook.Worksheets
                If WS.Name <> "Search Results" Then
                     Set Found = WS.Cells.Find(What:=LookFor)
                     If Found Is Nothing Then
                         Range("D5").Select
                     Else
                         Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                         Found.EntireRow.Interior.Color = vbYellow
                     End If
                End If
            Next WS
            Unload UserForm1
            With Sheets("Search Results")
  Set rngLast = .Cells.Find(What:="*", after:=.Cells(1, 1), LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
  If rngLast Is Nothing Then
    MsgBox "Search item not found! Exiting Sub...": Exit Sub
  Else
    If rngLast.Row = 1 Then _
      MsgBox "Search item not found! Exiting Sub...": Exit Sub
  End If
End With

            Sheets("Search Results").Activate
            Columns("A:T").AutoFit
           
            
    Range("A1").Select
     Application.ScreenUpdating = True
                 
End Sub

 

[Mally1975]

Thank you so much for your helpful pointer.. It's much appreciated..