Forum Discussion

Joe null's avatar
Joe null
Copper Contributor
Jul 12, 2017

Prep List

I am a restaurant owner/operator and trying to create a good tool for our chefs to put out a neat and organized daily prep list for the crew at the start of each day.  Right now a lot of this is still done w/ paper and pen but with mulitple languages, this can be a challenge.  I've created an Excel file where one page list our prep items which right now is 90 lines long.  I didn't want to do a typical drop down list due to the length of this list.  My original thought was seeing if there was a way to have the master list and double click on the items that have to be prepped and they would populate a report that could be printed out for the crew.  I was unsuccessful in my online search so I decided to try using combobox.  I'm at a point now where I have the combo box working so that I can type in it and it filters down the list as more letters are added.  Not sure how to output these selections to a list or report that can be printed.  Any help is appreciated.

Macros & VBA
Show and Tell
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Aug 04, 2017

    I believe you want a listbox not a combobox.  See the attached file for an example.  You'll need to insert a code module and link the shape to the macro called AllTogether.  Then you can just pick the items you want in the listbox and click the shape and the 'Report' sheet will populate with your data...

     

    To Insert VBA Code:

    Alt + F11

    Insert > Module

    Paste Below Code

     

    To link the code to the shape:

    Right Click Shape that says 'Run Report' > Assign Macro > Select AllTogether

     

    Sub ClearData()

    Dim Lrow As Integer
    Dim sht As Worksheet
    
    Set sht = shtDest
    sht.PageSetup.PrintArea = "" 'Clear Previous PrintArea
    Lrow = sht.Cells(Rows.Count, "A").End(xlUp).Row 'Define last row based on data in column A
    If Lrow <> 1 Then 'Check to make sure there is actually data that needs to be removed
        sht.Range("A2:C" & Lrow).ClearContents 'Clear Old Data
    End If
    
End Sub

Sub GetData()

    Dim Lrow As Integer
    Dim sht As Worksheet
    Dim lstLp As Integer
    Dim mylst As String
    Dim myArr() As String
    
    Set sht = shtPrep

    With sht
        Lrow = .Cells(Rows.Count, "A").End(xlUp).Row 'Define last row based on data in column A
        With .Shapes("List Box 1").OLEFormat.Object
            For lstLp = 1 To .ListCount
                If .Selected(lstLp) Then 'If Prep Item is selected add to our filter
                   mylst = mylst & .List(lstLp) & "," 'Put all selected values in comma delimited string
                End If
            Next lstLp 'Go to next ListBox Item to see if it was selected
        End With
        myArr = Split(Left(mylst, Len(mylst) - 1), ",") 'Change comma delimited string into an array
       .Range("$A$1:$C$" & Lrow).AutoFilter Field:=1, Criteria1:=myArr, Operator:=xlFilterValues 'Filter all prep values
       .Range("$A$2:$C$" & Lrow).SpecialCells(xlCellTypeVisible).Copy shtDest.Range("A2")
       .AutoFilterMode = False 'Unfilter Data to leave the complete list up...
       shtDest.PageSetup.PrintArea = shtDest.Range("A1").CurrentRegion.Address 'Set PrintArea
       shtDest.Activate 'Activate Sheet so it can be reviewed and printed...
       
    End With
    
End Sub

Sub AllTogether()
    Application.ScreenUpdating = False
    Call ClearData
    Call GetData
    Application.ScreenUpdating = True
End Sub

     

Resources