CODEWITHSUNDEEP
vbabutton
Dolphin975
Dolphin975

Reputation: 341

Excel vba when adding or deleting sheet in workbook, show/hide button in main sheet

I have a button with a simple macro that deletes certain sheets. I'd like to show this button only when those sheets are actually there (I can use worksheets.count because I have 2 "permanent" sheets; if > 2 then I know I have a new sheet and I want to show the button to delete it if I want to).

I think I have to use "Workbook.SheetChange event" because "Worksheet.Change event" doesn't seem to work for me in this case.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim foglio_parametri As Worksheet
Set foglio_parametri = ThisWorkbook.Worksheets("PARAMETRI") 'my main sheet where I want to show/hide the button

Application.ScreenUpdating = True
If Application.Worksheets.Count > 2 Then
    foglio_parametri.CommandButton2.Visible = True
Else
    foglio_parametri.CommandButton2.Visible = False
End If


End Sub

Thank you very much for your time.

Upvotes: 1

Views: 60

Answers (1)

Storax
Storax

Reputation: 12167

I will not use your names as they are in a foreign language I do not understand .

Let's assume the button you are talking about is in a sheet with the name sheet3 which also has the codename sheet3. The button itself has the name CommandButton1. Let's further assume the certain sheets you are talking about have the names sheet4 and sheet5 then I would add the following code to the workbook module

Option Explicit

Private Sub Workbook_Open()
    Sheet3.HidecmdBtn
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "Sheet3" Then
        Sheet3.HidecmdBtn
    End If
End Sub

In the worksheet module of sheet3 you have the following code

Option Explicit

    Private Sub CommandButton1_Click()        
        ' Your code goes here

        ' In case your code deletes the sheets you have to hide the button
        HidecmdBtn
    End Sub

    Sub HidecmdBtn()

        Dim Sh As CommandButton
        ' My button is located on sheet 3 and has the name "CommandButton1"
        Set Sh = CommandButton1

        Dim sh1Name As String
        Dim sh2Name As String
        sh1Name = "Sheet4"
        sh2Name = "Sheet5"

        If SheetExists(sh1Name) Or SheetExists(sh2Name) Then
            Sh.Visible = msoTrue
        Else
            Sh.Visible = msoFalse
        End If    

    End Sub

In a normal module you have 

Public Function SheetExists(SheetName As String, Optional wrkBook As Workbook) As Boolean

    If wrkBook Is Nothing Then
        Set wrkBook = ActiveWorkbook 'or ThisWorkbook - whichever appropriate
    End If

    Dim obj As Object

    On Error GoTo HandleError
    Set obj = wrkBook.Sheets(SheetName)
    SheetExists = True
    Exit Function

HandleError:
    SheetExists = False
End Function

Upvotes: 1

Related Questions