Need help completing my Userform Repair db

    Repair Database.xlsm


    Hi folks.


    I am a noob when it comes to excel vba and am currently learning.


    I am creating an excel vba database to record repairs of electronic/electrical products.


    I have managed to put together the building blocks of the database front, but am struggling with the code to do the following:


    1. Add repair record to the next row down. When I add a new repair record, it overwrites the current one.

    2. After I add the repair data nothing happens. Ideally would like a message saying I have added data successfully and data is then cleared in the userform.

    3. How can I add a new product tab, for example Fridge or Mobile phone? At present I only have a TV product tab, but later want to add more product tabs.

    4. Once a new product tab is created, is it possible to automatically have the same columns as the TV tab?

    5. How can I display list of repair records in the search results box as I type any words from any repair record from different tabs?


    I have attached my database excel file.


    I am open to constructive criticism, which no doubt will help me to learn.


    Any help would be greatly received please.


    Thank you.

    Hello and Welcome to the Forum :)


    First of all ... Congratulations on what you have already accomplished ... !!!


    You should tackle ( and fix ) each problem ... one after the other


    Regarding Adding Data, you will agree you do need to identify the last used row and select the first on available ...


    so, you should insert following instruction


    last = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1


    and then ... ensure all your instructions use this variable ... which should the hard-coded 2 ...


    e.g. Worksheets("TV").Cells(last, 1).Value = TextBox1.Text


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Hello Carim.


    Thank you for replying to my problem.


    I am not very good at Excel VBA and i put this form together from different examples online and watching youtube videos.


    I am still learning and admittedly finding it a little difficult to understand VBA properly, but i am still trying to learn.


    I am not sure where i should put your code - last = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1


    Appreciate your help.


    Thank you Carim.

    You should not add different Tabs for different items. Maintain all in one database with a column for device type.


    I would not use the Option Buttons but a ComboBox, this will reduce code and allow adding different items later more easily.


    This is how I would write the add code


    I'll add a combobox and look at the rest of the code for you.



    Below is the modification you asked for ...



    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    1. Add repair record to the next row down. When I add a new repair record, it overwrites the current one.


    I have added code to do this

    2. After I add the repair data nothing happens. Ideally would like a message saying I have added data successfully and data is then cleared in the userform.


    I have added a message and the data is refreshed in the ListBox

    3. How can I add a new product tab, for example Fridge or Mobile phone? At present I only have a TV product tab, but later want to add more product tabs.


    Definitely do not, see my earlier comment

    4. Once a new product tab is created, is it possible to automatically have the same columns as the TV tab?


    You would nee a template sheet, but I would strongly recommend not to

    5. How can I display list of repair records in the search results box as I type any words from any repair record from different tabs?


    I have made the ListBox show all the data. What would you search for?



    I have added a new UserForm to show how I would build your form. It contains a non-activeX Calendar for the dates

    All data is loaded from the database to the ListBox, click an item in it to load that item's data to the TextBoxes, etc,


    Remember, to make sure your userform looks professional keep it tidy, align controls to make the form presentable

    Quote from Carim


    Below is the modification you asked for ...



    Hope this clarifies

    Carim, really appreciate the modification you did.


    Thank you for your great help.


    ;-)

    Hi Roy


    You have cleaned up my messy code and even the UI looks better.


    I sure will take your advice and try and keep the userform looking professional and tidy. However, i have a long way to go in order to get to a comfortable level of understanding of Excel VBA, but you have certainly helped me on my way.


    With regards to the 'adding of new tabs', my idea was to add a new tab (worksheet) every time a new product (e.g Mobile Phone, Fridge, Computer....etc) was repaired. To be honest, i don't know of any other way to handle this? Or would it be a case of adding all repair records into one long list in one tab, and using some kind of search option to pick records?


    And for point number 5, what i was trying to envisage is something like auto-complete search function. For example, if there were 50 repair records across several tabs (worksheet). And 25 of these records had the words 'power' in them, i could type the word power, and as i type each letter, the listbox would populate to match records containing each letter until i typed the whole word 'power' and eventually only the 25 records with the word 'power' would be displayed in the list box. This way it would save me from going through each tab looking for these repair records.


    Anyway, i will use your form as a base to develop and realise my idea for this project.


    Your suggestions above and any other suggestions are greatly appreciated and welcomed.


    Thank you very much for your support on this.


    Good evening.


    ;-)

    You need to add a column for the type of device. Then you create reports easily from that data and no complicated searching across sheets.


    I'll add a column for you to see what I mean

    Hi Roy


    I have not thought about using a column for each type of device, but would definitely consider it.


    I will have to change the way i am planning this, so that i can add 'all' data into one worksheet. I guess i will have to add the device type in row A and then the rest of the information into the subsequent columns? For example in Cell A2, add the device type (TV), then the following cells will have the repair record information. Is this what you mean?


    Hopefully you're amended excel will give me more insight into this.


    Thanks again.


    ;-)

    I've added a column for device type.


    Also in the userform there's a combobox to pick from. The ComboBox is loaded from a dynamic named range based on a Table in the new sheet(Lists), you can add items into the list and they will automatically load to the combobox on loading.

    Pleased to help. Post back as problems arise. You will need to look at ways of reporting the data when you have completed the Form.


    I have a userform that allows you to filter the ListBox with if you need to do that.

    Hi Roy.


    I have a lot of work to do in order to get your userform working for me.


    Once i have managed to get it to work as i expect, i will probably need to do some filtering for the listbox, so yes please if you will, provide the userform for listbox filtering.


    Once again, you have provided great and meaningful support.


    Thanks a million.


    ;-)