Do Not Delete lines

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
113
Office Version
  1. 2007
Thank you for looking. I'm having problems with to top part of this code. What I want is for the user to be unable to delete rows 1 through 10. Sometimes this code will work, sometimes not. What am I doing wrong?

VBA Code:
Private Sub CommandButton1_Click()
        If ActiveCell.Rows >= 10 Then
        MsgBox "You cannot insert a new line here"
    Exit Sub
    Else
    End If
    ActiveSheet.Unprotect
    Dim answer As Integer           'Delete Lines
        answer = MsgBox("Have You Selected The Row(s) To Delete?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete Row")
            If answer = vbYes Then
                Selection.EntireRow.Delete
    Else
            If answer = vbNo Then
                MsgBox "Please Select Row To Delete.", , "Delete Row"
        End If
        End If
     Application.ScreenUpdating = False
        lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
        Range("K9").Formula = "=IF($K8="""","""",IF(AND($H9="""",$J9=""""),"""",$K8+$J9-$H9))"
        Range("K9").AutoFill Range("K9:K" & lr)
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,166
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Shouldn't this line be
VBA Code:
 If ActiveCell.Rows >= 10

Rich (BB code):
 If ActiveCell.Rows <= 10
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,166
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, try
VBA Code:
Private Sub CommandButton1_Click()
    ActiveSheet.Unprotect
Dim answer As String        'Delete Lines
    answer = MsgBox("Have You Selected The Row(s) To Delete?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete Row")
        If answer = vbYes Then
        If Selection.Row <= 10 Then
        MsgBox "You cannot insert a new line here"
        Exit Sub
        End If
        Selection.EntireRow.Delete
Else
            MsgBox "Please Select Row To Delete.", , "Delete Row"
    End If
 Application.ScreenUpdating = False
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Range("K9").Formula = "=IF($K8="""","""",IF(AND($H9="""",$J9=""""),"""",$K8+$J9-$H9))"
    Range("K9").AutoFill Range("K9:K" & lr)
End Sub
 

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
113
Office Version
  1. 2007
Ok, try
VBA Code:
Private Sub CommandButton1_Click()
    ActiveSheet.Unprotect
Dim answer As String        'Delete Lines
    answer = MsgBox("Have You Selected The Row(s) To Delete?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete Row")
        If answer = vbYes Then
        If Selection.Row <= 10 Then
        MsgBox "You cannot insert a new line here"
        Exit Sub
        End If
        Selection.EntireRow.Delete
Else
            MsgBox "Please Select Row To Delete.", , "Delete Row"
    End If
Application.ScreenUpdating = False
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Range("K9").Formula = "=IF($K8="""","""",IF(AND($H9="""",$J9=""""),"""",$K8+$J9-$H9))"
    Range("K9").AutoFill Range("K9:K" & lr)
End Sub
That is perfect! Thank you so much! Once again proving this is the site to go for answers! Once again, Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,914
Messages
5,638,955
Members
417,062
Latest member
Canucks21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top