CREATING A SEARCH USER FORM WITH A MULTI COLUMN LIST BOX

    Sincere regards to all.



    Am new here but happy to join the aspiring cadets and knowledgeable chieftains of programming in Excel VBA. I am one who has taken up an increasing enthusiasm of VBA programming in Excel despite the view of many of my friends who have a preference for advanced programming platforms like Visual Studio, C++ and so forth. I find Excel VBA very entertaining and at times thoroughly challenging as is the case now. I spent an entire day yesterday trying to come up with a Search User Form tailored with a multi columned list box. The purpose of this User form is to search and list data based on three criteria. The name of a Cashier and the sales he/she has conducted between two dates: Start and End Dates.


    My coding yesterday actually amounted to nothing if not endless errors. Am humbly seeking expert assistance in constructing a workable code that will give me an efficient working search user form. I have attached a sample Excel worksheet with the relevant data.


    I will appreciate any willing assistance I can get in achieving this.



    Cheers

    Thanks for your comment Roy


    I have included my user form in this second attachment. I totally messed up the original coding and in my frustration that almost made me push the laptop off my desk I deleted the entire code content with the intention of starting afresh this morning. Kindly bear with me for having cleared the code I had originally constructed.

    Good morning Roy


    I have just checked out your two sample and I must admit I am very much impressed . Allow me to express my sincere appreciation for your time and effort in coming up with two samples. Don't know how I can satisfactorily thank you for what you have accomplished. Am awed by your calendar date pickers. Very impressive indeed, I have actually heard about the date picker and seen samples of them but my Visual Basic does not have the date picker tool. Tried downloading one from Microsoft but the terms and conditions put me. off. Presently am trying to come up with a Sales and Stock Management System for my business and my preference was via Excel VBA coding. Please don't be tired because it's likely you I will be consulting you more often in the coming days. Thanks too for including the Total sales box actually guessed it's relevance and it's something I forgot to mention in my initial details last Saturday. Another two questions I had in mind was will the user form still work if I switch off the filter buttons in the worksheet. And when it comes to printing how does one print displayed data from a list box?


    Sincere regards from me in Nanyuki, Kenya



    Cheers and do have a Fab day,

    The code manages the AutoFilter switching on and off, so it doesn't matter whether they are displayed on the sheet.


    You cannot successfully print a UserForm. The code works by copying the filtered data to a hidden sheet so you can format that sheet to print.

    Hello Roy


    Sincere regards to you. Do hope that you are doing fine since we last communicated. Am well at this end fine sunny weather prevailing here at this end of the world map.


    Am still sailing the storm of my stock and sales management system with challenges encountered here and there in constructing the relevant VBA code. I have uploaded the structure of my project. As it is of now I would say it’s approximately 65% done but still much to get done.


    Please take a look at Userform7. I cloned the code you provided to me for a list box with slight modifications on my part. On this user form one ought to be able to view Data based on Receipt Nos, individual cashier transactions between two dates (As I inquired from you), individual item sales and transactions dates. Using your provided code when I run the form and I enter the start /end dates and cashier name and click the DISPLAY RESULTS button I get this message


    Run time 91:

    Object Variable or With block Variable not set


    Click Debug and it highlights this section of my code under command button 2 (DISPLAY RESULTS)

    rData.AutoFilter Field:=3, Criteria1:=Me.ComboBox1.Value


    I have tried cracking my brain over this error Roy, but no head way. Kindly give me a clue where I need to make some tweaking to my coding.


    Thanks in advance


    DEBLACK JACKAL

    When you copied the code into the ne userform it looks like you actually typed in the initialize event because the name is incorrect


    Code
    1. Private Sub UserForm7_Initialize()

    It should be


    Code
    1. Private Sub UserForm_Initialize()

    You have also manually set the RowSource Property of the ComboBox. Doing that means the ComboBox cannot be populated by code.


    The ShowAllData is referencing the wrong worksheet


    Code
    1. With Sheets("Sheets10").Cells(1, 10).CurrentRegion

    It should be


    Code
    1. Worksheets("Sheet7").ShowAllData

    Good afternoon Roy (I don't know of your time zone but it's 3.55 pm at this end:))


    You seem to have some magic hand with VBA coding. Please consider putting up a website that will attune us to the regimes of VBA coding. I went through my coding the one hundredth time in exasperation. The way you have pointed out my simple syntax errors reminds me of the patience and keenness coding requires in every aspiring programmer.


    Errors noted and my sincere and warm appreciation again. I note however the columns in the List box are not displaying the column titles ?


    Regards


    DE BLACK JACKAL

    Hello Roy


    Back to you again.

    What does it take to clear a bound List box? Am using the Listbox.Clear code but the debugger highlights the latter code as having an error.


    Checked out your website and filled the submission form.


    Regards


    DE BLACK JACKAL

    Hello Roy


    What I filled was the Contact form on your website. Entered my name, email address and message and submitted the details


    As per my programming effort I’ve managed to add a Total box courtesy of the guidance of your coding sample. Am thinking of removing the individual cashier search option since apparently it doesn’t have any relevance if one can check up an individual cashier’s transaction under the start and end date options. My problem at the moment is Command button 6 (CLEAR DATA RESULTS). When this is clicked it ought to clear or refresh the entire battery of text boxes and the list box. It seems to be working the first time I try it but on next attempt errors pop up highlighting the Listbox1.Clear code. Why?


    Secondly An inquiry I had was supposing I was to place the Start and End Date text boxes and the Cashier combo box plus relevant button into a Frame to enhance user navigation will I have to do some revision to the relative coding? Will be sincere that VBA coding at times gives me the creeps;)


    Regards


    DE BLACK JACKAL