+ Reply to Thread
Results 1 to 14 of 14

Command Button code

  1. #1
    Registered User
    Join Date
    04-29-2008
    Posts
    5

    Command Button code

    I have a 2 worksheets. One "Projects in Process"and the other "Project Completed". when the users fill in the information in cells A4-F4 (line 4 is the starting line and it could go down as far as line 23 or a23 - f23) In Projects in Process they press a "completed Project" command button and the data in a4-f4 copies over to worksheet "Projects completed". In the next available row (starting at row 4). Can somone help? I just don't understand how to do this. Please help me

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    this can go in a standard module as i have done or put the body of it in a commandbutton click event:
    Please Login or Register  to view this content.
    change the sheets to suit!
    Not all forums are the same - seek and you shall find

  3. #3
    Registered User
    Join Date
    01-02-2007
    Posts
    39
    Hi lakle.
    Just to give me an idea of where you're coming from, how familiar are you with Excel Macro's? The task you're talking about will require three steps:

    1. Writing VBA code to accomplish the task of moving the row of data
    2. Creating a button in the first worksheet
    3. Assigning a macro to the button

    Is there one of these steps in particular you need help with, or all 3? Thanks
    Bradley D. Clouser
    www.ExcelPro.org

  4. #4
    Registered User
    Join Date
    04-29-2008
    Posts
    5
    Hi Brad - 1 and 3 are where I need help. badly...

  5. #5
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Lakle if you have created the button then in design mode double click the button and paste the body of my code in but change the sheet names as required.

  6. #6
    Registered User
    Join Date
    01-02-2007
    Posts
    39
    lakle,

    Simon has done all the work for you. If you need help putting it together in your workbook, let us know. Here's the rundown. It sounds like you already have the button on your worksheet, but no code assigned to it.

    1. Press Alt+F11 to enter design mode
    2. Click Insert -> Module
    3. Paste Simon's code into the new module (change "Sheet1" to the name of your Projects in Progress sheet, and "Sheet2" to the name of your completed projects sheet)
    4. Close design mode
    5. Right click the button on your worksheet, choose "assign macro", and assign the "data_copy" macro.

    Also, you said you wanted to copy the data, but it sounds like you actually want to cut the data to the new sheet (so it's no longer in the first sheet after you move it). If this is the case, just change ".Copy" to ".Cut" in Simon's code.

  7. #7
    Registered User
    Join Date
    04-29-2008
    Posts
    5
    I pasted the code and this is I got "expected end sub"

    Private Sub CommandButton1_Click()
    Sub copy_data()
    Dim Rng As Range, dRng As Range
    Set Rng = Sheets("Projects in Process").Range("A4:F" & Range("F" & Rows.Count).End(xlUp).Row)
    Set dRng = Sheets("Project Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Rng.Copy Destination:=dRng
    End Sub

  8. #8
    Registered User
    Join Date
    01-02-2007
    Posts
    39
    Get rid of the first line, Private Sub blah blah blah...

  9. #9
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Nooooo! get rid of Sub Copy_Data the private blah is your command button ! lol

  10. #10
    Registered User
    Join Date
    01-02-2007
    Posts
    39
    Simon, I didn't realize you could create a click event for a button sitting on a worksheet, just buttons on userforms. I always wrote the macro and then assigned it to the button. How did you do that? I can only edit the button's properties on the worksheet, and don't see it in the VBA editor. Oh yeah, I got the button on the sheet by using the Forms toolbar, if that makes a difference.

  11. #11
    Registered User
    Join Date
    04-29-2008
    Posts
    5

    ?

    it looks like it's duplicating the line and putting it into lines 29 and 30 of the Project Completed.

  12. #12
    Registered User
    Join Date
    04-29-2008
    Posts
    5

    Its kind of working.

    The only thing I see is that if I skip a few lines on the 1st sheet and hit the button - it populates the second sheet's cooresponding line. In other words, a user may have line 10 completed (a project completed) they hit the button and rather than going to the next available line...like line 4 in sheet #2 it goes to line 10 in sheet #2.

  13. #13
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Yeah Brad, the forms buttons don't have the same capability, really its best to use the Control Toolbox, create one on your worksheet then double click it and hey presto you're in the worksheet code module on the Commandbutton1_Click.

  14. #14
    Registered User
    Join Date
    01-02-2007
    Posts
    39
    Thanks for the tip!

+ 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