Run VBA one after the other

[KlausW]

Hi every one

I have a challenge; I use a button to run this VBA code.

It works really well. Is it possible to get it to run the code when I type in one of the following cells.

As shown in the second VBA code.

Any help will be appreciated.

Best regards

Klaus W

Sub Rektangelafrundedehjørner4_Klik()

Dim DatRng, Dest As Range
Dim TidCol, TidRow, c  As Integer

 With Sheets("Tilmelding")
    Set DatRng = .Range("C4:C10")
 On Error GoTo Ooops
    TidCol = Application.Match(.Range("A2"), Sheets("Tid").Range("1:1"), 0)
    TidRow = Application.Match(.Range("B4"), Sheets("Tid").Range("C:C"), 0)
 End With
 
For c = 0 To 2
    Set Dest = Sheets("Tid").Cells(TidRow, TidCol).Offset(0, c).Resize(7, 1)
    Dest.Value = DatRng.Offset(0, 2 * c).Value
 Next c
 
Ooops:
 If Not Err.Number = 0 Then MsgBox " Not able to match Initial or Date  -- Please check and try again"
On Error GoTo 0

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Range("A2", "G2")) Is Nothing Then Exit Sub

Dim WkRng, DestRng, SrcRng As Range

Dim TidCol, TidRow, c  As Integer

 With Sheets("Tilmelding")

    Set WkRng = .Range("B4:B10")  'Dates for week number

    Set DestRng = .Range("C4:C10")  'Required qty range

    On Error GoTo Ooops  'Error handler

    'TidCol = first column of initial

    'TidRow = first row of week number

       TidCol = Application.Match(.Range("A2"), Sheets("Tid").Range("1:1"), 0)

       TidRow = Application.Match(.Range("G2"), Sheets("Tid").Range("B:B"), 0)

 End With

Application.EnableEvents = False  'Stop this change event code triggereing itself and looping forever

'change the dates to match week number

WkRng.Value = Sheets("Tid").Cells(TidRow, 3).Resize(7, 1).Value


'Loop using offset to get 3 sets of data from Tid to cols C E G

For c = 0 To 2

    Set SrcRng = Sheets("Tid").Cells(TidRow, TidCol).Offset(0, c).Resize(7, 1)

    DestRng.Offset(0, 2 * c).Value = SrcRng.Value

Next c

 Ooops:  'Error message if there is error.

 If Not Err.Number = 0 Then MsgBox " Not able to match Initial or Week Number  -- Please check and try again"


On Error GoTo 0  'set error handling back to default

Application.EnableEvents = True  're-enable events handling


End Sub

 

[Jeffrey Mahoney]

Just add the name of Rektangelafrundedehjørner4_Klik to the Worksheet_Change SUB

 

[KlausW]

Just add the name of Rektangelafrundedehjørner4_Klik to the Worksheet_Change SUB

 

[KlausW]

Hi, I can't get it to run. The VBA code cannot run 2 x Sub Worksheet_Change() in the same sheet or module.

 

[Joe4]

Hi, I can't get it to run. The VBA code cannot run 2 x Sub Worksheet_Change() in the same sheet or module.

That is correct. You cannot have two procedures with the same name in the made module.
But you don't need that.

You have 2 options:
1. Put ALL the code you need under a single "Worksheet_Change" procedure
- or -
2. Have one "Worksheet_Change" procedure and have another procedure under a different name, and call that one from the "Worksheet_Change" procedure

See here for an explanation and example how to call one procedure from another (it is very easy!)

https://www.wallstreetmojo.com/vba-call-sub/

 

[KlausW]

That is correct. You cannot have two procedures with the same name in the made module.
But you don't need that.

You have 2 options:
1. Put ALL the code you need under a single "Worksheet_Change" procedure
- or -
2. Have one "Worksheet_Change" procedure and have another procedure under a different name, and call that one from the "Worksheet_Change" procedure

See here for an explanation and example how to call one procedure from another (it is very easy!)

https://www.wallstreetmojo.com/vba-call-sub/

Ok, I understand, but how do I put the 2 VBA codes together?

 

[Joe4]

Ok, I understand, but how do I put the 2 VBA codes together?

Very carefully!

Basically, you would have two blocks of code structured like this:

Private Sub procedure_name()

'Code block 1
...code here

'Code block 2
...code here

End Sub

But you have to be careful if you have "Exit Sub", "GoTo", on "On Error" commands.
Otherwise, you could skip over the second block entirely.
So it needs to be well thought out.

By the way, there is nothing wrong with calling one procedure from another. As matter as fact, it is often recommended to prevent your procedures from getting too long and unwieldy.

 

[KlausW]

That is correct. You cannot have two procedures with the same name in the made module.
But you don't need that.

You have 2 options:
1. Put ALL the code you need under a single "Worksheet_Change" procedure
- or -
2. Have one "Worksheet_Change" procedure and have another procedure under a different name, and call that one from the "Worksheet_Change" procedure

See here for an explanation and example how to call one procedure from another (it is very easy!)

https://www.wallstreetmojo.com/vba-call-sub/

Thanks allot it works

 

[Joe4]

You are welcome.
Glad I was able to help!