VBA issue. Runs on each tab

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
235
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.

VBA Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
how about just changing this Columns("D:O").Select to sheets("sheetname").Columns("D:O").Select
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,448
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

Board Regular
Joined
Jun 16, 2014
Messages
235

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
If it's a zero is it text or a number?
 

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
235

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
If c.Value = "0" Or c.Value = "" Then
 

Watch MrExcel Video

Forum statistics

Threads
1,127,540
Messages
5,625,412
Members
416,101
Latest member
CCoetzee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top