Как захватить лист добавляется через Copy / Paste в Excel VBA

Я пытаюсь захватить листы, которые копируются в книгу из другой книги.
Workbook_NewSheet событие не запускается, когда листы копируются из другой книги. Он срабатывает только в том случае, если пользователь вручную вставляет их через (пункт меню Вставить->лист), или когда вы добавляете новый лист через VBA как ThisWorkbook.Worksheets.Add.

То, что я пытаюсь захватить, - это в основном операция вставки, которая приводит к новому листу.

Это может быть результатом любого из приведенных ниже действий пользователя:

  1. пользователь копирует существующий лист, перетаскивая его, удерживая клавишу Control (которая добавляет новый лист)
  2. пользователь копирует лист/ы из другой книги
  3. пользователь переместил листы из другой книги

Или любой из приведенных ниже кодов VBA: 

SourceWorkbook.Sheets(“SourceSheet”).Copy Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'copy across workbook'  
SourceWorkbook.Sheets(“SourceSheet”).Move Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'move across workbook'  
ThisWorkbook. Sheets(“SheetName”).Copy 'copy within workbook'

Если вы знаете какой-либо способ захвата результатов этого действия/макроса в VBA, это было бы очень полезно.

Пожалуйста, обратите внимание, что я не хочу, чтобы избежать такого действия пользователя (поэтому я не хочу, чтобы защитить но я хочу обработать вставленный лист программно, чтобы проверить данные, и если подобный лист уже существует, то обновить существующий лист, а не иметь одинаковые данные в двух листах.

5 2
excel vba excel-vba excel-2003

5 ответов:

Событие SheetActivate сработает при всех этих обстоятельствах. Очевидно, что он будет стрелять и при многих других обстоятельствах. Это звучит как королевская боль, но вы можете сохранить свою собственную коллекцию рабочих листов и сравнить свою коллекцию с ThisWorkbook.Коллекция листов, чтобы увидеть, если что-то было добавлено/удалено.

Если вы пытаетесь предотвратить это, вы можете рассмотреть возможность защиты структуры книги вместо того, чтобы делать это в коде.

2

Когда лист копируется, его имя всегда заканчивается на "(2) "или, по крайней мере,")". Вы можете проверить это вот так

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name Like "*(2)" Then
        Application.DisplayAlerts = False
        Sh.Delete
        Application.DisplayAlerts = True
    End If
End Sub
2

Способ, которым я это подразумеваю, является 

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
ToggleMenuOptions False, 848, 889
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
ToggleMenuOptions True, 847, 848, 889
End Sub

Public Function ToggleMenuOptions(bToggle As Boolean, ParamArray ControlID() As Variant) As Boolean
'848 Move or Copy Sheet...
'889 Rename Sheet
'847 Delete Sheet
On Error GoTo lblError
Dim oControl As CommandBarControl, oControls As CommandBarControls, iControl As Integer
If IsMissing(ControlID) Then
    ToggleMenuOptions = False
    Exit Function
End If

For iControl = LBound(ControlID) To UBound(ControlID)
    For Each oControl In Application.CommandBars.FindControls(ID:=ControlID(iControl))
        oControl.Enabled = bToggle
    Next
Next
ToggleMenuOptions = True
Exit Function
lblError:
    If Err.Number Then
        ToggleMenuOptions = False
        Exit Function
    End If
End Function

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "Please use Add New Project option in custom Toolbar to add new sheets!!", vbExclamation, "Not Supported"
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End Sub

Таким образом, мои пользователи не смогут переименовывать, добавлять или удалять листы. На данный момент это работает довольно хорошо.

1

Единственный способ, который я могу придумать, чтобы сделать это без поддержки отдельной коллекции листов, - это поддерживать статический массив имен листов (или кодовых имен листов) и сравнивать его с фактическими листами в книге каждый раз, когда срабатывает событие SheetActivate, чтобы обнаружить какие-либо добавления. Если вы не хотите/не можете хранить список в массиве, вы всегда можете использовать скрытый лист для хранения списка. Является ли это большей или меньшей болью, чем поддержание отдельной коллекции, спорно :)

0

Я работаю над чем-то подобным, но не могу заблокировать ни одно из действий пользовательского меню. У меня есть листы, тип которых важен - каждый лист является либо ведущим, либо подчиненным-каждый главный лист суммирует над подчиненными листами под ним, и мне нужно сохранить эти формулы чистыми.

Вместо того, чтобы вести список листов в дополнительном скрытом листе, я определяю 2 скрытых имени на каждом листе, записывая смещение индекса листа к его связанному главному листу и ссылку на связанный главный лист. лист. Так что если мой лист (скажем) +2 вкладки от его основного листа, то на листе активировать/деактивировать (не уверен, что из них лучше отслеживать на этом этапе) это смещение изменится, если что-то будет вставлено, удалено или перемещено. Это охватывает большинство или все события, которые могут возникнуть в результате перемещения или копирования листов.

Если лист был перемещен, я циклически просматриваю книгу и вычисляю новые ссылки на главный / ведомый индекс для каждого листа.

Отправит код, когда я получу это достаточно стабильная, но, похоже, схема, которая будет работать в самых разных обстоятельствах.

0