PDA

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