调用模块的Excel工作表

rryofs0p  于 2023-03-13  发布在  其他
关注(0)|答案(2)|浏览(133)

所以我一直在努力让它工作,当工作表检测到更改时,它似乎不想调用模块。我知道它正在检测更改,因为一旦检测到更改,这个错误就会弹出。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

Said a wise man

i1icjdpr

i1icjdpr1#

我做了一个截图,你的模块和程序应该是什么样子的,和评论里解释的一样,但是根据(不仅仅是)中国人的说法,一张图说了一千多个字:

正如你所看到的,如何调用你的模块并不重要,重要的是过程的名字:-)

mv1qrgav

mv1qrgav2#

谢谢大家,我的问题是因为excel的工作方式。如果它的目标是一个日期/时间格式的单元格。目标。值不是正确的方法来处理这个问题。需要使用Isempty(Range("cell"))来实现这个方法。

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

相关问题