VBA issue. Runs on each tab

[shophoney]

Hi,

Below is VBA i would like to run when one of my sheets changes.

But when I refresh all it seems to run on each tab.

As the VBA will only work on one of the tabs. It gives a debug error.

How can i limit the VBA to run on a specific tab only.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Range

    Columns("D:O").Select
    Selection.EntireColumn.Hidden = False
   
    For Each c In Range("D1:L1").Cells
        If c.Value = "0" Then
            c.EntireColumn.Hidden = True

            'You can change the property above to False
            'to unhide the columns.
        End If
    Next c

End Sub

 

[mole999]

how about just changing this Columns("D:O").Select to sheets("sheetname").Columns("D:O").Select

 

[jasonb75]

The code will only run on the tab for the module in which it is located, however if an action on another sheet results in a change being made on the sheet with the code then it will still run. In such cases, any references to ranges that are not qualified with the sheet name will be taken from the active sheet not the sheet with the code.

 

[Fluff]

That code should only run on one sheet, but if the sheet is being changed whilst it's not the active sheet you will get an error because of the Select.
Try

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Range

Columns("D:O").EntireColumn.Hidden = False

For Each c In Range("D1:L1").Cells
If c.Value = "0" Then
c.EntireColumn.Hidden = True

'You can change the property above to False
'to unhide the columns.
End If
Next c

End Sub

 

[shophoney]

Thanks for the quick reply.

I think the next issue is sometimes the value it is evaluating is 0 other times it’s a null.
How can I have it look at both to decide to hide the column.
Thanks for helping.

 

[Fluff]

If it's a zero is it text or a number?

 

[shophoney]

If it's a zero is it text or a number?

Sorry for not being clear.
It’s evaluating a pivot table. So “0”, if formatted the table to put a blank when 0 to look cleaner in a printout.
Thanks.

 

[Fluff]

Try

If c.Value = "0" Or c.Value = "" Then

 

[shophoney]

Try

If c.Value = "0" Or c.Value = "" Then

Thanks will give it a try.
Appreciate the help