打开工作簿时运行宏 [英] run macro when open workbook

问题描述

我已经做了搜索.最相关的建议宏名称为"workbook_open"但是我仍然必须手动调用该模块.这是我编写的代码.(欢迎提出任何其他建议，因为这是我的第一个vba脚本-73岁)

I have done a search. The most relevant suggests the macro name of "workbook_open" But I still must manually invoke the module. Here's what I've coded. (Any other suggestions welcome as this is my first vba script -- at age 73)

Sub Workbook_Open()  
  Dim lastRow As Long     'last row with data  
  Dim thisDate As Double  'start timestamp  
  thisDate = Now()  
  With Sheets("Pressure Log")  
    lastRow = .Range("B" & .Rows.Count).End(xlUp).Row 'populate next row with date/time  
    Range("B" & lastRow).Offset(1) = Format(thisDate, "dddd")  
    Range("B" & lastRow).Offset(1, 1) = Format(thisDate, "mm/dd/yyyy")  
    Range("B" & lastRow).Offset(1, 2) = Format(thisDate, "hh:mm AM/PM")  
    Range("B" & lastRow).Offset(1, 3).Select 'position for user data  
  End With  
End Sub  

推荐答案

在Visual Basic编辑器(VBE)中，打开 Project Explorer (Ctrl + R)，然后双击 ThisWorkbook 模块(或右键单击它并选择查看代码"):

In the Visual Basic Editor (VBE), bring up the Project Explorer (Ctrl+R), then double-click the ThisWorkbook module (or right-click it and select "View Code"):

这将调出 ThisWorkbook 模块的 code-behind . ThisWorkbook 代表托管您的VBA项目的工作簿；这是一种特殊的模块类型，继承了 Excel.Workbook 类的所有成员，这些成员可以表示任何 Excel工作簿.

That will bring up the ThisWorkbook module's code-behind. ThisWorkbook represents the workbook that's hosting your VBA project; it's a special type of module that inherits all the members of the Excel.Workbook class, which can represent any Excel workbook.

在代码窗格的顶部，您会注意到两个下拉列表:

At the top of the code pane, you will notice two dropdowns:

从左侧的下拉菜单中选择 Workbook ；VBE自动为 Open 事件生成一个事件处理程序过程:

Select Workbook from the left-hand dropdown; the VBE generates an event handler procedure for the Open event, automatically:

请注意，右侧的下拉菜单现在显示 Open -如果您单击该下拉列表，您会发现它列出了 Workbook 的每个事件代码>可以处理；选择一个将自动生成具有正确签名/原型的方法.

Notice the right-hand dropdown now says Open - if you click that dropdown, you'll find that it lists every event that a Workbook can handle; selecting one will automatically generate a method with the correct signature/prototype for it.

现在获取您的代码，并将其放入该事件处理程序过程中，保存-这样就设置好了！下次在启用宏的情况下打开工作簿时，将调用该事件处理程序，并且您的宏将运行.

Now take your code and put it in that event handler procedure, save - and you're set! Next time that workbook is opened with macros enabled, that event handler will be invoked, and your macro will run.

玩得开心！学习永远不会太晚！

Have fun! It's never too late to learn!

这篇关于打开工作簿时运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！