Closed Thread
Results 1 to 32 of 32

CheckBox and Submit Button

  1. #1
    Registered User
    Join Date
    06-14-2008
    Posts
    26

    CheckBox and Submit Button

    Hello,
    i'm trying to create a macro in which there are checkboxes and there is a submit button.
    each checkbox corresponds with each month's expenses
    so i will have a total of 12 checkboxes and one "submit button"
    i want to make a macro that will give me an option to select which month's expenses to view. all the expenses are on one sheet and how the checkbox should work is very simple. it will hide the unwanted columns to view the desired months. simple but the coding isn't. the months will be viewed until after the sumbit buttons is pressed

    thank you for all your help
    i would greatly appreciated if someone could help me out
    thanks in advance again

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You really need a UserForm to do this. Can you attach an example workbook so that I can see your layout?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    well basically what i have is 1 sheet with all the month's expenses

    for january it is columns A through C
    february it is D through F
    and so on....

    if i select january checkbox, i want to hide february columns and also is it possible to have multiple selections? for example to view february and march only?
    thanks for your help and also what is Userform?

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    I'll see what i can do. Food time at the moment so check back later

  5. #5
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    thank you
    please help

  6. #6
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Hello Again

    i want to make checkboxes with a submit button

    all my months expenses are on one sheet
    each checkbox corresponds with each month
    want to be able to select multiple checkboxes at once
    basically how it should work is that it should only hide columns that are not wanted at that time

    thanks again for all your help

    Jenn

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You have three columns for each month?

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jenniferkognan,

    Maybe this worksheet example is more of what you are looking for. This worksheet will hide/unhide the rows associated with a given month. It uses a ListBox with the check box option. You select the months then click Submit to hide the unwanted data.

    Sincerely,
    Leith Ross
    Attached Files Attached Files

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Is this what you want
    Last edited by royUK; 06-15-2008 at 04:32 AM. Reason: Remove attachment, see later example.

  10. #10
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    not quite guys

    okay i have all 12 months expenses

    january is columns A, B, C
    feb is D E F
    march is G H I
    april J K L
    and so on...
    they are all on the same sheet

    i should have a checklist of to select which months to view
    and hit submit and it would hide all the other ones that i didnt select

    and so on and it should also have a reset button so i can unhide ALL columns

    thanks
    please bear with me i know this is confusing

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jenniferkognan,

    This workbook will open with the expense form showing. It hides/unhides data in column "A:AJ" (3 columns per month). There is a Submit and Reset button on the form.

    Sincerely,
    Leith Ross
    Attached Files Attached Files

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    I've amended the code, there was a typo You can select the months to hide or show
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    yes thank you guys very much for all your help
    now my next part is how do i use the UserForm and code to my file?

    just copy and past the code? and how?

    Thank you soooooooooooooooooooooooooooooooooo muchhhhhhhhhhhhh for helping me out.......................

    and i can make the userform graphically creative correct?

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jenniferkognan,

    Here is how to "copy" the UserForm to your workbook.

    Export the User Form and Save it to Disk
    1) Open Example 2.xls
    2) Open the Visual Basic Editor using ALT+F11
    3) Under your VBAProject double click on UserForm1
    4) Activate the File menu using ALT+F
    5) Export the UserForm with CTRL+E
    6) Close the workbook

    Import the UserForm to Your Workbook
    1) Open your workbook
    2) Open the Visual Basic Editor using ALT+F11
    3) Activate the File menu using ALT+F
    4) Import the UserForm using CTRL+M
    5) Save the changes

    This will copy the user form and all its code into your workbook. The code is setup that each month is 3 columns wide. January is "A:C", February is "D:F", etc. The user form checks the months selected in the list box , and hides the columns that aren't checked when the user clicks the Submit button.

    Sincerely,
    Leith Ross

  15. #15
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Alternatively to Leith's suggestion you can simply drag 7 drop a userform or module to another workbook object.

    Open both workbooks.
    Open the Visual Basic Editor using ALT+F11
    You should see the list of open VBA projects to the left.
    Click on each one to open up a view of it.
    Locate the UserForm & drag it to the other Projec. Do the same with any code needed, such as the shForm macro.
    Save the destination workbook.

    The advantage of Leith's instructions are that you will have an external copy of the form for future use.

  16. #16
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Thanks So Much again guys you have been great help

    also i had one more thing i forgot to mention

    at the end of each month, there is a total amount, for example, for january there is a total amount on cell C33 and for february F33
    and what i want to do is when i click submit for those months, i want those values to be added to a cell at the end for example cell # AK33

    Many thanks in advance

  17. #17
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    the total values should be added for any month that i select

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello ,

    I changed the Submit macro to take the total of each selected month and put it into "AK33".
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Thank You So Much Again

    but is there anyway i can edit the UserForm?
    and if possible i can edit the code correct? from the UserForm code? if i need to change columns
    Love,


    Jenn

  20. #20
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Also one more thing,
    can you add a button on the worksheet itself, so i can launch the dialog box from the worksheet instead of re-opening the sheet itself.

    the button should be named "Launch Month Selector"

    Thanks Again

  21. #21
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jenniferkognan,

    I added the button to show the user form.

    Sincerely,
    Leith Ross
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    is there anyway i can move the button or modify it?
    because i cant seem to modify it

    Thanks

  23. #23
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jennifer,

    The button is from the Controls Toolbox. Go to View > Toolbars > Control Toolbox. Click on the design icon. That is one with the drafting triangle, pencil, and ruler on it. Right click the button to move it or change the formatting.

    Sincerely,
    Leith Ross

  24. #24
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Hello,
    it's me again

    Also what i was looking for, i think this would be impossible, but rather let me give it a shot

    there's a a "paid to" with "type of payment" (for example, credit card, check etc..)column, standard "number" column (lists all the bills like 1, 2, 3, in numerical order), and finally there's a "amount" column. is it possible to have them organized when you make the selection.
    each month has four columns so i think you would have to edit the code

    and on the window that pops up, is there anyway you can add a check box selection, like an option that says "okay for that particular month (s), organize it by "amount" (ascending) , it is already in alphabetical order, when the user inputs the data in the column it is alphabetical order...

    so each month has four columns

    jan - A B C D

    feb - E F G H


    and so on.....

    Thanks so muchh............

  25. #25
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Also i want to have an option to organize it by the type of payment also
    so a total of two options....
    the user can only select one of them....

    and when you select an option, the corresponding "paid to" individual or "amount" should be the same meaning
    if the user selects to organize by amount, the "paid to" should correspond to the amount, it should be like grouped together so that when organizing it, it really doesnt' mess up the data....

  26. #26
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Can you attach a zipped example workbook?

  27. #27
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Okay RoyUK, i have attached the workbook and also Leith Ross's workbook

    Leith's layout is what i want, Thank you for helping out

    please read instructions in the workbook.
    Attached Files Attached Files
    Last edited by jenniferkognan; 06-16-2008 at 10:32 AM.

  28. #28
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Please someone help me
    Thanks So Much in advance...

  29. #29
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jenniferkognan,

    I made the changes you requested to the attached workbook. Each month now uses 4 columns per month. The totals in the 4 column of row 52 for month are totaled together as before. The user form can be launched by typing CTRL+R from any worksheet or by typing ALT+L when the worksheet with the button is active.

    Sincerely,
    Leith Ross
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Hello Leith Ross

    Thank you for the new worksheet but i had some other things to do

    Hello,
    it's me again

    Also what i was looking for, i think this would be impossible, but rather let me give it a shot

    there's a a "paid to" with "type of payment" (for example, credit card, check etc..)column, standard "number" column (lists all the bills like 1, 2, 3, in numerical order), and finally there's a "amount" column. is it possible to have them organized when you make the selection.
    each month has four columns so i think you would have to edit the code

    and on the window that pops up, is there anyway you can add a check box selection, like an option that says "okay for that particular month (s), organize it by "amount" (ascending) , it is already in alphabetical order, when the user inputs the data in the column it is alphabetical order...

    so each month has four columns

    jan - A B C D

    feb - E F G H


    and so on.....

    Thanks so muchh............

    Also i want to have an option to organize it by the type of payment also
    so a total of two options....
    the user can only select one of them....

    and when you select an option, the corresponding "paid to" individual or "amount" should be the same meaning
    if the user selects to organize by amount, the "paid to" should correspond to the amount, it should be like grouped together so that when organizing it, it really doesnt' mess up the data....

  31. #31
    Registered User
    Join Date
    06-14-2008
    Posts
    26
    Hello Everyone....
    I'm very new to VBA

    can anyone help with the above problem?
    i have attached the sample(book 1) and the file that is the most closes to what i like (example 2 rev 2)

    Thanks Again

  32. #32
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jenniferkognan,

    I am closing this post because your requests are straying to far from the original topic. It also appears to me that you need help creating more than a simple interface for this project. You should stop and think about what you need and create a list. Submit this list under a new thread. Both Roy and I have to attend to other members' requests for help. Perhaps you should place a request for help in the Commercial Services forum.

    Sincerely,
    Leith Ross

Closed 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