Excel Worksheet to Call Modules

Question

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

Said a wise man

Monday is the name of the Module as well as the procedure? :) You can't do that. Rename the Module to something like `ModMonday` and keep the procedure name as `Monday`. Also since you are working with `Worksheet_Change`, you may want to see [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) — Siddharth Rout, Mar 09 '23 at 06:10
Yup, so I changed the Monday module to ModMonday. It's now giving me a new error. "User-defined type not defined". This is really getting on my nerves right now ughhhh. I am just about to go back to a simple button to call, it worked that way. — Shiwoon Yi, Mar 09 '23 at 06:58
Alright, so changed Modules procedure names to MondayCalc. So it looks like this `Public Sub MondayCalc()`. Now it dont give me any errors but It doesn't call the Modules to run the scripts.. yes, I changed the worksheet changed to `Call ModMonday.MondayCalc`. Hrmmmm — Shiwoon Yi, Mar 09 '23 at 07:11
You have a typo: Call `Thrusday` should be `Call Thursday`. You also need to rename the other modules. Then we need to see what the procedures do. If they are similar, then just post the codes for Monday and Tuesday. — VBasic2008, Mar 09 '23 at 07:18
Yup I noticed that while I was correcting some stuff, thanks for pointing that out though. But yeah, I did rename the other modules when I renamed the monday module. They pretty much do the same thing, has all the same dim (no custom libraries). Just the start and end of the modules are different because of the `startTime = Range("D10").Value`. I've gotten to the point of no errors but also no modules is auto exec when column for that row is filled/changed. The modules will run when manually running it or assigned to a button. — Shiwoon Yi, Mar 09 '23 at 08:00
As I said, post one of the procedures so that we can amend the code for the event with a certain accuracy. — VBasic2008, Mar 09 '23 at 09:59

score 0 · Answer 1 · answered Mar 09 '23 at 08:10

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 :-)

answered Mar 09 '23 at 08:10

Dominique

16,450
15
56
112

Maybe Im just dense headed and don't understand, but my modules is exactly like that. Except each `Public Sub NameHere()` to `End Sub` has their own Modules. I've gotten worksheet change to not error now but it wont call the modules. I've even tested it with exactly that, a msg box instead of my scripts. (Korean btw) – Shiwoon Yi Mar 09 '23 at 08:33
Your "Error picture" looks quite different than my screenshot. Can you show (edit your question) what your module/procedures currently look like? – Dominique Mar 09 '23 at 08:35
Yup, just did that. Sorry about the confusion lol – Shiwoon Yi Mar 09 '23 at 08:43
I see that you are including the name of the module when you call the procedures, like `Call ModMonday.MondayCalc`, while VBA does not need the name of the module in order to find the procedure (I know, this is weird indeed), so replacing it by `Call MondayCalc` should do the trick. – Dominique Mar 09 '23 at 08:51
So, changed `Call ModMonday.MondayCalc` to `Call MondayCalc`. Still no modules are running when meeting the criteria of `If Target.Value <> "" And Cells(Target.row, col - 1).Value <> "" Then` I guess I am doomed to eternal button command to run modules. – Shiwoon Yi Mar 09 '23 at 09:10
I'm sorry, I don't see what more I can say. I would advise you to close the other Excel workbooks, and try to get something to work, step by step, by recreating the whole thing again (don't forget to copy your code, so you can easily get it back). – Dominique Mar 09 '23 at 09:18

Shiwoon Yi · Accepted Answer · 2023-03-12T00:21:28.743

0

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

edited Mar 12 '23 at 00:21

answered Mar 12 '23 at 00:20

Shiwoon Yi

1
4

Excel Worksheet to Call Modules

2 Answers2