Macro not working correctly when called from a button

[wtom0412]

Hi All,

I have a macro that is misbehaving when it is called by a button.

I have the following code...

Sub HideColumns()

Application.ScreenUpdating = False 


Range("I8:AL8").EntireColumn.Hidden = False ' Unhides all columns

' Search through the range and hide columns where cell in row 8 = zero
    For Each d In Range("I8:AL8").Cells
        If d.Value = "0" Then
            d.EntireColumn.Hidden = True

        End If
    Next d
    
Application.ScreenUpdating = True

End Sub

If I run it from within developer using F5 or step through it with F8, it works as it should and hides all of the columns it should (I.E., any column between I and AL that has a value of zero in row 8 is hidden).

When I call this macro from a button, it won't hide Column I, even though the value in I8 = zero.

For the life of me, I can't work out what's wrong!!

I have tried using a form control and an ActiveX button and neither will run the code correctly.

Any help would be greatly appreciated.

Cheers

WT

 

[steve the fish]

I cant replicate that. If I8 = 0 then running your code from a button works for me. Are you sure I8 is exactly 0?

 

[wtom0412]

Hi Steve, thank you for your quick reply.

Your question makes perfect sense, that was the first thing I looked at!!

I assigned all of the cells in row 8 it to be general (no specific format).
The value in row 8 is derived from a formula - =IF(Rates!O4="Y",Rates!I4,"0") - as are the rest of the columns which work.
I used Clear All Formats that the formula refers to (Rates!O4)
I even extended the range from I8:AL8 to H8:AL8 thinking that it is ignoring the first column in the loop.

As I said previously, I have tried an ActiveX button, a forms control, and I even assigned the macro to a shape!! I also tried a toggle button just before you replied.

AND, I tried it on a new blank worksheet and it still did the same thing!!

I am really stumped.

When you ran it, did the code work as expected from the button?

Cheers, WT

 

[steve the fish]

What does this produce?

=I8+0=0

 

[dmt32]

Hi,
try this update to your code:

Sub HideColumns()
Dim d As Range
Application.ScreenUpdating = False


' Search through the range and hide columns where cell in row 8 = zero
    For Each d In Range("I8:AL8").Cells
        d.EntireColumn.Hidden = CBool(d.Value = 0)
    Next d
    
Application.ScreenUpdating = True


End Sub

Dave

 

[wtom0412]

Should that go into I8 on the test sheet?

If so, I get a Circular Reference warning and it still doesn't work.

 

[steve the fish]

No its to test if cell I8 really does equal 0 so can be placed in any cell. Is =IF(Rates!O4="Y",Rates!I4,"0") the exact formula in I8? If so what does O4 equal and what does I4 equal?

 

[wtom0412]

Of course, sorry, dumb moment.

When I placed it on the original sheet, I got True
When I placed it on the test sheet, I got zero
When I related it to Rates!O4 I got True

 

[steve the fish]

No merged cells about is there? What do these messageboxes give?

Application.ScreenUpdating = False
Range("I8:J8").EntireColumn.Hidden = False ' Unhides all columns
' Search through the range and hide columns where cell in row 8 = zero
    For Each d In Range("I8:J8").Cells
        MsgBox d.Column & ", " & d.Value
        If d.Value = "0" Then
            d.EntireColumn.Hidden = True
        End If
    Next d
    
Application.ScreenUpdating = True

 

[wtom0412]

I ran it from a button and from Developer (F5), and they both gave me 9,0 and 10,0