英文:
Excel Worksheet to Call Modules
问题
所以我一直在苦苦思索如何让这个工作,它似乎不愿在工作表检测到更改时调用模块。我知道它正在检测到更改,因为一旦检测到更改,就会弹出此错误。[错误图片](https://i.stack.imgur.com/QiCNY.png)
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "Worksheet_Change事件触发"
Dim row As Long
'检查更改的单元格是否在D列或F列,并已填充
If Target.Column = 4 Or Target.Column = 6 Then
If Target.Value <> "" And Cells(Target.Row, Target.Column - 1).Value <> "" Then
'该行中的两个单元格已填充,询问用户是否要运行脚本
row = Target.Row
If MsgBox("是否要计算第 " & row & " 行的小时数?", vbYesNo) = vbYes Then
'用户点击是,执行该行的脚本
Select Case row
Case 10
Call Monday
MsgBox "星期一已计算"
Case 11
Call Tuesday
MsgBox "星期二已计算"
Case 12
Call Wednesday
MsgBox "星期三已计算"
Case 13
Call Thursday
MsgBox "星期四已计算"
Case 14
Call Friday
MsgBox "星期五已计算"
End Select
Else
'用户点击否,移动到下一行(如果存在)
If row < 14 Then
'移动到下一行
Cells(row + 1, 4).Select
End If
End If
End If
End If
End Sub
所有要调用的模块都是公共的,例如
Public Sub Monday()
'脚本在这里
End Sub
我尝试了各种方法,从应用程序运行,模块名称 = "here",工作表计算然后完全更改脚本以适用于工作表计算。我陷入困境。所有模块都按预期工作,因为我可以手动运行它们并且它有效。是的,我也尝试过"Call Monday.Monday"。
**更新**,修复了错误。仍然没有运行任何模块。还更新了脚本以监视合并的单元格【图片说得通透】【1】【2】
英文:
So I been banging my head trying to get this to work, it just don't seem to want to call a module when worksheet detects a change. I know it's detecting changes because this error pops up as soon as change is detected. 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
All of the modules to call are public, example
Public Sub Monday()
'script here
End Sub
I've tried a host of things from application run, modules_name = "here", worksheet_calculate then change the script completely to work for worksheet_calculate. I am stumped. All modules are working as intended because I can manually run them and it works. Yes, I did try "Call Monday.Monday" as well.
Update, fixed errors. Still, no modules are being run. Also updated the script to watch for merged cell Pictures are worth 1000 words
答案1
得分: 0
我已经截取了你的模块和程序应该看起来的截图。就像评论中解释的那样,但是根据(不仅仅是)中国人的说法,一图抵千言:
如你所见,模块的调用方式并不重要,重要的是过程的名称
英文:
I've made a screenshot of what your module and procedures should look like. It's the same as explained in the comments, but according (not only) to the Chinese, a picture says more than a thousand words:
As you see, it does not matter how to call your module, it's the names of the procedures which matter
答案2
得分: 0
谢谢大家,我的问题是因为Excel的工作方式。如果它针对的是具有日期/时间格式的单元格,Target.Value不是正确的方法。需要使用Isempty(Range("cell"))
来处理这个问题。
If Target.Value <> "" And Cells(Target.Row, Target.Column - 1).Value <> "" Then
'问题出现在这里,当它不运行我调用的脚本时
英文:
Thanks folks, my issue was because of how excel works. If it's targeting a cell with date/time format. Target.Value isn't the right way to approach this. Need to use Isempty(Range("cell"))
for this approach.
If Target.Value <> "" And Cells(Target.row, Target.Column - 1).Value <> "" Then
'This is where the issue starts when it's not running the scripts im calling out
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论