Excel中 - 如何在Workbook_Open事件创建按钮 [英] Excel - How to create button upon Workbook_Open event
问题描述
我试图使一个Excel加载创建一个简单的按钮时,任何打开工作簿,但我发现
I'm trying to make an Excel Add-In to create a simple button when any workbook is opened, but I'm getting
对象变量或带块变量未设置
Object variable or With Block variable not set
我认为这正在发生的事情,因为在技术上不存在ActiveWorkbook'呢。
I think this is happening because technically there is no 'ActiveWorkbook' yet.
我想要做的第一件事是删除当前工作表上的任何按钮。然后,我要放置一个按钮。
First thing I want to do is delete any buttons currently on the sheet. Then I want to place a button.
任何人知道如何做到这一点?
Anyone know how to make that happen?
Private Sub Workbook_Open()
ActiveWorkbook.ActiveSheet.Buttons.Delete
Dim CommandButton As Button
Set CommandButton = ActiveWorkbook.ActiveSheet.Buttons.Add(1200, 100, 200, 75)
With CommandButton
.OnAction = "Test_Press"
.Caption = "Press for Test"
.Name = "Test"
End With
End Sub
然后我有一个私人小组TEST_ $ P $(PSS)
来显示MSGBOX。未被虽然创建的按钮。
I then have a Private Sub Test_Press()
to display a MsgBox. The button is not being created though.
推荐答案
幸得 HTTP ://www.jkp-ads.com/Articles/FixLinks2UDF.asp
注意:我还有一个模块我没有低于张贴,里面有宏观的 Project_Count 的我绑在我按钮放置在工作簿只有在工作簿名称是TT_GO_ExceptionReport
Note: I have another module I didn't post below, which houses the macro Project_Count I tied to the button I place on the workbook only if the workbook name is TT_GO_ExceptionReport
我也有下载加载项,将它置于用户的插件文件夹,并安装一个VBScript。如果你想知道如何做到这一点,发表评论。
I also have a VBScript that downloads the Add-In, places it in the users addin folder, and installs it. If you want to know how to do that, leave a comment.
的ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
' Purpose : Code run at opening of workbook
'-------------------------------------------------------------------------
'Initialise the application
InitApp
modProcessWBOpen.TimesLooped = 0
Application.OnTime Now + TimeValue("00:00:03"), "CheckIfBookOpened"
End Sub
的模块1 的名为 modInit
显式的选项
Module 1 named modInit Option Explicit
'Create a module level object variable that will keep the instance of the
'event listener in memory (and hence alive)
Dim moAppEventHandler As cAppEvents
Sub InitApp()
'Create a new instance of cAppEvents class
Set moAppEventHandler = New cAppEvents
With moAppEventHandler
'Tell it to listen to Excel's events
Set .App = Application
End With
End Sub
的模块2 的名为 modProcessWBOpen
显式的选项
Module 2 named modProcessWBOpen Option Explicit
'Counter to keep track of how many workbooks are open
Dim mlBookCount As Long
'Counter to check how many times we've looped
Private mlTimesLooped As Long
' Purpose : When a new workbook is opened, this sub will be run.
' Called from: clsAppEvents.App_Workbook_Open and ThisWorkbook.Workbook_Open
'-------------------------------------------------------------------------
Sub ProcessNewBookOpened(oBk As Workbook)
If oBk Is Nothing Then Exit Sub
If oBk Is ThisWorkbook Then Exit Sub
If oBk.IsInplace Then Exit Sub
CountBooks
'This checks to make sure the name of the new book matches what I
'want to place the button on
If oBk.Name = "TT_GO_ExceptionReport.xlsm" Then
Dim CommandButton As Button
Set CommandButton = Workbooks("TT_GO_ExceptionReport.xlsm").Sheets(1).Buttons.Add(1200, 100, 200, 75)
With CommandButton
.OnAction = "Project_Count"
.Caption = "Press for Simplified Overview"
.Name = "Simplified Overview"
End With
End If
End Sub
Sub CountBooks()
mlBookCount = Workbooks.Count
End Sub
Function BookAdded() As Boolean
If mlBookCount <> Workbooks.Count Then
BookAdded = True
CountBooks
End If
End Function
' Purpose : Checks if a new workbook has been opened
' (repeatedly until activeworkbook is not nothing)
'-------------------------------------------------------------------------
Sub CheckIfBookOpened()
If BookAdded Then
If ActiveWorkbook Is Nothing Then
mlBookCount = 0
TimesLooped = TimesLooped + 1
'May be needed if Excel is opened from Internet explorer
Application.Visible = True
If TimesLooped < 20 Then
Application.OnTime Now + TimeValue("00:00:01"), "CheckIfBookOpened"
Else
TimesLooped = 0
End If
Else
ProcessNewBookOpened ActiveWorkbook
End If
End If
End Sub
Public Property Get TimesLooped() As Long
TimesLooped = mlTimesLooped
End Property
Public Property Let TimesLooped(ByVal lTimesLooped As Long)
mlTimesLooped = lTimesLooped
End Property
的类模块的名为 cAppEvents
' Purpose : Handles Excel Application events
'-------------------------------------------------------------------------
Option Explicit
'This object variable will hold the object who's events we want to respond to
Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
'Make sure newly opened book is valid
ProcessNewBookOpened Wb
End Sub
Private Sub Class_Terminate()
Set App = Nothing
End Sub
这篇关于Excel中 - 如何在Workbook_Open事件创建按钮的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!