所以我一直在努力让它工作,当工作表检测到更改时,它似乎不想调用模块。我知道它正在检测更改,因为一旦检测到更改,这个错误就会弹出。Error picture
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "Worksheet_Change event fired"
Dim row As Long
'Check if the changed cells are in column D or F and have been filled
If Target.Column = 4 Or Target.Column = 6 Then
If Target.Value <> "" And Cells(Target.row, Target.Column - 1).Value <> "" Then
'Both cells in the row have been filled, ask the user if they want to run the script
row = Target.row
If MsgBox("Do you want to calculate the hours for " & row & "?", vbYesNo) = vbYes Then
'User clicked Yes, execute the script for the row
Select Case row
Case 10
Call Monday
MsgBox "Monday has been calculated"
Case 11
Call Tuesday
MsgBox "Tuesday has been calculated"
Case 12
Call Wednesday
MsgBox "Wednesday has been calculated"
Case 13
Call Thrusday
MsgBox "Thursday has been calculated"
Case 14
Call Friday
MsgBox "Friday has been calculated"
End Select
Else
'User clicked No, move to the next row (if it exists)
If row < 14 Then
'Move to the next row
Cells(row + 1, 4).Select
End If
End If
End If
End If
End Sub
要调用的所有模块都是公共的,例如
Public Sub Monday()
'script here
End Sub
我已经尝试了很多东西,从应用程序运行,modules_name =“here”,worksheet_calculate,然后改变脚本完全工作的worksheet_calculate。我难倒了。所有的模块都在按预期工作,因为我可以手动运行它们,它的工作。是的,我确实尝试了“调用星期一。星期一”以及。
更新,修复了错误。仍然没有运行任何模块。还更新了脚本以监视合并的单元格Pictures are worth 1000 words
2条答案
按热度按时间i1icjdpr1#
我做了一个截图,你的模块和程序应该是什么样子的,和评论里解释的一样,但是根据(不仅仅是)中国人的说法,一张图说了一千多个字:
正如你所看到的,如何调用你的模块并不重要,重要的是过程的名字:-)
mv1qrgav2#
谢谢大家,我的问题是因为excel的工作方式。如果它的目标是一个日期/时间格式的单元格。目标。值不是正确的方法来处理这个问题。需要使用
Isempty(Range("cell"))
来实现这个方法。