Consulting

Results 1 to 5 of 5

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

  1. #1
    VBAX Regular
    Joined
    Apr 2018
    Posts
    50
    Location

    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
    Attached Files Attached Files
    Last edited by Ladyj205; 06-20-2018 at 09:21 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Something along these lines:
    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
    ?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    Another method :

    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!

  4. #4
    VBAX Regular
    Joined
    Apr 2018
    Posts
    50
    Location
    Quote Originally Posted by Logit View Post
    .
    Another method :

    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!!!!

  5. #5
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    You are welcome

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •