How to Disabled a Command AFTER the last row is inserted.

using MS excel 2010
win.7

i want the commandbutton to be disabled after max rows displayed. if that is even possible. i attached my workbook.


This my vba code:
Option Explicit
Private Sub CommandButton1_Click()
Dim pass As String
pass = "nh1234"
ActiveSheet.Protect Password:=pass, UserInterFaceOnly:=True


With Range("A17:A42").SpecialCells(xlVisible).Areas(1)
With .Offset(.Count).Resize(1)
.EntireRow.Hidden = False
.Offset(, 1).Select
End With
End With
End Sub

Something along these lines:

Code:

---

Private Sub CommandButton1_Click()
Dim pass As String
pass = "nh1234"
ActiveSheet.Protect Password:=pass, UserInterFaceOnly:=True
With Range("A17:A42").SpecialCells(xlVisible)
  If .Cells.Count < 26 Then
    With .Areas(1)
      .Offset(.Count).Resize(1).EntireRow.Hidden = False
      .Offset(.Count, 1).Resize(1).Select
      'ActiveSheet.Range("b18:d42").Select
    End With
  Else
    MsgBox "All full"
  End If
End With
End Sub

---

?

.
Another method :

Code:

---

Option Explicit
Private Sub CommandButton1_Click()
Dim pass As String
    pass = "nh1234"
    ActiveSheet.Protect Password:=pass, UserInterFaceOnly:=True


With Range("A17:A42").SpecialCells(xlVisible).Areas(1)
      .Offset(.Count).Resize(1).EntireRow.Hidden = False
 
  ActiveSheet.Range("b18:d42").Select


ShowRows


End With
End Sub


Sub foo()
CommandButton1.Enabled = True
End Sub


Sub ShowRows()
    Dim rng As Range
    Dim c As Range
    Dim sTemp As String


    Set rng = Range("A18:A42")
    sTemp = ""


    For Each c In rng
        If c.EntireRow.Hidden Then
            sTemp = sTemp & "Row " & c.Row & vbCrLf
        End If
    Next c


    If sTemp > "" Then
        CommandButton1.Enabled = True
    Else
        CommandButton1.Enabled = False
    End If
End Sub

---

p45cal ... love your macro. Great code!

Quote:

---

Originally Posted by Logit

.
Another method :

Code:

---

Option Explicit
Private Sub CommandButton1_Click()
Dim pass As String
    pass = "nh1234"
    ActiveSheet.Protect Password:=pass, UserInterFaceOnly:=True


With Range("A17:A42").SpecialCells(xlVisible).Areas(1)
      .Offset(.Count).Resize(1).EntireRow.Hidden = False
 
  ActiveSheet.Range("b18:d42").Select


ShowRows


End With
End Sub


Sub foo()
CommandButton1.Enabled = True
End Sub


Sub ShowRows()
    Dim rng As Range
    Dim c As Range
    Dim sTemp As String


    Set rng = Range("A18:A42")
    sTemp = ""


    For Each c In rng
        If c.EntireRow.Hidden Then
            sTemp = sTemp & "Row " & c.Row & vbCrLf
        End If
    Next c


    If sTemp > "" Then
        CommandButton1.Enabled = True
    Else
        CommandButton1.Enabled = False
    End If
End Sub

---

p45cal ... love your macro. Great code!

---

Thank u guys so much!!!!

.
You are welcome