Excel工作表调用模块

huangapple go评论105阅读模式
英文:

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 &quot;Worksheet_Change event fired&quot;
    Dim row As Long
    
    &#39;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 &lt;&gt; &quot;&quot; And Cells(Target.row, Target.Column - 1).Value &lt;&gt; &quot;&quot; Then
            &#39;Both cells in the row have been filled, ask the user if they want to run the script
            row = Target.row
            If MsgBox(&quot;Do you want to calculate the hours for &quot; &amp; row &amp; &quot;?&quot;, vbYesNo) = vbYes Then
                &#39;User clicked Yes, execute the script for the row
                Select Case row
                    Case 10
                        Call Monday
                        MsgBox &quot;Monday has been calculated&quot;
                        
                    Case 11
                        Call Tuesday
                        MsgBox &quot;Tuesday has been calculated&quot;
                        
                    Case 12
                        Call Wednesday
                        MsgBox &quot;Wednesday has been calculated&quot;
                        
                    Case 13
                        Call Thrusday
                        MsgBox &quot;Thursday has been calculated&quot;
                        
                    Case 14
                        Call Friday
                        MsgBox &quot;Friday has been calculated&quot;
                End Select
            Else
                &#39;User clicked No, move to the next row (if it exists)
                If row &lt; 14 Then
                    &#39;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()
   &#39;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

Said a wise man

答案1

得分: 0

我已经截取了你的模块和程序应该看起来的截图。就像评论中解释的那样,但是根据(不仅仅是)中国人的说法,一图抵千言:

Excel工作表调用模块

如你所见,模块的调用方式并不重要,重要的是过程的名称 Excel工作表调用模块

英文:

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:

Excel工作表调用模块

As you see, it does not matter how to call your module, it's the names of the procedures which matter Excel工作表调用模块

答案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(&quot;cell&quot;)) for this approach.

If Target.Value &lt;&gt; &quot;&quot; And Cells(Target.row, Target.Column - 1).Value &lt;&gt; &quot;&quot; Then
&#39;This is where the issue starts when it&#39;s not running the scripts im calling out

huangapple
  • 本文由 发表于 2023年3月9日 14:07:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/75680960.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定