VBA how to insert a row after a specific row number only

[cypher1985]

Due to a few reasons regarding cell security and trying to consider user requests. I've been forced down a road to make a button that allows me to insert a user selected number of rows, below a user selected row number. See below;

Sub InsertRow() ' ' ButtonInsertRow ' Inserts a new row ' ' Keyboard Shortcut: Ctrl+Shift+I ' ActiveSheet.Unprotect Password:="123dreid" Dim iRow As Long Dim iCount As Long Dim i As Long iCount = InputBox(Prompt:="How many rows you want to add?") iRow = InputBox _ (Prompt:="After which row you want to add new rows? (Enter the row number below. Must be higher than 27)") For i = 1 To iCount Rows(iRow).EntireRow.Insert Next i ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingRows:=True, AllowDeletingRows:=True, Password:="123dreid" End Sub

This is tied to a button on the sheet and works exactly as I need. I just need to add one more condition. After the 2nd prompt, I only want users to be able to type a number higher than 27 for the row number, as that is where the user input data starts. If its lower than 27. Would be nice to have a prompt warning them to type a higher number next time, but this isn't essential. Then "End This Sub" if lower than 27, or carry on with the code if higher than 27. Problem is I'm not sure where to place the condition to do that or even the code required.

Hitting a wall with this and its taken a few days just to get the above working. So any help from the gurus would be greatly appreciated

 

[HaHoBe]

Hi cypher1985,

maybe

Sub InsertRow()
'
' ButtonInsertRow
' Inserts a new row
'
' Keyboard Shortcut: Ctrl+Shift+I
'

  Dim lRow As Long
  Dim iCount As Long
  
  iCount = Application.InputBox(Prompt:="How many rows you want to add?", Type:=1)
  If iCount = 0 Then Exit Sub

again:
  lRow = Application.InputBox _
      (Prompt:="After which row you want to add new rows? (Enter the row number below. Must be higher than 27)", Type:=1)
  If StrPtr(lRow) = 0 Then
    Exit Sub
  ElseIf Len(lRow) = 0 Then
    MsgBox "Please enter a row number higher than 27 "
    GoTo again
  Else
    If lRow < 28 Then
      MsgBox "Entered value was " & lRow & "; requested is at least 28. Aborting here", , "Exit procedure"
      Exit Sub
    End If
  End If

  ActiveSheet.Unprotect Password:="123dreid"
  Rows(lRow).Resize(iCount).EntireRow.Insert
  ActiveSheet.Protect DrawingObjects:=True, _
                      Contents:=True, _
                      Scenarios:=True, _
                      AllowInsertingRows:=True, _
                      AllowDeletingRows:=True, _
                      Password:="123dreid"
End Sub

Ciao,
Holger

 

[breynolds0431]

Another option would be to use a custom userform. These have much more control over the data entry including limiting only to numbers. Take a look at the userform I put together specific to your need at Insert Row User Form.xlsm.

Note that the userform must be shown using .Show. This is in the dropbox file's Sheet1 module code. You can copy the .show code to your button's code. You can copy the userform to your workbook by dragging it to the forms section of your workbook.

 

[HaHoBe]

Hi breynolds0431,

a lot of work invested. Please allow me some remarks which arose testing the workbook/UserForm.

Developing a form in VBA has different concepts for each of the single persons. And this goes for calling up the form via button as well.

How do you know that OP uses an ActiveX-Control? If the code is transferred to a Forms-Control button it will work ok but that button must not necessarily be located in a sheet-module.

A matter of personal taste but in general I use a commandbutton for Cancel and disable the closing of the UF via X in the top right corner (QueryClose).

Button cbProcess is enabled all the time. There is the logic to check both textboxes for content and then leave the procedure. I would expect the user to be brought to the (first) empty textbox instead. And I would rather disable the button on startup as well as at the end of the process and include a check for both textboxes to be filled in order to enable the button.

On start of the userform the cursor is set to tbNewRows but if the mouse hovers over tbRowStart the ControlTipText „Must be after Row 27“ is displayed. I find that irritating.

In cbProcess you allow the rows to be inserted to be zero – why would you start the procedure if there is nothing to do (for me that should be part of the routine for tbNewRows)?

You should either delete the Debug.Print lines or make good use of a modulewide boolean to switch between Develeoper-Mode and Live-Mode.

Holger

 

[breynolds0431]

Hi breynolds0431,

a lot of work invested. Please allow me some remarks which arose testing the workbook/UserForm.

Developing a form in VBA has different concepts for each of the single persons. And this goes for calling up the form via button as well.

How do you know that OP uses an ActiveX-Control? If the code is transferred to a Forms-Control button it will work ok but that button must not necessarily be located in a sheet-module.

A matter of personal taste but in general I use a commandbutton for Cancel and disable the closing of the UF via X in the top right corner (QueryClose).

Button cbProcess is enabled all the time. There is the logic to check both textboxes for content and then leave the procedure. I would expect the user to be brought to the (first) empty textbox instead. And I would rather disable the button on startup as well as at the end of the process and include a check for both textboxes to be filled in order to enable the button.

On start of the userform the cursor is set to tbNewRows but if the mouse hovers over tbRowStart the ControlTipText „Must be after Row 27“ is displayed. I find that irritating.

In cbProcess you allow the rows to be inserted to be zero – why would you start the procedure if there is nothing to do (for me that should be part of the routine for tbNewRows)?

You should either delete the Debug.Print lines or make good use of a modulewide boolean to switch between Develeoper-Mode and Live-Mode.

Holger

Cool

 

[cypher1985]

Hi cypher1985,

maybe

Sub InsertRow()
'
' ButtonInsertRow
' Inserts a new row
'
' Keyboard Shortcut: Ctrl+Shift+I
'

  Dim lRow As Long
  Dim iCount As Long
 
  iCount = Application.InputBox(Prompt:="How many rows you want to add?", Type:=1)
  If iCount = 0 Then Exit Sub

again:
  lRow = Application.InputBox _
      (Prompt:="After which row you want to add new rows? (Enter the row number below. Must be higher than 27)", Type:=1)
  If StrPtr(lRow) = 0 Then
    Exit Sub
  ElseIf Len(lRow) = 0 Then
    MsgBox "Please enter a row number higher than 27 "
    GoTo again
  Else
    If lRow < 28 Then
      MsgBox "Entered value was " & lRow & "; requested is at least 28. Aborting here", , "Exit procedure"
      Exit Sub
    End If
  End If

  ActiveSheet.Unprotect Password:="123dreid"
  Rows(lRow).Resize(iCount).EntireRow.Insert
  ActiveSheet.Protect DrawingObjects:=True, _
                      Contents:=True, _
                      Scenarios:=True, _
                      AllowInsertingRows:=True, _
                      AllowDeletingRows:=True, _
                      Password:="123dreid"
End Sub

Ciao,
Holger

Holger, if you ever come to Birmingham in the UK. I owe you at least 50 alcoholic drinks of your choice. That worked perfectly. Thank you!

 

[cypher1985]

Hi breynolds0431,

a lot of work invested. Please allow me some remarks which arose testing the workbook/UserForm.

Developing a form in VBA has different concepts for each of the single persons. And this goes for calling up the form via button as well.

How do you know that OP uses an ActiveX-Control? If the code is transferred to a Forms-Control button it will work ok but that button must not necessarily be located in a sheet-module.

A matter of personal taste but in general I use a commandbutton for Cancel and disable the closing of the UF via X in the top right corner (QueryClose).

Button cbProcess is enabled all the time. There is the logic to check both textboxes for content and then leave the procedure. I would expect the user to be brought to the (first) empty textbox instead. And I would rather disable the button on startup as well as at the end of the process and include a check for both textboxes to be filled in order to enable the button.

On start of the userform the cursor is set to tbNewRows but if the mouse hovers over tbRowStart the ControlTipText „Must be after Row 27“ is displayed. I find that irritating.

In cbProcess you allow the rows to be inserted to be zero – why would you start the procedure if there is nothing to do (for me that should be part of the routine for tbNewRows)?

You should either delete the Debug.Print lines or make good use of a modulewide boolean to switch between Develeoper-Mode and Live-Mode.

Holger

in regards to the active X control element. Most users here / in the future would look at me with a blankface if i asked them if they activated active X. So to address that, Every tab has an image of the active X pop up with instructions on that image saying "enable" if asked. Theres also a link to a company PDF outlining how to activate it through the settings should someone miss the enable option. This image and the instructions within it have been set to not appear on prints so all should be good in that regard. Hoping that would be enough to address it.