CODEWITHSUNDEEP
excelvba
AlbertM
AlbertM

Reputation: 13

Pass value from worksheet to Module

I have this in my worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim D_KeyCells As Range

    Set D_KeyCells = Range("D6:D16")

  If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        Call D_CheckRow()


  End If
End Sub

But I need to have this run only when column D is selected and changed.

Because I will need to have several checks depending on what cell column is changed.

So if cell

D6 is changed then call D_CheckRow

E6 is changed then call E_CheckRow

F6 is changed then call F_CheckRow

G6 is changed then call G_CheckRow

ect..

I am sure there is an easier way to write the code so any help is much appreciated.

Update: Goal it to check that once a cell is populated it runs a sub module

Here are the ranges I am checking:

Set D_KeyCells = Range("D6:D16")
Set E_KeyCells = Range("E6:E16")
Set G_KeyCells = Range("G6:G16")
Set I_KeyCells = Range("I6:I16")
Set J_KeyCells = Range("J6:J16")
Set K_KeyCells = Range("K6:K16")
Set L_KeyCells = Range("L6:L16")
Set M_KeyCells = Range("M6:M16")

So if a D cell is changed then run D_CheckRow, If it was an E cell then run E_CheckRow, ect.

Upvotes: 0

Views: 142

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

KISS (pun intended) :D

Code is easier to maintain. Since you have very few checks, you may want to try this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("D6:D16,E6:E16,G6:G16,I6:I16,J6:J16,K6:K16,L6:L16,M6:M16")

    If Not Intersect(rng, Target) Is Nothing Then
        Select Case rng.Column
            Case 4: D_CheckRow
            Case 5: E_CheckRow
            Case 7: G_CheckRow
            Case 9: I_CheckRow
            Case 10: J_CheckRow
            Case 11: K_CheckRow
            Case 12: L_CheckRow
            Case 13: M_CheckRow
        End Select
    End If
End Sub

Also since you are working with Worksheet_Change you may want to see THIS

If you have lot of checks then you can use Application.Run (Split(Target.Address, "$")(1) & "_CheckRow") as @SJR mentioned. But then you should understand how that code works.

Upvotes: 1

SJR
SJR

Reputation: 23081

You could use Application.Run, as in:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim D_KeyCells As Range

Set D_KeyCells = Range("D6:G6")

If Not Application.Intersect(D_KeyCells, Target) Is Nothing Then
   Application.Run (Split(Target.Address, "$")(1) & "_CheckRow")
End If

End Sub

Or use a parameter approach, as per Mathieu's comment, which I have to say is the approach I would be inclined to take if I hadn't gone down this particular rabbit hole.

Upvotes: 2

Related Questions