+ Reply to Thread
Results 1 to 7 of 7

Command Button - Insert Lines

Hybrid View

  1. #1
    Registered User
    Join Date
    01-27-2007
    Posts
    18

    Command Button - Insert Lines

    I am attempting to write a command button to insert a line between two lines that already have formulas in them. I would like to insert the row and have the forumals shift down, but not be the same as above. Sort of something like below.


    1 003 1st Floor CHWS&R 1st Floor 500.00 287.50 212.50
    2 004 VAV Connections 1st Floor 25.00 125.00 -100.00

    I want the line to insert between A and B, but I want B to move down to C and become 005, and I want the new line in B to be 004. I want that consistent for the codes in 3, 4, and 5. Thanks for your help.

    Thanks,

    TJB

  2. #2
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    I would need to see the formulas (not the results) in order to give a precise response.

    As it is, all I can tell you is ... this will insert a row based on row number "n":
    Rows(n).Insert Shift:=xlDown
    The formulas will shift automatically if you have used relative row references throughout. If not, you will need to re-write the formulas for all rows below "n".

  3. #3
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    See if this is what you had in mind:
    Private Sub CommandButton1_Click()
    Dim currRow As Long
        ActiveCell.Activate
        currRow = ActiveCell.Row
        uChoose = MsgBox("Insert new row at " & currRow & "?", vbYesNo, "Please confirm ...")
        If uChoose = vbNo Then Exit Sub
        'insert new row
        Me.Rows(currRow).Insert
        'put formula in column A
        With Me.Range("A" & currRow)
            .FormulaR1C1 = "= R[-1]C+1"
            .NumberFormat = "000"
        End With
        'adjust formula for the old column
        Me.Range("A" & currRow + 1).FormulaR1C1 = "= R[-1]C+1"
    End Sub

  4. #4
    Registered User
    Join Date
    01-27-2007
    Posts
    18

    Little More

    The button works (Thanks!), but the codes for E and F do not drop down. Also, I would like to allow undo. What code to I have to add so all formulas in the row that we are adding move down as well.

    The A column has the formula:

    A2 = A1+1

    B column is user input

    C Column is user input

    D Column is user input

    E Column has the formula:

    E2 = 'Worsheet 2'!D482

    F Column has the formula:

    F2 = D2-E2

    Thanks,

    TJB

  5. #5
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    E Column has the formula: E2 = 'Worsheet 2'!D482
    F Column has the formula: F2 = D2-E2
    Let me be sure I understand ... for the NEW cell F2, you just want that it comes "pre-loaded" with the formula "=D2-E2". Is that correct?

    Now, for E2 ... how do you know what cell it should apply to on another worksheet? (I'm sure you know, but I don't!)

    Tell you what I can do ... I can put formulas there and then you can tweak if I don't get it right.

    Private Sub CommandButton1_Click()
    Dim currRow As Long
        ActiveCell.Activate
        currRow = ActiveCell.Row
        uChoose = MsgBox("Insert new row at " & currRow & "?", vbYesNo, "Please confirm ...")
        If uChoose = vbNo Then Exit Sub
        'insert new row
        Me.Rows(currRow).Insert
        'put formula in column A
        With Me.Range("A" & currRow)
            .FormulaR1C1 = "= R[-1]C+1"
            .NumberFormat = "000"
        End With
        'adjust formula for the old column A
        Me.Range("A" & currRow + 1).FormulaR1C1 = "= R[-1]C+1"
    
        'formula for column E
        With Me.Range("E" & currRow)
            .Formula = "= 'Worksheet 2'!D482"
        End With
        
        'formula for column F
        With Me.Range("F" & currRow)
            .FormulaR1C1 = "= RC[-2]-RC[-1]"
        End With
        
    End Sub

  6. #6
    Registered User
    Join Date
    01-27-2007
    Posts
    18

    Almost there

    Alright, as you suspected, the 'E' column is still a problem. The codes that show up in the "A" column of my spreadsheet are 1, 2, 3, 4, 5, 6, 7.......etc. In the other worksheet, the user uses these codes to log his time like so.



    A B C D
    Date Description Description Code Hours

    1/29/2007 Logging Equipment 003 16

    It then sums the hours by code at the bottom of the page, and the sum is reverted back to the front page in the "E" column. Does that make more sense? Thanks for all your help. It is greatly appreciated. I am working on it as well. If I come up with the code prior to you, I will let you know.

    Thanks,

    TJB

+ 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