英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论