如何在打开工作簿后自动运行宏?

huangapple go评论81阅读模式
英文:

How to autorun a macro after opening a workbook?

问题

我成功创建了一个简单的宏:当单元格B1处于活动状态并按下Enter键时,活动单元格会更改为单元格B4。

这是Module2:

Sub B1ToB4()
    Range("B4").Select
End Sub

这是Microsoft Excel对象下的Sheet1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Address = "$B$1" Then
        Application.OnKey "{ENTER}", "B1ToB4"
        Application.OnKey "~", "B1ToB4"
    Else
        Application.OnKey "{ENTER}"
        Application.OnKey "~"
    End If
End Sub

这在ThisWorkbook中,所以每当我打开工作簿时,单元格B1都会被选中。

Private Sub Workbook_Open()
    Range("B1").Select
End Sub

我想要解决的问题是,当我打开工作簿并按Enter键时,宏不起作用,活动单元格更改为B2而不是B4。问题可能来自使用Worksheet_SelectionChange事件,它需要在工作表中进行一些更改以激活宏。在进行一些更改后,宏开始起作用。我希望宏在打开工作簿后立即起作用,而不需要对其进行任何更改,所以我会感激一些帮助。我已经检查了类似的主题,但是在我的情况下我不知道应该做什么更改。

英文:

I have managed to create a simple macro: When the cell B1 is active and an Enter key is pressed, the active cell is changed to the cell B4.

This is Module2:

Sub B1ToB4()
    Range("B4").Select
End Sub

This is Sheet1 under Microsoft Excel Objects:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Address = "$B$1" Then
        Application.OnKey "{ENTER}", "B1ToB4"
        Application.OnKey "~", "B1ToB4"
    Else
        Application.OnKey "{ENTER}"
        Application.OnKey "~"
    End If
End Sub

This is in ThisWorkbook, so that whenever I open the workbook, the cell B1 is selected.

Private Sub Workbook_Open()
    Range("B1").Select
End Sub

The problem I'd like to solve is that when I open the workbook and press Enter, the macro does not work and the active cell is changed to the B2, not B4. The problem may come from using the Worksheet_SelectionChange event which requires making some change in the worksheet in order to activate the macro. After doing some change, the macro starts to work. I'd like the macro to work right after opening the workbook without making any changes in it, so I'd appreciate some help. I've checked similar topics here, but I just don't know what to change in my case.

答案1

得分: 1

在Workbook_Open中插入这一行:

Private Sub Workbook_Open()
    Range("B1").Select
    Set dummy = Range("B1")
    Sheet1.Worksheet_SelectionChange dummy
End Sub

并声明为公共子程序Worksheet_SelectionChange。

英文:

Insert this line in Workbook_Open

Private Sub Workbook_Open()
    Range("B1").Select
    set dummy = Range("B1")
    Sheet1.Worksheet_SelectionChange (dummy)
End Sub

and declare as Public Sub Worksheet_SelectionChange

答案2

得分: 0

以下是翻译好的代码部分:

工作簿模块中的代码:

Option Explicit

Private Sub Workbook_Open()
   With Worksheets("SHEET01")
      .Activate
      .Range("B1").Select
   End With
End Sub

公共模块中的代码:

Option Explicit

Public curCellAddress As String, enterEventIsSet As Boolean

Public Sub onEnter()
   If ActiveSheet.Name = "SHEET01" Then
      If curCellAddress = "B1" Then
         Cells(4, 2).Select
         Exit Sub
      End If
   End If
   Call removeENTEReventHandlers
End Sub

Public Sub setENTEReventHandlers()
   Application.OnKey "{ENTER}", "onEnter"
   Application.OnKey "~", "onEnter"
   enterEventIsSet = True
End Sub

Public Sub removeENTEReventHandlers()
   If enterEventIsSet Then
      Application.OnKey "{ENTER}"
      Application.OnKey "~"
      enterEventIsSet = False
   End If
End Sub

工作表模块中的代码。在我的示例中,工作表名称是SHEET01:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   curCellAddress = Target(1).Address(0, 0)
   If curCellAddress = "B1" Then
      Call setENTEReventHandlers
   Else
      Call removeENTEReventHandlers
   End If
End Sub
英文:

This code in workbook module:

Option Explicit

Private Sub Workbook_Open()
   With Worksheets("SHEET01")
      .Activate
      .Range("B1").Select
   End With
End Sub

This code in a public module

Option Explicit

Public curCellAddress As String, enterEventIsSet As Boolean

Public Sub onEnter()
   If ActiveSheet.Name = "SHEET01" Then
      If curCellAddress = "B1" Then
         Cells(4, 2).Select
         Exit Sub
      End If
   End If
   Call removeENTEReventHandlers
End Sub

Public Sub setENTEReventHandlers()
   Application.OnKey "{ENTER}", "onEnter"
   Application.OnKey "~", "onEnter"
   enterEventIsSet = True
End Sub

Public Sub removeENTEReventHandlers()
   If enterEventIsSet Then
      Application.OnKey "{ENTER}"
      Application.OnKey "~"
      enterEventIsSet = False
   End If
End Sub

This code in the sheet's module.
In my example the sheet name is SHEET01

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   curCellAddress = Target(1).Address(0, 0)
   If curCellAddress = "B1" Then
      Call setENTEReventHandlers
   Else
      Call removeENTEReventHandlers
   End If
End Sub

huangapple
  • 本文由 发表于 2023年7月4日 22:54:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76613837.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定