Not Working : Worksheet_Change Event

[anish.ms]

Hi
Request help on the below-
I have to call a procedure on worksheet change as mentioned below. But my problem here is, the value in "D2" is changed from a user form and that is not triggering the worksheet change event. Codes are in two different workbooks.
How to call a procedure based on a change in cell value in WB1 by another procedure in WB2?



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then Call List_Assignments
End Sub




Sub ComboBox1_Change()
BaseWS.Range("D2").Value = ComboBox1.Value
End Sub

Thanks in advance!

[Kenneth Hobs]

I guess maybe something like:

Application.Run "'" & Workbooks(2).Path & "\RunMyMacros.xlsm'!Module1.List_Assignments"

[Paul_Hossler]

Maybe ...

Don't use Worksheet_Change event, and pass the value that you put into D2 as a parameter to ListAssignments




Sub ComboBox1_Change()
BaseWS.Range("D2").Value = ComboBox1.Value
Call List_Assignments (ComboBox1.Value)

End Sub

[anish.ms]

Thanks!
It worked


Sub combb_vertical_Change()
BaseWS.Range("D2").Value = combb_vertical.Value
combb_assignment.Value = ""
Application.Run ("'Time Sheet Data.xlsb'!List_Assignments")
combb_assignment.List = BaseWS.Range("D4:D" & BaseWS.Range("D4").End(xlDown).Row).Value
End Sub

[anish.ms]

Thanks Paul!
I got your point but not sure how to do it.

[anish.ms]

Request if someone can check my codes and suggest if any better way of doing it. Currently it is working, but I'm sure it can be done in a matured way as I have prepared it based on my basic understanding and searching on the web.

There are two workbooks
(1) Time Sheet - Form.xlsm - This will be finally saved as an add-in and will be used by me and my department staffs
(2) Time Sheet Data.xlsb - Used for storing data from the above form. This file will be located in OneDrive.

Thanks in advance!

[Paul_Hossler]

These are the highlights, but search for !!!! in both WB projects. I used !!!! as a marker for lines I changed



'!!!
Call Application.Run("'Time Sheet Data.xlsb'!List_Assignments", combb_vertical.Value)




and



'!!!! 1. Added Thisworkbook
'!!!! 2. Added 'dot' to Rows
'!!!! 3. Added parameter to List_Assignments


'!!!!
Sub List_Assignments(Vert As String)
Dim lastrow As Long, i As Long, x As Long
Application.EnableEvents = False
lastrow = Range("L4").End(xlDown).Row
ThisWorkbook.Sheets("Base").Range("D4:D50").Clear
x = 4
With ThisWorkbook.Sheets("Base")
For i = 4 To lastrow
'!!!!
If (.Cells(i, 12) = Vert) And (.Cells(i, 15) = "Open") Then
.Cells(x, 4) = .Cells(i, 13).Value
x = x + 1
End If
Next i
.Range("D3", .Range("D" & .Rows.Count).End(xlUp)).Sort _
Key1:=.Range("D3"), Header:=xlYes
If x = 4 Then .Range("D4").Value = "No Assignments Found"
End With

Call EndOfListAssignments
Application.EnableEvents = True
End Sub

See if these changes work

[anish.ms]

Thanks a lot Paul for your time and support