CODEWITHSUNDEEP
vbaexcel
KISHOR BHOIR
KISHOR BHOIR

Reputation: 89

Command Button () should be clicked only when certain sheet available

I am getting data on daily basis. For that I use different command buttons to filer data and gather data from different sheets. Already kept made around 25 command buttons on first sheet. My issue is e.g. command button say 20 should not be work or should not be click until unless sheet no. 20 available. Currently I am using 

Dim j As Integer, k As Integer
j = Worksheets.Count
For k = 20 To 20
With Worksheets(k)

Sometimes by mistake I click on command button which particular sheet not available and code does not generate any data.

Upvotes: 0

Views: 80

Answers (1)

dgorti
dgorti

Reputation: 1240

Could you do something like below? Obviously you need to replace the "20" with whatever your sheet name is and and you will put this code in your click handler

Dim isWorlsheetAvailable
isWorlsheetAvailable = False
For i = 1 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Worksheets(i).Name = "20" Then
        isWorlsheetAvailable = True
    End If
Next i

If Not isWorlsheetAvailable Then
    MsgBox ("sdffd")
    Exit Sub
End If
Do your work here......

Upvotes: 1

Related Questions