Posts by Eoliver777

Quote from royUK

Re: Including a ComboBox when inserting a new table row with Macro

Why do you need a macro to copy down formulas. This feature is available in an Excel Table

Overview of Excel Tables

Also, why use a ComboBox when data Validation Lists are more convenient and less problematic. Also, you don't need VBA hen using this feature

If you don't want a Data Validation there is another built-in feature that allows the user to pick from a list of previous entries. Just right click on the cell and choose pick from list

Display More

I needed a macro because the worksheet is protected and will be used by others as a form that may require more lines to be added as needed.

I need the combobox because a validation table may not always have the latest information and I would like them to be able to write in the correct information if it's not there.

Good news is I have been able to work through the problem, including having to unlock the worksheet and locking it back up when row is inserted. Other complications that arose were having to clear out the copied data from the row it was copied from.

If anyone is interested in the details of how this was done, let me know and I will post.

Hi all,

I am looking for help with a macro that inserts a new row to a table that includes a combobox in one of the columns. I have created a macro with a CommandButton that carries down formulas and formats from the last row in the table but I do not know how to include the ComboBox. I am using Excel 2013.
The table is found in columns A thru I in rows 20 thru 27 with the ComboBox in column H

the following is the code I have so far.

Private Sub CommandButton1_Click()    Dim Lr As Integer, Fr As Integer
     
    Fr = Columns("A").Find(What:="Item", After:=Range("A20")).Row
    Lr = Range("A" & Fr).End(xlDown).Row
     
    Rows(Lr + 1).Insert Shift:=xlDown
    Cells(Lr + 1, "A") = Cells(Lr, "A") + 1
    Rows(Lr).Copy
    Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Cells(Lr + 1, "B").Select
End Sub

Hi all,

I am looking for help with a macro that inserts a new row to a table that includes a combobox in one of the columns. I have created a macro with a CommandButton that carries down formulas and formats from the last row in the table but I do not know how to include the ComboBox. I am using Excel 2013.
The table is found in columns A thru I in rows 20 thru 27 with the ComboBox in column H

the following is the code I have so far.

Private Sub CommandButton1_Click()    Dim Lr As Integer, Fr As Integer
     
    Fr = Columns("A").Find(What:="Item", After:=Range("A20")).Row
    Lr = Range("A" & Fr).End(xlDown).Row
     
    Rows(Lr + 1).Insert Shift:=xlDown
    Cells(Lr + 1, "A") = Cells(Lr, "A") + 1
    Rows(Lr).Copy
    Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Cells(Lr + 1, "B").Select
End Sub