How to start a macro when the dropdown list value is changed

[Vanrob]

Good morning guys,


I am stuck at one point.
I have a cell (A1) where I put a dropdown list with 2 choices (simply made by a Data Validation). And I want to run my macro when I change the value of this cell (ON / OFF).
If I change something else on the sheet/workbook, I don't want to run it.

So, basically, I have this code in "ThisWorkbook"

Private Sub Workbook_Open()
Shift
End Sub

And here is what's inside my Module1

Sub Shift()
If ActiveSheet.Range("A1").Value = "OFF" Then
Cells.EntireRow.Hidden = False
Rows("71:139").EntireRow.Hidden = True


Else
Cells.EntireRow.Hidden = False
Rows("71:139").EntireRow.Hidden = False


End If
End Sub

The macro is working, it's doing what I want but it seems that it's starting the macro each time that I click on a cell. Up to know, it's not really a big issue as it slow down the file for a few ms (maybe?) but they sould be a way to do it... Especially for longer code with much more macros.

I've tried differents solution I found on the forum (If Intersect(target, Range("A1")) Is... // If target.Address = "A1"// ...) but I couldn't find the right one in my case... Or I couldn't make it suitable for me.


Any tips?

Thanks!
Vanrob



PS: Just in case it could be usefull, here is a link to my file
https://www.dropbox.com/s/38zn6k16n37exyz/Vanrob file.xlsm?dl=0

 

[SteveO59L]

try using a worksheet_change event

if target.address=$A$1 then

call.........

 

[Vanrob]

Hi SteveO59L,

Thanks for your answer.
I've tried something similar. And thit it again but it looks like it's still charging the macro for each clic.

I replace my old code in ThisWorkbook:

Private Sub Workbook_Open()
Shift
End Sub

By

Private Sub Worksheet_Change(ByVal target As Range)
If target.Address = "$A$1" Then
Call Shift
End Sub

But it doesn't change anything. My screen is still shortly blinking as if it was running the macro.

 

[SteveO59L]

this works for me

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 And Target.Value = "No" Then
Rows("20:29").EntireRow.Hidden = True
Else
Rows("20:29").EntireRow.Hidden = False
End If

End Sub

 

[Vanrob]

Ok, almost got it!
Your way to select the cell seems to have solved a part of my problem

It's working but only if I put my code in the Sheet1, not if it is in ThisWorkbook.
So if I have 10 sheets with the same action, do I have to copy it for each page? As there is no Workbook_Change.

 

[Vanrob]

Problem noticed by playing a bit with the file...

If I make a copy/paste on some merged cells (let say, line 65) above the ones which is suppose to use the macro (1st one), I have an error.
"Run-time error '13':
Type mismatch"

It enlight the line "If Target.Column = 1 And Target.Row = 1 And Target.Value = "No" Then" in the VB editor
How comes that this have an impact on the others cells?


Edit:
Constatations:
- It only bug when something is done on merged cells, writing something inside is ok. But copy/paste or delete not.
- If the code is inside "ThisWorkbook" in the editor, it's ok... but it's only working for 1 sheet. If it's inside each sheet, bug.

 

[Vanrob]

Got it! The solution was:

Inside each sheet:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Row = 1 Then
    Call HideAndUnhide
End If
End Sub

Create a new module and put the code:

Sub HideAndUnhide()

With ActiveSheet
    If .Cells(1, 1).Value = "ON" Then
        Rows("74:143").EntireRow.Hidden = True
    Else
        Rows("74:143").EntireRow.Hidden = False
    End If
End With


End Sub

And it solve this Run-time error '13'
There is still a small code on each page but it's running smoothly

Thanks for your help