Insert rows - Limiting to a specific Range

[MaxChef]

I'm using code from David Ritchie's website.
This works fine

Sub InsertNewRow()

Dim vRows As Long

' row selection based on active cell --
Application.EnableEvents = False
ActiveCell.EntireRow.Select
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
.......

I would like to limit a user's ability to insert rows to within a certain range of rows.
If they go out of range and try to insert a row, I'd like a Msgbx to inform them of that.

I'm confused as to how to code this.

I'm guessing it's a conditional statement incorporating an Active Cell target.

If ActiveCell is within range Then
Insert Row
Else Msgbx " Please place cursor within Defined Table"

Thanks

 

[RolfJ]

You could try something like this:

const ROW_MIN = 5
const ROW_MAX = 10
 
If ActiveCell.Row >= ROW_MIN and ActiveCell.Row <= ROW_MAX Then
   Insert Row 
Else 
   Msgbx " Please place cursor within Defined Table"
End If

Hope this helped.

 

[MaxChef]

Sorry for the delay in posting back to you.

I was in a hurry so, I came up with using the AcitveCell in the code as follows:

Sub InsertNewRow()
Dim vRows As Long
 
' row selection based on active cell --
Application.EnableEvents = False
 
' make sure cursor placement is within range
If Intersect(ActiveCell, Range("InsertRange")) Is Nothing Then
MsgBox "Place cursor next to the row you wish to insert to."
 
Else
 
' ask how many row needed?
ActiveCell.EntireRow.Select
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
 
' Insert rows
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), _
xlFillDefault
On Error Resume Next
Application.EnableEvents = True
 
End If
 
End Sub

I defined a named range which would expand with any inserted rows but only if the cursor was placed within that range.

Thanks for the suggestion.