• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Displaying search results from my data

brazen

New Member
Hello.

I am learning vba as i go along and am creating an excel vba database to record repairs of products.

I have managed to put together a basic database, but there is code needed to make it work properly.

At the moment the database has only 1 tab for TV repair records. I will be adding a tab for other products like Mobile Phone, Fridge.....etc. Not sure how i will add these extra tabs, so will need some help with this.

Scenario
When i do have several tabs - TV, Mobile, Fridge, i will want to search for a search string like WiFi or Repairer Name or State of repair (open) and all records from all the tabs should be displayed as a list in the search results box.

Is this possible? If so, please can someone show me how or point me in the right direction?

One other thing i noticed is that when I add a new repair record, it overwrites the current one. It should a a new repair record to the next row down. Again if someone can show me how to do this please.

I have attached the file here.

I am continuously learning, so your help would be valuable.

Thanks in advance.

;-)
 

Attachments

  • Record product repairs.xlsm
    29.1 KB · Views: 11
... Is this possible?

It's possible. But personally, I'd go about it differently. You should have data entry sheet, where "ALL" data are stored (whether the sheet is updated via userform or other means is your choice).

Data entry should be validated as much as possible before it's submitted (i.e. use dropdown list, be it listbox or combobox, whenever possible).

Then loading list to useform listbox is simple matter of loading filtered list (using Dictionary, Array, or filtered range).

Individual tabs can be set up using pivot table to auto update when data entry sheet is updated (no need for code or just single line of code to refresh pivot cache).

As for your issue of overwriting existing value... that is because your CommandButtonAddData_Click() sub has hard coded value of "2" for row index.
You'll either need to dynamically obtain last used row (recommended), or use counter (not recommended).

If you search web, you should find some example workbooks that does exactly what you are after. Though you'll need to customize it to suite your need.
 
Hello Chihiro.

Thank you for your great suggestions.

I have been searching on Google for some ideas.

Do you know of any sites that have example workbooks that i could download and play with?

Cheers.
;-)
 
Hello Chihiro

Unfortunately i am not knowledgeable enough in Excel VBA to follow the instructions you have kindly provided. So may be if i could find a template that i can play with.

I have been looking online, but cannot find such template.

Your help would be appreciated.

B
 
Back
Top