+ Reply to Thread
Results 1 to 12 of 12

Userforms & Checkboxes & populating rows

  1. #1
    Registered User
    Join Date
    05-31-2007
    Posts
    11

    Userforms & Checkboxes & populating rows

    I sure hope this hasn't been answered elsewhere; I already feel like a dork. :-)

    In my spreadsheet, the user clicks on a button which shows a userform. On the userform, there is a list of checkboxes and several radio buttons from which to choose, based on the information needed to fill in a row of the spreadsheet. (Each row is snail mail addresses followed by cells that will contain either an "x" or are left blank. The "x"'ed cells cause formulas to generate text strings that are copied & pasted into another application.)

    I can't seem to locate an example of how to get checked boxes to populate a row. Any ideas?

    I also need to be able to copy horizontal info into vertical lines to print a letter, as well as use those "x"'ed boxes to fill in the letter.

    Thanks in advance!

    gwendolyn

  2. #2
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    When do you want the checked boxes to populate the cells?

    If you want the cells to be populated at the time they are checked they you can use the checkbox's 'Click' or 'Change' event handler. Enter your code to populate the appropriate cells.

    If you want the cells to be populated when the userform is closed (i.e. when an 'OK' button is pressed on the userform) then in the 'OK' button's 'Click' event handler have a loop that checks each checkbox or optionbutton (radio button) on your userform value to see if it is true. If true then populate the appropriate cell on your spreadsheet, if false skip to check the next checkbox / optionbutton.

    As for copying horizontal information to vertical that also depends on how your information is arranged.
    Is it all in one cell or one peice of information in each cell across the page?

    Either way you can loop through the information checking for delimiters that can be used to identify where the line breaks should be inserted.

    If you can give some more specific information about the requirements of your checkboxes populating the sheet or the the arrangement of your horizontal information then more specific help can be provided.

    HTH

  3. #3
    Registered User
    Join Date
    05-31-2007
    Posts
    11
    Hi, bhofsetz! Thanks for your help!

    I want the cells to be populated once the user clicks on the "OK" button.

    I dl'ed the zip file from this thread: http://www.excelforum.com/showthread.php?t=601906 and hope that it will give me a clue as to the code involved.

    Is there anywhere on the net that lists the VBA code and the syntax? I already have two books on programming Excel using Macros, but there isn't anything in there about making userforms do what I need to be done. I don't have any experience with this programming language.

    When the user clicks the "OK" button, the cells that need to be populated change for each instance. In other words, the user will be inputing address information across a row, then clicking a button that will bring up the userform that asks for reasons why we are sending a customer a letter. Each row in the spreadsheet contains differing customer information; it isn't cleared or deleted afterwards.

    After the userform fills in "x"'s in a series of columns, the user will then click on another commandbutton macro which is supposed to copy the customer's information and place it in an absolute cell reference. It will also copy the "x"'s in the appropriate columns for that customer and paste them into cells (this time vertically) on the letter (also absolute references). Then it will print the active sheet. It will also need to clear out the customer info & checked boxes once it has been printed, to make it ready for the next customer.

    When the user inputs the customer information, each piece gets its own cell: 1st name, last name, addy, city, st, zip. It would be nice if the related pieces could be concatenated into a single cell on the letter worksheet, but not necessary.

    Again, thank you for your help.

    gwendolyn

  4. #4
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    Here is a link to an overview article on MSDN about userforms and
    manipulating them. Perhaps this would be a good place to start.

    http://support.microsoft.com/kb/829070

    Also have you looked at the Help when in the VBA editor. A search
    of the Help should give you some help with syntax etc.

    To help get you started.

    Each command button on the userform has event handlers associated with it.
    Your 'OK' button you can have it a click event handler which will trigger whatever code you place within the following sub.

    This code should be in the module for your userform which contains a command button named CommandButton1
    Please Login or Register  to view this content.
    I guess I'm also still not clear about the layout of your worksheet and the flow
    of data between the userform and sheet.
    How many userforms does your project contain? Does the user first enter
    customer information, Name, Address, Phone number, etc. into the worksheet
    or into a userform?
    Are columns on the worksheet being filled in with 'X's or are you using checkboxes on one of the userforms?

    An overview of the flow of data input to final output would be useful in giving
    better direction for your project.

    HTH
    Last edited by bhofsetz; 06-05-2007 at 05:51 PM.

  5. #5
    Registered User
    Join Date
    05-31-2007
    Posts
    11
    Quote Originally Posted by bhofsetz
    Here is a link to an overview article on MSDN about userforms and
    manipulating them. Perhaps this would be a good place to start.
    Cool. Will check it out.


    Each command button on the userform has event handlers associated with it.
    Your 'OK' button you can have it a click event handler which will trigger whatever code you place within the following sub.
    So far, so good. For the spreadsheet currently in use, the user is supposed to enter the name & other info (which has it's own buttons & macros that I've almost figured out) first, then click on a button that brings up the userform (which I've been able to figure out how to do that). It's when the user checks a box, or selects a radio button, then clicks on the "OK" button do I come up against a wall. If the user checks the first box, then pressed "OK", I want an "x" to appear in, say, column AY, but the row # would be different in each instance of clicking the "OK" button. If the user checks the first and second boxes, then a want an "x" to appear in columns AY and AZ on the active row. And so on.

    If the user checks the first box and the first radio option, I want an "x" to appear in column AY, and a text string associated with that radio option to appear in column BF, again, the row # would be different in each instance of clicking the "OK" button.

    There are formulas on each row that concatenate the information for that row and a command button will copy the value of the formula and paste it into another, empty, cell on that same row. Then it will copy that result and paste it to an external application.


    I guess I'm also still not clear about the layout of your worksheet and the flow
    of data between the userform and sheet.
    How many userforms does your project contain? Does the user first enter
    customer information, Name, Address, Phone number, etc. into the worksheet
    or into a userform?
    Are columns on the worksheet being filled in with 'X's or are you using checkboxes on one of the userforms?

    An overview of the flow of data input to final output would be useful in giving
    better direction for your project.

    HTH
    So far, I only have the one userform. With 15 checkboxes and 13 radio options, the last of which is where the user can supply their own text.

    The user enters name & other info from one of two choices: manually; or if the information is available from an external application, it is copied from that application and a macro pastes it, formats it and concatenates it (on Sheet 2), making it available for copying onto the main sheet (Sheet 1). Sheet 1 is where the user would call the userform and the print letter macro (the letter is on Sheet 3).

    Hope that clears it up a little.

    gwendolyn

  6. #6
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    If each checkbox is associated with a specific column then you can easily
    add the 'X' to the appropriate column. The problem is figuring out which row
    your 'X's need to be added to. If your user is always adding the new
    customer information to the last row in your input sheet then this is easy by
    simply assigning a variable like myRow to the last row in the sheet.

    myRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    then in the 'OK' button's click event

    Please Login or Register  to view this content.
    If the new data is not in the last row of the activesheet then you will have
    to have some other way to identify the 'myRow' variable (possibly by the
    activecell.row)

    HTH

  7. #7
    Registered User
    Join Date
    05-31-2007
    Posts
    11

    VBA "Help" is no help

    Ah, yes... now I remember why I haven't made extensive use of VBA help...

    I type in a query, it returns a whole lot of links which look as though they are
    answers from heaven that will solve all my problems, but when I click on the
    link... nothing happens. Just a click. :sigh:


    Quote Originally Posted by bhofsetz

    myRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    What is the (xlUP) do? I am attempting to compile the code and run into an
    error highlighting that part. I tried switching from an L to a One, but it made
    no difference.

    If the new data is not in the last row of the activesheet then you will have
    to have some other way to identify the 'myRow' variable (possibly by the
    activecell.row)

    HTH
    Typically it is, but I'd like to *make sure* that the information is pasted into
    the next empty row, regardless of where the active cell is, and not to paste
    over existing information.

    Thanks for your help. I'm making significant progress with this spreadsheet.
    While I have enjoyed the challenge, I just want it to *work* so the work it's
    supposed to be doing will get *done.* Know what I mean??

    gwendolyn

  8. #8
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    what is the error you are getting on this line?

    Please Login or Register  to view this content.
    It could be erroring if you haven't declared myRows

    this line will find the last row which contains data in column A. The End(xlUP) tells it to start at the 'end' (bottom) of the column and move 'UP' until if finds a populated cell.

  9. #9
    Registered User
    Join Date
    05-31-2007
    Posts
    11

    I am the error!

    I get "Compile Error. Invalid outside procedure." Which is probably because I haven't declared myRows (but I thought that's what that line of code *did*.)

    I have that line of code under general declarations. Is that right?

    Then I have:
    Please Login or Register  to view this content.
    after that. Is that right?



    My books on macro programming do not cover this... they're written for people who have kept up with programming since 1995....



    gwendolyn <--

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    There's a database form example here that might help
    Hope that helps.

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

    Free DataBaseForm example

  11. #11
    Registered User
    Join Date
    05-31-2007
    Posts
    11
    Quote Originally Posted by royUK
    There's a database form example here that might help
    Thanks, Roy.

    I dl'ed a couple of the examples and rearranged my code. So it now looks something like this:

    Please Login or Register  to view this content.
    What's supposed to be happening here is that the user clicks on CommandButton5, which brings up UserForm1. When the user clicks on the Enter button
    (CommandButton2) on UserForm1, the above code is supposed to populate the active row with an "x", depending on which box has been checked.

    The code for CommandButton5 is:
    Please Login or Register  to view this content.
    but now I'm getting an error with that line of code: Run time error 91: Object
    variable or With block variable not set.

    So I took out the "With Me" and "End With" statements and still got the same error message with the same line of code highlighted.

    I thought I had to specify that when the user clicked on a button to activate
    a userform, that that line of code was what you used.

    What have I done wrong?

    BTW, I really appreciate everyone's help.


    gwendolyn

  12. #12
    Registered User
    Join Date
    05-31-2007
    Posts
    11

    I give up

    I give up. I can't do it. I can't even figure out how to change the code to put x's in the active row without getting an error.

    And I'm tired of trying to figure it out while getting paid minimum wage.


    gwendolyn

+ 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