View Full Version : [SOLVED:] How to Disabled a Command AFTER the last row is inserted.
Ladyj205
06-20-2018, 08:13 AM
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
p45cal
06-20-2018, 11:11 AM
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
?
Logit
06-20-2018, 11:38 AM
.
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!
Ladyj205
06-21-2018, 06:28 AM
.
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!!!!
Logit
06-21-2018, 08:08 AM
.
You are welcome
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.