3

I cant make my code run in Excel VBA (office 365).

I would like to make this code to run after any cell is changed by user or code:

`

Sub Workbook_SC()

Dim var1 As Double

Application.EnableEvents = False

Worksheets("GSCells").Cells(5, 4).Value = 22

Worksheets("GSCells").Cells(5, 5).GoalSeek Goal:=0, ChangingCell:=Worksheets("GSCells").Cells(5, 4)

var1 = Worksheets("GSCells").Cells(5, 4).Value

Worksheets("GSCells").Cells(10, 5).Value = var1

If var1 < 23 Then
    Worksheets("Best").Cells(24, 11).Value = 23
End If

If var1 > 45 Then
    Worksheets("Best").Cells(24, 11).Value = 45
End If

If var1 >= 23 And var1 <= 45 Then
    Worksheets("Best").Cells(24, 11).Value = var1
End If

Application.EnableEvents = True

End Sub

` I made this code based on info of this forum. I have this code in 'ThisWorkbook' in Excel VBA. Initially, The Sub was named Workbook_SheetChange() but it returned an error ("Procedure declaration does not match description of event or procedure having same name").

This error disapeared after I changed its name to SC(). Dispite that, every time I change any value on any sheet, it just doesn't run.

However, if I go into VBA and press F5, it works fine.

Can someone help?

Many thanks in advance.

JvdV
  • 70,606
  • 8
  • 39
  • 70
user3641311
  • 175
  • 2
  • 11
  • 1
    See [this answer](https://stackoverflow.com/a/44311867/1188513). – Mathieu Guindon Sep 13 '19 at 20:19
  • 1
    Just Curious... Why are you using `Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)` and not `Worksheet_Change(ByVal Target As Range)`? Also when working with the Sheet Change Event, you need to be slightly more careful. You may want to see [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Sep 13 '19 at 23:30
  • Or in short, forget the code for a moment. Can you tell me in "words" what exactly are you trying to achieve? – Siddharth Rout Sep 13 '19 at 23:33

1 Answers1

4

In order to trigger on the SheetChange() event it's important to have the required parameters in the Subroutine's definition:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Any other name, or any other list of parameters will result in an error or no event triggering, both of which you've already experienced.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Many thanks for your help!!! Could you explain what means Sh (=Sheet) and Target (=Range?)? – user3641311 Sep 13 '19 at 19:54
  • 3
    Inside this subroutine you can use those variables. `sh` will hold the Worksheet upon which the change was made and `Target` will hold the range upon which the change was made. You could do the following code inside, for instance: `If Sh.Name = "Sheet1" Then Target.Value = "No Changes Allowed"`. So if the change was detected on tab `Sheet1` then the cell(s) will be set to the value "No Changes Allowed" (which would be really annoying). – JNevill Sep 13 '19 at 19:56