Preventing Users from Uploading a Sheet Manually?

[mayahzid]

Hello! Wondering if it was possible to implement a macro that would prevent users on a workbook from adding a new sheet manually, and force them to use a custom macro. I currently have a macro that is designed for users to add sheets from a different workbook to the current workbook, but it has the functionality of deleting all name ranges and links from the previous workbook. I'm trying to force users to use this macro so I can prevent the workbook from being populated with random junk, and I'm wondering if that's possible to implement!

 

[bebo021999]

Whenever a user tries to add a new sheet manually (by right-clicking on a sheet tab and selecting "Insert"), the VBA code will trigger, and the sheet will be deleted immediately, effectively preventing manual addition.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    ' Delete the newly added sheet to prevent manual addition
    Sh.Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

 

[dmt32]

Hi
Protecting the workbook structure may be sufficient to do what you want

Protect a workbook

Dave

 

[mayahzid]

This doesn't seem to work unfortunately, the macro does stop the user from creating a new blank sheet, but it still allows me to manually insert a new sheet from a different workbook into the current workbook. For example, from the other workbook, I can still "Move or Copy" a sheet into the original workbook, which I want to prevent.

 

[bebo021999]

This doesn't seem to work unfortunately, the macro does stop the user from creating a new blank sheet, but it still allows me to manually insert a new sheet from a different workbook into the current workbook. For example, from the other workbook, I can still "Move or Copy" a sheet into the original workbook, which I want to prevent.

I think protect structure may help. Then you can open it in your macro, then insert sheet by code only.

 

[mayahzid]

This seems to work great! The only thing is that it's preventing any modification to the structure, I'd still like to keep the functionality of being able to delete or move sheets around, or maybe even add a new blank sheet. The only thing I'm really trying to prevent is the user from adding an external worksheet, since for our purposes, the external worksheets are often filled with 10,000+ Name Ranges and links, which can make the workbook extremely laggy, so using the alternate macro that I can ensure that those name ranges aren't added. To be honest, I'm not really sure if it is possible, so I was really just wondering if VBA even supports this kind of function.

 

[Jaafar Tribak]

Add this code to the ThisWorkbook Module and see if it works for you:

Option Explicit

Private WithEvents xlEvents As Application
Private lSheetsCount As Long, oWb As Workbook

Private Sub Workbook_Activate()
    Dim sSheet As String
    If Me.Sheets.Count <> lSheetsCount Then
        'A new Sheet was added from a remote workbook so, delete it.
        sSheet = ActiveSheet.Name
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        Debug.Print "The Sheet:[" & sSheet & "] that was copied\moved from Workbook:[" _
                    & oWb.Name & "] was deleted!"
    End If
End Sub

Private Sub Workbook_Deactivate()
    Set xlEvents = Application
    lSheetsCount = Me.Sheets.Count
End Sub

Private Sub xlEvents_WorkbookDeactivate(ByVal Wb As Workbook)
    Set oWb = Wb
End Sub

 

[mayahzid]

Add this code to the ThisWorkbook Module and see if it works for you:

Option Explicit

Private WithEvents xlEvents As Application
Private lSheetsCount As Long, oWb As Workbook

Private Sub Workbook_Activate()
    Dim sSheet As String
    If Me.Sheets.Count <> lSheetsCount Then
        'A new Sheet was added from a remote workbook so, delete it.
        sSheet = ActiveSheet.Name
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        Debug.Print "The Sheet:[" & sSheet & "] that was copied\moved from Workbook:[" _
                    & oWb.Name & "] was deleted!"
    End If
End Sub

Private Sub Workbook_Deactivate()
    Set xlEvents = Application
    lSheetsCount = Me.Sheets.Count
End Sub

Private Sub xlEvents_WorkbookDeactivate(ByVal Wb As Workbook)
    Set oWb = Wb
End Sub

Wow!!! This works absolutely amazing! Do you mind explaning how the code works? I'm sort of new to VBA so I'd love to understand how the code actaully works :D

 

[Jaafar Tribak]

Wow!!! This works absolutely amazing! Do you mind explaning how the code works? I'm sort of new to VBA so I'd love to understand how the code actaully works :D

The idea is that the active workbook will need to be deactivated BEFORE activating the onther workbook for copying\moving its sheets . So, in the workbook Deactivate event, we cache the initial workbook sheets count in a module level varaible.

Then when the actual copying\moving of sheet(s) takes place, the Activate event fires. This is where we compare the cahed sheets count with the current sheets count. If the current sheets count is greater than the previous cahed sheets count, it means a new sheet has been added or moved and if so, we just delete it.