# [SOLVED] Run a macro every time a cell change it's value



## barcas84 (Nov 2, 2010)

Hello everybody!

I have this simple macro:


Sub CleatsMessage()
Dim cellValue As Integer
cellValue = Range("E117").Value
If cellValue = 1 Then
MsgBox ("Hello World")
End If
End Sub


And I need a macro to run this one, everytime the value of Sheet1, cell C108 changes... I do have to say that there is a dropdown list in that cell, so I don't know if that would affect the macro...

Thanks!!!!!


----------



## macropod (Apr 11, 2008)

*Re: Run a macro every time a cell change it's value*

Hi Barcas84,

Put the following code in the corresponding worksheet module:

```
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$108" Then Exit Sub
If Range("E117").Value = 1 Then MsgBox ("Hello World")
End Sub
```
_Edit: Fixed cell reference_


----------



## barcas84 (Nov 2, 2010)

*Re: Run a macro every time a cell change it's value*

Hi Paul, thanks for your answer!!!

But is not working... I put the code you gave me in the worksheet module, changing $C$118 for $c$108 which is the correct cell and nothing happened...

I closed the workbook and re-open it and whenever there is a change in C108 and E117 = 1, there is no message coming up...


----------



## macropod (Apr 11, 2008)

*Re: Run a macro every time a cell change it's value*

Hi Barcas84,

Did you put the code in the correct worksheet module, or in an ordinary code module?


----------



## barcas84 (Nov 2, 2010)

*Re: Run a macro every time a cell change it's value*

Hi Paul,

No, I put it in the correct worksheet module...


----------



## RSpecianjr (Jan 20, 2010)

*Re: Run a macro every time a cell change it's value*

Hey barcas84,

Welcome back! haha Can you post the workbook for us to look at?

Regards,

Robert D. Specian Jr.


----------



## barcas84 (Nov 2, 2010)

*Re: Run a macro every time a cell change it's value*

Hey Robert!!

No, I can't, you know it's too big even if I zip it...


----------



## RSpecianjr (Jan 20, 2010)

*Re: Run a macro every time a cell change it's value*

Hey,

Same workbook huh? haha That thing must be huge now!!

I'm surprised the code above doesn't work... = / Let's try and adapt it:



> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.address = "$C$118" and Range("E117").Value = 1 Then
> MsgBox ("Hello World")
> Else
> ...


Is the dropdown list an active x box, or is it a data validation? If it's active X then it isn't in cell C118.

Regards,

Robert


----------



## barcas84 (Nov 2, 2010)

*Re: Run a macro every time a cell change it's value*

Hey Robert!!! Yeah the workbook is getting f... huge!!!!

This macro works perfect!!!! Thanks a lot my friend!!!!!!

Marco


----------



## barcas84 (Nov 2, 2010)

Hey Robert,

There is another macro that also starts with

Private Sub Worksheet_Change (ByVal Target As Range) so it says ambiguous sub name... Can I just change the name of that, or what should I do?


----------



## RSpecianjr (Jan 20, 2010)

Hey Marco,

If the other macro was to try and do the same thing, just remove it. For it to run correctly under the event, unless there is some way to determine between the two, you would have to run them both as one macro.

Regards,

Robert D. Specian Jr.


----------



## barcas84 (Nov 2, 2010)

Hey Robert,

They other one what it does is protect the hyperlinks to being change or removed and it opens a window that says you can't alter this cell... Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
If bHLink = False Then Exit Sub
With Application
.EnableEvents = False
.Undo
MsgBox "You can't alter cell(s):" & vbCr & strAdd, vbExclamation + vbOKOnly
.EnableEvents = True
End With
End Sub


----------



## RSpecianjr (Jan 20, 2010)

Hey Marco,

Try something like this:



> Private Sub Worksheet_Change(ByVal Target As Range)
> If bHLink = False Then
> If Target.address = "$C$118" and Range("E117").Value = 1 Then
> MsgBox ("Hello World")
> ...


----------



## barcas84 (Nov 2, 2010)

That's awesome man, it works perfect!

Thanks a lot!!!!!

Marco


----------

