- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
背景:我有一本记录奥林匹克举重/历史的工作簿。用户可以通过按下调用宏“New_Lift”和“Create_Button”的按钮(添加新电梯)来创建新电梯。这将创建一个包含电梯名称的新工作表,在主工作表上创建一个包含电梯名称的新列,添加一个名为“日志历史记录”的按钮(主工作表)(OnAction = 新工作表子项)。
新工作表、列和按钮创建良好,但在打开工作簿后首次运行宏时收到运行时错误“1004”(此后工作正常)。该错误指向按钮的“.OnAction”。下面是主表和“Create_Button”代码的屏幕截图。非常感谢任何帮助,如果我需要澄清任何内容,请告诉我。
Sub Add_New_Lift()
'*****************************************************************************************************
' This macro creates a new columns that contains the lift name, "Current", "Goal", "% Goal" and formulas
'*****************************************************************************************************
Dim ecol As Integer
Dim erow As Integer
Dim NewLift As String
Dim Lift_Metcon As String
Dim SheetCodeName As String
Application.ScreenUpdating = False
'Ask user to provide the name of the lift through a message box
NewLift = InputBox("New Lift Name:", "Add New Lift")
If StrPtr(NewLift) = 0 Then
Exit Sub
Else
Do
Lift_Metcon = InputBox("Is this a Lift (Weight), Metcon (Time), or AMRAP (Total Reps):" & _
vbCrLf & vbTab & "- Lift" & _
vbCrLf & vbTab & "- Metcon" & _
vbCrLf & vbTab & "- AMRAP" _
, "Type of Measurement")
If StrPtr(Lift_Metcon) = 0 Then
Exit Sub
ElseIf (Lift_Metcon = "Lift") Or (Lift_Metcon = "Metcon") Or (Lift_Metcon = "AMRAP") Then
Exit Do
Else
MsgBox "You have not made a valid entry. Please try again."
End If
Loop
End If
'Find first empty column to add the new lift and formatting as well as Column letters for use with formula
ecol = Worksheets("Main").Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Column
ColNo1 = ecol
ColLet1 = Split(Cells(, ColNo1).Address, "$")(1)
ColNo2 = ecol + 1
ColLet2 = Split(Cells(, ColNo2).Address, "$")(1)
ColNo3 = ecol + 2
ColLet3 = Split(Cells(, ColNo3).Address, "$")(1)
'Formatting
Worksheets("Main").Activate
Columns(ecol).Select
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Range(Cells(3, ecol), Cells(3, ecol + 2)).Merge
Cells(3, ecol) = NewLift
Cells(3, ecol).Font.Size = 16
Cells(4, ecol) = "Current"
Cells(4, ecol + 1) = "Goal"
Cells(4, ecol + 2) = "% Goal"
Range(Cells(3, ecol), Cells(4, ecol + 2)).HorizontalAlignment = xlCenter
Range(Cells(3, ecol), Cells(4, ecol + 2)).Font.Bold = True
Range(Cells(3, ecol), Cells(4, ecol + 2)).ColumnWidth = 8
Range(Cells(1, ecol), Cells(4, ecol + 2)).Interior.Color = RGB(166, 166, 166)
Range(Cells(5, ecol + 2), Cells(100, ecol + 2)).Formula = "=IF(" & ColLet1 & "5<> """", " & ColLet1 & "5/" & ColLet2 & "5,"""" )"
Range(Cells(5, ecol + 2), Cells(100, ecol + 2)).NumberFormat = "0.00%"
If Lift_Metcon = "Metcon" Then
Range(Cells(5, ecol), Cells(100, ecol)).NumberFormat = "0.0"
End If
'Create new worksheet with formatting
Sheets.Add(After:=Sheets(Sheets.Count)).Name = NewLift
Sheets(NewLift).Range("A2") = "Name"
Sheets(NewLift).Range("A1") = Lift_Metcon
Sheets(NewLift).Range("A1").Font.Color = RGB(166, 166, 166)
Sheets(NewLift).Range("A2:B2").Font.Bold = True
Sheets(NewLift).Range("A:A").ColumnWidth = 27
Sheets(NewLift).Range("A1:BZ2").Interior.Color = RGB(166, 166, 166)
Sheets(NewLift).Range("A1").RowHeight = 55
Sheets(NewLift).Range("B2") = "M/F"
Sheets(NewLift).Columns("C").Select
ActiveWindow.FreezePanes = True
Sheets(NewLift).Range("A3").Select
For Each Cell In Range("A3:BZ100") ''change range accordingly
If Cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
Cell.Interior.Color = RGB(217, 217, 217) ''color to preference
Else
Cell.Interior.ColorIndex = xlNone ''color to preference or remove
End If
Next Cell
SheetCodeName = ActiveSheet.CodeName
'Calls the CreateButton subroutine and passes the NewLift from user, last empty column and SheetCodeName (i.e. Sheet5)
Call CreateButton(NewLift, ecol, SheetCodeName)
Worksheets("Records").Activate
erow = Worksheets("Records").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Cells(erow, 1) = NewLift
Worksheets("Main").Activate
Range("A5").Select
Application.ScreenUpdating = True
End Sub
Sub CreateButton(NewLift As String, ecol As Integer, SheetCodeName As String)
Dim Code As String
Dim NewLiftSpace As String
NewLiftSpace = Replace(NewLift, " ", "_")
SheetCodeName = Worksheets(NewLift).CodeName
With ActiveSheet 'Main Sheet
.Buttons.Add(Cells(2, ecol + 1).Left, Cells(2, ecol + 1).Top, 45, 45).Select
Selection.Characters.Text = "Log" & vbCrLf & "History"
Selection.OnAction = SheetCodeName & "." & NewLiftSpace & "_Button"
End With
'subroutine macro text
Code = "Public Sub " & NewLiftSpace & "_Button()" & vbCrLf
Code = Code & "Dim LiftSheet As String" & vbCrLf
Code = Code & "LiftSheet = " & """" & NewLift & """" & vbCrLf
Code = Code & "Call History.Log_History(LiftSheet)" & vbCrLf
Code = Code & "End Sub" & vbCrLf
Code = Code & "Public Sub CommandButton1_Click()" & vbCrLf
Code = Code & "UserForm1.Show" & vbCrLf
Code = Code & "Athlete_Chart(Athlete)" & vbCrLf
Code = Code & "End Sub"
'add macro at the end of the sheet module
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
.InsertLines .CountOfLines + 1, Code
End With
End Sub
编辑:如果 VBA 编辑器打开,代码运行不会出现错误。
最佳答案
这是因为在 Sheets.Add(...
之后,新工作表变为 Active,并且一旦进入 CreateButton()
语句:
With ActiveSheet 'Main Sheet
实际上引用的是新添加的工作表,而不是您预期的“主”工作表
底线,尽可能避免 Activate
/ActiveXXX
/Select
/Selection
编码模式并使用完全限定的范围引用,如以下代码重构所示:
Option Explicit
Sub Add_New_Lift()
'*****************************************************************************************************
' This macro creates a new columns that contains the lift name, "Current", "Goal", "% Goal" and formulas
'*****************************************************************************************************
Dim ecol As Integer, ColNo1 As Integer, ColNo2 As Integer, ColNo3 As Integer
Dim ColLet1 As String, ColLet2 As String, ColLet3 As String
Dim erow As Integer
Dim NewLift As String
Dim Lift_Metcon As String
Dim SheetCodeName As String
Dim cell As Range
Application.ScreenUpdating = False
On Error GoTo errHandler
'Ask user to provide the name of the lift through a message box
NewLift = InputBox("New Lift Name:", "Add New Lift")
If StrPtr(NewLift) = 0 Or NewLift = "" Then Exit Sub
Do
Lift_Metcon = InputBox("Is this a Lift (Weight), Metcon (Time), or AMRAP (Total Reps):" & _
vbCrLf & vbTab & "- Lift" & _
vbCrLf & vbTab & "- Metcon" & _
vbCrLf & vbTab & "- AMRAP" _
, "Type of Measurement")
If StrPtr(Lift_Metcon) = 0 Then Exit Sub
Loop While Not ((Lift_Metcon = "Lift") Or (Lift_Metcon = "Metcon") Or (Lift_Metcon = "AMRAP"))
'Find first empty column to add the new lift and formatting as well as Column letters for use with formula
With Worksheets("Main") '<--| reference your "Main" sheet
ecol = .Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Column
ColNo1 = ecol
ColLet1 = Split(.Cells(, ColNo1).Address, "$")(1)
ColNo2 = ecol + 1
ColLet2 = Split(.Cells(, ColNo2).Address, "$")(1)
ColNo3 = ecol + 2
ColLet3 = Split(.Cells(, ColNo3).Address, "$")(1)
'Formatting
With .Columns(ecol) '<--| reference referenced sheet 'ecol'th column
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
End With
.Range(.Cells(3, ecol), .Cells(3, ecol + 2)).Merge
.Cells(3, ecol) = NewLift
.Cells(3, ecol).Font.Size = 16
.Cells(4, ecol) = "Current"
.Cells(4, ecol + 1) = "Goal"
.Cells(4, ecol + 2) = "% Goal"
.Range(.Cells(3, ecol), .Cells(4, ecol + 2)).HorizontalAlignment = xlCenter
.Range(.Cells(3, ecol), .Cells(4, ecol + 2)).Font.Bold = True
.Range(.Cells(3, ecol), .Cells(4, ecol + 2)).ColumnWidth = 8
.Range(.Cells(1, ecol), .Cells(4, ecol + 2)).Interior.Color = RGB(166, 166, 166)
.Range(.Cells(5, ecol + 2), .Cells(100, ecol + 2)).Formula = "=IF(" & ColLet1 & "5<> """", " & ColLet1 & "5/" & ColLet2 & "5,"""" )"
.Range(.Cells(5, ecol + 2), .Cells(100, ecol + 2)).NumberFormat = "0.00%"
If Lift_Metcon = "Metcon" Then .Range(.Cells(5, ecol), .Cells(100, ecol)).NumberFormat = "0.0"
'Create new worksheet with formatting
With Sheets.Add(After:=Sheets(Sheets.Count)) '<--| this will make the new sheet the "Active" one
.Name = NewLift
.Range("A2") = "Name"
.Range("A1") = Lift_Metcon
.Range("A1").Font.Color = RGB(166, 166, 166)
.Range("A2:B2").Font.Bold = True
.Range("A:A").ColumnWidth = 27
.Range("A1:BZ2").Interior.Color = RGB(166, 166, 166)
.Range("A1").RowHeight = 55
.Range("B2") = "M/F"
.Columns("C").Select
ActiveWindow.FreezePanes = True
For Each cell In .Range("A3:BZ100") ''change range accordingly
If cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
cell.Interior.Color = RGB(217, 217, 217) ''color to preference
Else
cell.Interior.ColorIndex = xlNone ''color to preference or remove
End If
Next cell
SheetCodeName = .CodeName
End With
.Activate '<--| jump back to referenced (i.e.: "Main") sheet and make it active again
'Calls the CreateButton subroutine and passes the NewLift from user, last empty column and SheetCodeName (i.e. Sheet5)
CreateButton NewLift, ecol, SheetCodeName
End With
Worksheets("Records").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = NewLift
errHandler:
Application.ScreenUpdating = True
End Sub
Sub CreateButton(NewLift As String, ecol As Integer, SheetCodeName As String)
Dim Code As String
Dim NewLiftSpace As String
NewLiftSpace = Replace(NewLift, " ", "_")
SheetCodeName = Worksheets(NewLift).CodeName
With ActiveSheet.Buttons.Add(Cells(2, ecol + 1).Left, Cells(2, ecol + 1).Top, 45, 45) '<--| reference a new button on active sheet
.Characters.Text = "Log" & vbCrLf & "History"
.OnAction = SheetCodeName & "." & NewLiftSpace & "_Button"
End With
'subroutine macro text
Code = "Public Sub " & NewLiftSpace & "_Button()" & vbCrLf
Code = Code & "Dim LiftSheet As String" & vbCrLf
Code = Code & "LiftSheet = " & """" & NewLift & """" & vbCrLf
Code = Code & "Call History.Log_History(LiftSheet)" & vbCrLf
Code = Code & "End Sub" & vbCrLf
Code = Code & "Public Sub CommandButton1_Click()" & vbCrLf
Code = Code & "UserForm1.Show" & vbCrLf
Code = Code & "Athlete_Chart(Athlete)" & vbCrLf
Code = Code & "End Sub"
'add macro at the end of the sheet module
With ActiveWorkbook.VBProject.VBComponents(SheetCodeName).CodeModule '<--| reference your new sheet 'CodeName'
.InsertLines .CountOfLines + 1, Code
End With
End Sub
我特意选择离开:
.Activate '<--| jump back to referenced (i.e.: "Main") sheet and make it active again
因为我想让用户将“主”工作表保留为事件工作表
所以我还利用它在 CreateButton() 中保留 ActiveSheet
引用来隐式引用“Main”表,而不是更改 Sub signature 添加新参数(引用到“主”表或其名称)也可以使用和引用“主”表
关于vba - 首次运行时出现 OnAction 运行时错误 "1004",我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41582083/
您好,我在使用 JavaFX 时遇到了一个奇怪的问题,我正在使用 Scene Builder 为您提供的 Controller 。问题是无论菜单栏上的菜单在单击时什么都不做。这是我的 Controll
所以我已经有一段时间了,我已经搜索了很多网站和论坛,但是我找不到解决我的问题的方法。 我正在尝试向 .OnAction 添加参数Checkbox 的事件 所以..例如 Dim chk as Check
我有一本工作簿,用作其他人的模板。模板文档已锁定以供编辑,因此最终用户必须使用其项目的具体信息“另存为”。 此模板包括多个工作表以合并所有可能的布局选项。由于与我一起工作的大多数人既没有经验也不熟悉
有没有办法知道 NavLink 何时处于事件状态?像这样的东西?: 最佳答案 您可以使用 NavLink 的 isActive 回调来实现此目的。 isActive 允许您在链接处于事件状态时定义自
嗯,我的问题是我创建了一个 VBA Sub,它接收一个 excel 单元格引用和 2 个文本值以及一个 Variant 作为参数。 Sub CreateButton(oCell, sLabel, sO
这个问题已经有答案了: javafx 8 compatibility issues - FXML static fields or methods (1 个回答) 已关闭 6 年前。 我是 JavaF
我创建了一个新的自定义控件,其中包含标签、文本字段和按钮。如何在 FXML 文件中设置自定义控件的按钮“onAction”方法? 示例代码: 最佳答案 好的,我找到了解决
我的功能区上有几个按钮(目前只有一个拆分按钮和菜单上的几个按钮)。因此,我可以为操作处理程序选择两种不同的设计模式。 模式 #1(常用方法) public void DoStuff(Office.
我设计了一个 Outlook 2010 加载项,我试图在其中触发(或者更确切地说,捕获)单击按钮时触发的事件,如图所示 in this article .我瞄准了正确的 XML(因为在功能区上可以看到
我正在尝试使用以下宏将 vba 代码分配给形状。 使用代码时出现运行时错误 438“对象不支持此属性或方法” 我的工作表编号总是改变,所以我不能将它硬编码到下面的代码中。 这是我正在使用的代码: Su
有没有办法处理 Chrome 打包的 appWindow 事件/非事件状态?类似这样的事件 onActive 或 onFocus。 最佳答案 AppWindow 不提供此功能,但标准 DOM 窗口通过
背景:我有一本记录奥林匹克举重/历史的工作簿。用户可以通过按下调用宏“New_Lift”和“Create_Button”的按钮(添加新电梯)来创建新电梯。这将创建一个包含电梯名称的新工作表,在主工作表
我是 VBA 新手,正在尝试使用 smartArt.Nodes 基于某些单元格数据动态生成组织结构图。我能够毫无问题地生成图表。现在,我希望能够通过单击图表的特定节点来显示它们的更多详细信息。我知道我
我关注了directions here为 Access 应用程序创建自定义功能区。但所有按钮都不起作用!我不断收到一条错误消息,指出 Access 无法找到该函数或宏,即使它是公共(public)的且
假设您有两个工作簿,一个名为“MyWorkbook”,另一个名为“PatchMyWorkbook”。两个工作簿在保存时都打开。 “PatchMyWorkbook”有一个宏,用于添加按钮并将“MyWor
我遇到了一个特殊的问题,尽管在谷歌上搜索和修补了好几个小时,我还是没能解决这个问题。
我正在尝试创建一种方法,以便在按下 Quit MenuItem 时退出应用程序。 我有以下方法: @FXML public void doExit(ActionEvent event) { P
我有一个自定义功能区,菜单中有多个复选框:
我有一个 FXML 编辑器和一个 Controller ,它在 TreeView 中选择时设置域对象。因此,每次在 TreeView 中选择一个新对象时, Controller 都会解除编辑器中所有控
如何将参数传递给在 Excel VBA 中使用 onAction 事件注册的方法? 我的代码是: With ActiveSheet.CheckBoxes.Add(rCell.Left, rCell.T
我是一名优秀的程序员,十分优秀!