在重新命名前单时,如何使某些传统做法守则发挥作用?
这里有一个办法。 陷阱是通过一个专门班,在申请一级把事件捉.见肘。 利用“谢特活动”活动,储存对活页及其名称的提及。 当该表失效(另一个启用)时,将纸面参考资料的名称与所储存的座标进行比较。 这里的类别(所谓的CExcelEvents):
Option Explicit
Private WithEvents xl As Application
Private CurrSheet As Worksheet
Private CurrSheetName As String
Private Sub Class_Initialize()
Set xl = Excel.Application
Set CurrSheet = ActiveSheet
CurrSheetName = CurrSheet.Name
End Sub
Private Sub Class_Terminate()
Set xl = Nothing
End Sub
Private Sub xl_SheetActivate(ByVal Sh As Object)
If CurrSheetName <> CurrSheet.Name Then
Debug.Print "You ve renamed the sheet: " & CurrSheetName & " to " & CurrSheet.Name
Do something here - rename the sheet to original name?
End If
Set CurrSheet = Sh
CurrSheetName = CurrSheet.Name
End Sub
利用《工作手册》的公开活动,用全球变量证明了这一点:
Public xlc As CExcelEvents
Sub Workbook_Open()
Set xlc = New CExcelEvents
End Sub
只有在用户选择另一个工作表格时,上述例子才会启动。 如果你想要更加冷静,也就监测“谢特变革”事件。
即便使用申请标语,显然也没有任何处理结果。 怎么说。
我可能试图抓住这一机会,将工作单的启动价值储存起来,并尽可能地检查工作单的启动价值——这当然是一个黑板。
以下是我的工作,希望会有所帮助。
本工作手册模块:
Private strWorksheetName As String
Private Sub Workbook_Open()
strWorksheetName = shtMySheet.Name
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call CheckWorksheetName
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call CheckWorksheetName
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Call CheckWorksheetName
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call CheckWorksheetName
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Call CheckWorksheetName
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call CheckWorksheetName
End Sub
Private Sub CheckWorksheetName()
If the worksheet has changed name
If shtMySheet.Name <> strWorksheetName Then
DoSomething
End If
End Sub
我知道这是一个老的问题,但我最近开始使用Excel s CELL(“filename”)功能,这些功能详细说明了档案和表格名称。
我们可以使用这一众所周知的公式,用上表:
=MID(CELL(“身份”)、A1、FIND(“”)、CELL(“身份””、“A1)+1,255)”
通过将这一职能写给隐藏的工作单,然后监测<代码>_计算的活动,我们可以对工作名称作任何改动。
我不得不采用这一方法,因为我需要与客户分享某些传统做法编码,这使他有可能在方案上和在表格上打字某些工作名称。 这种方法包含的是纸面名称变更的事件,即便是在代码上。
在下文的《凯尔顿法典》中,我刚刚掌握了积极工作名称的改动,但没有任何东西可以阻止你增加目标工作单,并相应调整处理法。
The Code below is in the Workbook Code-behind:
Option Explicit
Private mSheetNamesWS As Worksheet
Private mOldSheetName As String
Private Sub Workbook_Open()
Find or create the hidden worksheet
containing the sheet reference.
On Error Resume Next
Set mSheetNamesWS = Me.Worksheets("SheetNames")
On Error GoTo 0
If mSheetNamesWS Is Nothing Then
Disable events so that the _calculate event
isn t thrown.
Application.EnableEvents = False
Set mSheetNamesWS = Me.Worksheets.Add
With mSheetNamesWS
.Name = "SheetNames"
.Visible = xlSheetVeryHidden
End With
Application.EnableEvents = True
End If
Update the sheet reference.
If TypeOf ActiveSheet Is Worksheet Then
UpdateCellFormula
End If
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Active sheet has changed so update the reference.
If TypeOf ActiveSheet Is Worksheet Then
UpdateCellFormula
End If
End Sub
Private Sub UpdateCellFormula()
Dim cellRef As String
Sense check.
If mSheetNamesWS Is Nothing Then Exit Sub
The CELL function returns details about
the file and sheet name of any
specified range.
By adding a formula that extracts the
sheet name portion from the CELL function,
we can listen for any changes
of that value in the _calculate event method.
Disable events to avoid a spurious
_calculate event.
Application.EnableEvents = False
cellRef = ActiveSheet.Name & "!A1"
With mSheetNamesWS.Range("A1")
.Formula = _
"=MID(CELL(""filename""," & _
cellRef & _
"),FIND(""]"",CELL(""filename""," & _
cellRef & _
"))+1,255)"
mOldSheetName = .Value
End With
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Disregard any sheet that isn t our reference sheet.
If Not Sh Is mSheetNamesWS Then Exit Sub
The reference sheet has recalculated.
It means the value of the cell containing
the current sheet name has changed.
Ergo we have a sheet name change.
Handle the event here ...
MsgBox "You can t change the name of this sheet!"
Application.EnableEvents = False
ActiveSheet.Name = mOldSheetName
Application.EnableEvents = True
End Sub
重新命名后发射的唯一事件是Application.CommandBars_OnUpdate。 根据这一点,你可以制定法典,在任何纸面名称发生变化时,迅速检查。 很显然,这种办法看上去是ky,而且由于<代码>而出现一些间接费用,几乎在任何应用变化上发生火灾,任何途径比任何情况都好。 我注意到,在<代码>Application_SheetSelectionChange之后,它每秒多报两次火灾,尽管如此,它不应放弃申请。
下面是展示<条码>应用的精选课程。 活动有助于跟踪一些额外工作事项,如增加、改名、移动和删除。
创建级模块,名称为cSheetEvents,并排列如下:
Option Explicit
Public Event SheetAdd(ByVal wb As Workbook, ByVal sh As Object)
Public Event SheetRename(ByVal wb As Workbook, ByVal sh As Object, ByVal oldName As String)
Public Event SheetMove(ByVal wb As Workbook, ByVal sh As Object, ByVal oldIndex As Long)
Public Event SheetDelete(ByVal wb As Workbook, ByVal oldName As String, ByVal oldIndex As Long)
Public Event SheetAny()
Private WithEvents app As Application
Private WithEvents appCmdBars As CommandBars
Private skipCheck As Boolean
Private sheetData As Object
Private Sub Class_Initialize()
Set app = Application
Set appCmdBars = Application.CommandBars
Set sheetData = CreateObject("Scripting.Dictionary")
Dim wb As Workbook
For Each wb In app.Workbooks
Dim sh As Object
For Each sh In wb.sheets
sheetData(sh) = Array(sh.Name, sh.index, wb)
Next
Next
End Sub
Private Sub Class_Terminate()
Set sheetData = Nothing
End Sub
Private Sub app_NewWorkbook(ByVal wb As Workbook)
Dim sh As Object
For Each sh In wb.sheets
sheetData(sh) = Array(sh.Name, sh.index, wb)
Next
End Sub
Private Sub app_WorkbookOpen(ByVal wb As Workbook)
Dim sh As Object
For Each sh In wb.sheets
sheetData(sh) = Array(sh.Name, sh.index, wb)
Next
End Sub
Private Sub app_WorkbookNewSheet(ByVal wb As Workbook, ByVal sh As Object)
sheetData(sh) = Array(sh.Name, sh.index, wb)
RaiseEvent SheetAdd(wb, sh)
RaiseEvent SheetAny
skipCheck = True
End Sub
Private Sub app_SheetChange(ByVal sh As Object, ByVal Target As Range)
skipCheck = True
End Sub
Private Sub appCmdBars_OnUpdate()
If skipCheck Then
skipCheck = False
Else
Dim anyEvt As Boolean
Dim wb As Workbook
For Each wb In app.Workbooks
Dim sh As Object
For Each sh In wb.sheets
If Not sheetData.exists(sh) Then
sheetData(sh) = Array(sh.Name, sh.index, wb)
RaiseEvent SheetAdd(wb, sh)
anyEvt = True
End If
Next
Next
On Error Resume Next
For Each sh In sheetData
Set wb = sheetData(sh)(2)
If wb.Name = "" Then
sheetData.Remove sh
Set sh = Nothing
Set wb = Nothing
Else
Dim oldName As String
oldName = sheetData(sh)(0)
Dim oldIndex As Long
oldIndex = sheetData(sh)(1)
If sh.Name = "" Then
sheetData.Remove sh
Set sh = Nothing
RaiseEvent SheetDelete(wb, oldName, oldIndex)
anyEvt = True
Else
If sh.Name <> oldName Then
sheetData(sh) = Array(sh.Name, sh.index, wb)
RaiseEvent SheetRename(wb, sh, oldName)
anyEvt = True
ElseIf sh.index <> oldIndex Then
sheetData(sh) = Array(sh.Name, sh.index, wb)
RaiseEvent SheetMove(wb, sh, oldIndex)
anyEvt = True
End If
End If
End If
Next
If anyEvt Then
RaiseEvent SheetAny
End If
End If
End Sub
例如,在<条码>后发生的一些不必要的<条码> 活动,通过增加旗帜变量减少间接费用。 你可能会试图绕过其他不必要的事件。 注,例如Application_SheetSelection 改动:当用户通过打字重新命名表格,然后点击纸张上任何(未选定)电池,以及<代码>在纸面上重新命名时发生火灾,而且有些地方存在挥发公式。
如欲测试,请见。 模块,将以下代码放在其中:
Option Explicit
Private WithEvents sheetEvents As cSheetEvents
Private Sub Workbook_Open()
Set sheetEvents = New cSheetEvents
End Sub
Private Sub sheetEvents_SheetAdd(ByVal wb As Workbook, ByVal sh As Object)
MsgBox _
"Sheet added" & vbCrLf & _
Now & vbCrLf & vbCrLf & _
"Workbook: " & wb.Name & vbCrLf & _
"Name: " & sh.Name
End Sub
Private Sub sheetEvents_SheetRename(ByVal wb As Workbook, ByVal sh As Object, ByVal oldName As String)
MsgBox _
"Sheet renamed" & vbCrLf & _
Now & vbCrLf & vbCrLf & _
"Workbook: " & wb.Name & vbCrLf & _
"Old name: " & oldName & vbCrLf & _
"New name: " & sh.Name
End Sub
Private Sub sheetEvents_SheetMove(ByVal wb As Workbook, ByVal sh As Object, ByVal oldIndex As Long)
MsgBox _
"Sheet renamed" & vbCrLf & _
Now & vbCrLf & vbCrLf & _
"Workbook: " & wb.Name & vbCrLf & _
"Name: " & sh.Name & vbCrLf & _
"Old index: " & oldIndex & vbCrLf & _
"New index: " & sh.Index
End Sub
Private Sub sheetEvents_SheetDelete(ByVal wb As Workbook, ByVal oldName As String, ByVal oldIndex As Long)
MsgBox _
"Sheet deleted" & vbCrLf & _
Now & vbCrLf & vbCrLf & _
"Workbook: " & wb.Name & vbCrLf & _
"Name: " & oldName & vbCrLf & _
"Index: " & oldIndex
End Sub
Save the workbook, and open, after that each 姓名和SheetDelete活动将受到警惕。
我急切地等待对此做出答复,因为我在进行大量搜查后,没有看到答案。 我发现,没有重新命名的工作表格,因此,你被迫采取替代做法。
我所看到的最好情况(awful)是禁止重新命名在册,将其改为只读或看不见,然后提供改名的你自己的工具棒或子。 令人厌恶的是,用户对此表示仇恨。
我也看到有人把更名的菜单放在办公室工具栏中,但这种说法并不阻止在桌上重复和重新命名。 也是非常丑恶的,用户对此表示仇恨。
好幸运的是,我希望有人会得到更好的回答。
For an Excel formula I need the first cell out of a list of cells which contains a numeric value: A | B | C | ... | Z | ----------------------------- | 0.1 | 0.4 | ... | =0.1 | | ...
I have a stored procedure that imports differently formatted workbooks into a database table, does work on them then drops the table. Here is the populating query. SELECT IDENTITY(INT,1,1) AS ID ...
The following code works. the connection opens fine but recordset.recordCount always returns -1 when there is data in the table. ANd If I try to call any methods/properties on recordset it crashes ...
I m using Application run to call several macros in order like this. Sub Run_All_Macros() Application.Run ("Macro_1") Application.Run ("Macro_1") End Sub When I start Run_All_Macros, all the ...
Does anyone know how to convert an Excel date to a correct Unix timestamp?
I am trying to import an excel file into a data table using GemBox and I keep getting this error: Invalid data value when extracting to DataTable at SourceRowIndex: 1, and SourceColumnIndex: 1. As ...
I am looking for any tips or resources on importing from excel into a SQL database, but specifically when the information is NOT in column and row format. I am currently doing some pre-development ...
I have created an Add-In in C# that implements user defined functions for Excel. These UDF s return immediately, but they control background asynchronous procedures. These procedures have status ...