Need my Macro to insert rows below where user selects (now inserts above)

[PitBoo]

This macro/code works perfect, I need it to continue to do exactly what it is doing except to insert the new row(s) below where the user clicks.

I could have a sheet where there is data like this:

A Patient 1
B Test1
C Test 2
D Test 3
E Test 4
F Patient 2
G Test 1
H Test 2

And I need to add a Test 5, 6, 7 to Patient 1 after test 4.
Right now it would add them above test 4 if user clicked cell E4 for the insertion point.

And I cant select cell F1 because the patient line is always going to be formatted a different color and font and stuff and I can't have it pull that formatting for test lines.


Here is the code

[Maudibe]

PitBoo,

Here is a different approach:

To add a row(s), select a cell and press ctrl+z. From the combobox that appears adjacent, select the number of rows you wish to enter.

To add cell(s) vertically, select a cell and press ctrl+Shift+Z. From the combobox that appears adjacent, select the number of rows you wish to enter.

Here are the 3 simple steps to follow:

Place 2 active x comboboxes on your worksheet named Combobox1 and Combobox2. Set both of their visible properties to false in the properties window. Their size and placement is totally irrelevant.

Place the following code in a standard module:

Public Sub InsertRow()
With ActiveSheet.ComboBox1
    .Clear
    For I = 1 To 10 'CHANGE TO THE MAX NUMBER OF POSSIBLE ADDED ROWS
        .AddItem I
    Next I
    .Height = ActiveCell.Height
    .Width = 50
    .Left = ActiveCell.Offset(0, 1).Left
    .Top = ActiveCell.Top
    .Visible = True
End With
End Sub

Public Sub InsertCells()
With ActiveSheet.ComboBox2
    .Clear
    For I = 0 To 10 'CHANGE TO THE MAX NUMBER OF POSSIBLE ADDED CELLS
        .AddItem I
    Next I
    .Height = ActiveCell.Height
    .Width = 50
    .Left = ActiveCell.Offset(0, 1).Left
    .Top = ActiveCell.Top
    .Visible = True
End With
End Sub

Place the following code in the sheet module:

Private Sub ComboBox1_Change()
With ComboBox1
    For I = 1 To Val(.Value)
        ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
    Next I
    .Visible = False
    End With
End Sub

Private Sub ComboBox2_Change()
With ComboBox2
    For I = 0 To Val(.Value)
        ActiveCell.Offset(1, 0).Insert Shift:=xlDown
    Next I
    .Visible = False
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1.Visible = False
ComboBox2.Visible = False
End Sub

In the macro window (Alt-F8), assign the macro "InsertRow" the shortcut key Ctrl+z
In the macro window (Alt-F8), assign the macro "InsertCells" the shortcut key Ctrl+Shift+Z

To use the code, select the cell where the rows or cells will be placed under. press the appropriate shortcut, select the number of rows/cells you want to add. the combobox disappears and the cells/rows are added. if you press the shortcut then change your mind and don't want to add any cells/rows, simply click another cell.

HTH,
Maud

PitBoo1.png
Add Rows Or Cells VBA_Rev1.xlsm

[bakerman2]

@ maudibe

Instead of AddItem you could also use

ComboBox1.List = [row(1:10)]

to fill a combobox with numbers (from 1 to 10)

[Maudibe]

Thanks! Or ComboBox1.List = Range("A1:A10")

Maud

[bakerman2]

Very important.

ComboBox1.List = Range("A1:A10").Value

[Maudibe]

correct as usual!

[PitBoo]

OK I like your idea, it seems smoother. However I am a mess trying to figure out how to fit it into this worksheet.
I'm attaching the worksheet so you can see what I have done so far (with others help on here)

Here are the current functions:

• The users can add blank rows when needed by using the insert rows button. They need to be able to do this when setting up a job and also if they need more lines in between jobs.
• The users can format a row as a "Header" row with the Format Job Row command. This row quickly identifies the start of a new job by turning the row blue and changes text formatting.
• Once a date is entered into the "Date Shipped/Complete cell then the entire row is moved off of the main sheet to the "complete" sheet.

There are also some conditional formats going on and stuff like that, should not matter in all of this.

I also put a code in to force ALL pastes to be Paste-Values only so users can copy/paste without screwing up the conditional formatting.


The Insert Row and Format Job Row macros contain code that unlocks the sheet, performs the action, then locks it back down so users to accidently destroy the sheet, but also allows them to insert and format by using the macro buttons.

Everything seems to be working OK, except the Insert Job Row however is glitchy. Sometimes it throws the rows in weird spots, and after you do it once (say if you choose to insert 2 rows), then every time after it doubles your requested row count. If I choose 4 next time it will put in 8. And sometimes it splits half above and half below the selection point. Its really buggy. Yours seems better but I an very basic at this and am struggling on where to put everything and still allow the unlock/lock functions to work. My sheet kind of works, if its just easier to tighten my code up I can try that as well.


Thanks for the help.

[PitBoo]

Thanks for the help, I am going to try to attack this another way. I'm going to try to fix the multiple row addition issue and use it as is.

I will just close this one as fixed.

I am trying to learn vba, just not advanced enough to tackle this