+ Reply to Thread
Results 1 to 10 of 10

Creating a Parts List from an Equipment Database with checkboxes for each row

  1. #1
    Registered User
    Join Date
    02-11-2016
    Location
    Boston, MA, USA
    MS-Off Ver
    2013
    Posts
    5

    Lightbulb Creating a Parts List from an Equipment Database with checkboxes for each row

    Hello!

    I have a workbook that contains equipment database tables for each equipment categories as found in the first 7 sheets. By using the checkboxes of each row, I would like to select rows (a la carte equipment) that will populate/insert the equipment row information under the corresponding category found in the Parts List sheet. By unchecking the checkbox, the equipment should be removed from the Parts List.

    Please, if you would be so kind to help!

    Also, as a side note, where would you recommend a good place to start learning VBA as a very beginner? It has caught my attention...

    AV Equipment DatabaseIN_PROGRESS with MACRO2.xlsm
    Last edited by JoeyFinnz; 02-11-2016 at 03:00 PM. Reason: Attaching excel doc

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    Hi Joey,

    You can use a code like this...

    Please Login or Register  to view this content.
    It loops through every sheet and every checkbox and if the checkbox is activated it copies the cells over to the parts list sheet.
    It also clears each check box as it goes. You can also update twice in a row (i.e. if you miss a tick box)
    I have also included some code to clear the PARTS LIST sheet as well.

    AV Equipment DB.xlsm

  3. #3
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    If you have any questions about how the code works let me know.

  4. #4
    Registered User
    Join Date
    02-11-2016
    Location
    Boston, MA, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    Thanks spitfire! This is great. I've hashed out some formatting issues so that the equipment database rows translate correctly onto the Parts List. The only question that I have now is: Is it possible to have the Check Boxes remain in their checked/unchecked state when the Update Button is pressed? That way if equipment updates need to be made to the Parts List, then the user can step into the database, see what is, or is not, included on the Parts List, and add/remove as required.

    AV Equipment Database-MACRO.xlsm

  5. #5
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    Hi Joey,
    Yeah, just jump into the code and delete the line:
    Please Login or Register  to view this content.
    However, the reason that I did clear the check boxes is so that if you go back and select more check boxes and then run the update again then it will add them into the parts list below the ones you already have. To remove parts all you have to do is to delet the row in the parts list.

    The other thing is that it would be annoying having to untick everything when you start again, but I could write another macro to do this if you want?

  6. #6
    Registered User
    Join Date
    02-11-2016
    Location
    Boston, MA, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    The more that I think of it, the more I like your reasoning, so the check boxes clearing works great.

    Let's take this document a step further...

    How about adding a "Publish" button which will add a sheet and prompt for a room name to be populated in Cell D1 as well as the new sheet's title? The new sheet will will also contain all of the information found on the populated Parts List, excluding the buttons.
    Last edited by JoeyFinnz; 02-12-2016 at 04:29 PM.

  7. #7
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    Hi Joey,

    Try this:

    Please Login or Register  to view this content.
    This calls for the room number in an input box and then copies the sheet into a new workbook and removes the buttons.

    AV Equipment Database-MACRO.xlsm

  8. #8
    Registered User
    Join Date
    02-11-2016
    Location
    Boston, MA, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    Hey Spitfire,

    Cheers to you! I just performed a demo for my boss and he loved it! Of course, it came with some feedback. I'd like to add an "import" button so that I can import the data found in the new excel doc that had previously been created by the "publish" button into the "parts list" sheet of the database. Once the data is imported then the corresponding checkboxes in the database will change to the checked state. The equipment can then be added or removed from the "parts list" sheet.

    AV Equipment Database.xlsm

  9. #9
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    Hi Joey,
    The first part can be done by opening the saved workbook and simply copying the data back across...
    Please Login or Register  to view this content.
    However, I don't believe that you need for the checkboxes to be re-ticked. To add further equipment you just need to tick the boxes of the additional equipment and then select update.
    To delete equipment, I have added a macro that will delete the entire row that you have selected when you select Ctrl + Shift + D.

    AV Equipment Database.xlsm

  10. #10
    Registered User
    Join Date
    02-11-2016
    Location
    Boston, MA, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Creating a Parts List from an Equipment Database with checkboxes for each row

    Hey Spitfire,

    I have a bit of a bug with this database. When I select some of the check boxes, the incorrect rows are copied and pasted into the Parts List. Could you please take a look and tell me what you find?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Quotes, Picklists, Parts Database
    By Scottmcq12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2014, 03:21 AM
  2. Scan parts OUT to Sheet1, parts IN to Sheet2 and list in Sheet3
    By Nu2Java in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2012, 11:40 AM
  3. UserForm for filtering/querying a database of parts
    By MattParry in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2012, 10:25 AM
  4. creating a parts list
    By artj in forum Excel General
    Replies: 3
    Last Post: 07-29-2012, 06:42 PM
  5. creating a hierarchy from equipment list
    By mrggutz in forum Excel General
    Replies: 2
    Last Post: 02-14-2011, 07:26 PM
  6. Create User form to pull from equipment list and fill in data
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-29-2009, 02:48 PM
  7. Creating a used parts list?
    By danjstock in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2008, 10:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1