+ Reply to Thread
Results 1 to 10 of 10

Split into multiple files using combo box and button

  1. #1
    Registered User
    Join Date
    06-22-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    32

    Split into multiple files using combo box and button

    Attached is the file that contains activex buttons. I appreciate your help...

    Thank you.
    Attached Files Attached Files
    Last edited by christlivethinme; 06-22-2012 at 05:00 AM.

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Split into multiple files using combo box and button

    I am not seeing any values in your dropdowns for both options.

    Also, how are you ensuring that the user doesnt click both option boxes?

    When you say the formatting for sheets A & B should be retained? Do you mean the formatting in the new files that are created?

    In what format should the new file be? Should it have the filtered data for each respective sheet in its own sheet? Where should the file be saved and in what name?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Split into multiple files using combo box and button

    Hi,

    Just had a short look at it, it's a project all right, i will not do it all, but will instead point you in the right direction.

    You populate a combobox like this

    Please Login or Register  to view this content.
    If the combobox should hold a lot of values then create an array and asign the values to the combobox list

    Next you should look in to the "Combobox_change" event

    Please Login or Register  to view this content.
    next up you create a new workbook, you can read how here

    http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

    Next you copy the data, here you should look into specialcells to copy only visible part of sheets 2 and 3

    Please Login or Register  to view this content.
    After coppying you save the new workbook, this you can read about in the link given earlier or in more detail here:

    http://www.rondebruin.nl/saveas.htm

    Clear the filter and your done!

    If you have en specific questions along the way feel free to ask them here
    Last edited by Steffen Thomsen; 06-22-2012 at 04:30 AM.
    Please take time to read the forum rules

  4. #4
    Registered User
    Join Date
    06-22-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Split into multiple files using combo box and button

    Quote Originally Posted by arlu1201 View Post
    I am not seeing any values in your dropdowns for both options.

    Also, how are you ensuring that the user doesnt click both option boxes?

    When you say the formatting for sheets A & B should be retained? Do you mean the formatting in the new files that are created?

    In what format should the new file be? Should it have the filtered data for each respective sheet in its own sheet? Where should the file be saved and in what name?
    Hi Sir,
    I have updated the file pls ignore and delete the other one.
    the formatting of the sheets a and b should also be retained in the new file.
    the new file will be in excel 2003 format
    ill take care of the filename, the new file should contain sheet a and b only, instructions sheet not included.

  5. #5
    Registered User
    Join Date
    06-22-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Split into multiple files using combo box and button

    Quote Originally Posted by Steffen Thomsen View Post
    Hi,

    Just had a short look at it, it's a project all right, i will not do it all, but will instead point you in the right direction.

    You populate a combobox like this

    Please Login or Register  to view this content.
    If the combobox should hold a lot of values then create an array and asign the values to the combobox list

    Next you should look in to the "Combobox_change" event

    Please Login or Register  to view this content.
    next up you create a new workbook, you can read how here

    http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

    Next you copy the data, here you should look into specialcells to copy only visible part of sheets 2 and 3

    Please Login or Register  to view this content.
    After coppying you save the new workbook, this you can read about in the link given earlier or in more detail here:

    http://www.rondebruin.nl/saveas.htm

    Clear the filter and your done!

    If you have en specific questions along the way feel free to ask them here

    thank you sir, but i am still new in macros, should i paste the code on the buttons and combo boxes?
    the option 1 drop down will contain 5 options and the other one will contain 3 options only, so i will have 8 files to generate.

  6. #6
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Split into multiple files using combo box and button

    Should all 8 file be generated every time? If this is the case i think you going about it the wrong way.

    The code for the populating the comboboxes should only be run once if they never change and therfore be placed in a module and then deleted.

    The combobox change event should be placed in the first sheets codemodule, so should the "CommandButton1_click" event

  7. #7
    Registered User
    Join Date
    06-22-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Split into multiple files using combo box and button

    Quote Originally Posted by Steffen Thomsen View Post
    Should all 8 file be generated every time? If this is the case i think you going about it the wrong way.

    The code for the populating the comboboxes should only be run once if they never change and therfore be placed in a module and then deleted.

    The combobox change event should be placed in the first sheets codemodule, so should the "CommandButton1_click" event

    Hi sir, 8 files can be generated everytime because we need the reports regularly, however i should also have an option to generate one by one.

  8. #8
    Registered User
    Join Date
    06-22-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Split into multiple files using combo box and button

    apologies for the confusion, the objects that i created are form controls, it should be activex

  9. #9
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Split into multiple files using combo box and button

    I think you should start creating the macros you need with the information given, if you have questions for pieces of code you have written then feel free to ask.

    Split up the project in smaller pieces.

    1. Create the comboboxes and build the code to populate them.
    2. Create the filter when a value is changed in the comboboxes
    3. Create the commandbutton action to copy data from the filtered sheets to a new workbook.
    4. Optional - Create a command button to create all

  10. #10
    Registered User
    Join Date
    06-22-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Split into multiple files using combo box and button

    I appreaciate your help guys, ill be waiting for your responses.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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