Unable to set the hidden property of the range class - Column Hiding

[chobson]

I am working with the two pieces of code below. One hides rows when the sheet is activated and one hides columns when a combo box is updated. The error shows up when I activate the sheet, but when I debug, the error is referring to the combo box code and specifically the "Cells(1, indx).Columns.Hidden = DoHide" line.

Ideas?

Private Sub ComboBox10_Change()

    Dim indx As Integer, DoHide As Boolean

    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
   
    For indx = 2 To 200
        DoHide = (Cells(1, indx) = 0) And (Cells(1, indx) <> "")
        Cells(1, indx).Columns.Hidden = DoHide
    Next indx
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
    Application.ScreenUpdating = True


End Sub

Private Sub Worksheet_Activate()

    Dim indx As Integer, DoHide As Boolean

    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
   
    For indx = 2 To 200
        DoHide = (Cells(indx, 1) = 0) And (Cells(indx, 1) <> "")
        Cells(indx, 1).Rows.Hidden = DoHide
    Next indx
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
    Application.ScreenUpdating = True

End Sub

[romperstomper]

Cells(1, indx).EntireColumn.Hidden = DoHide

should work.

[chobson]

Cells(1, indx).EntireColumn.Hidden = DoHide

should work.

Thanks Robert...hmmmm. That didn't seem to work. Got the exact same error.

Could it be that "indx" and "dohide" are both used by both pieces of code?

Strange thing is that once I'm in the sheet and turn off the debugger, the code that hides the columns works fine (including now that I am using your EntireColumn.Hidden code). When I leave and come back, it would seem that the Worksheet.Activate code is trying to run but the error is showing up in the combo box code.

[Domski]

Do you not mean to unprotect your worksheet at the start of the code?

Dom

[chobson]

Do you not mean to unprotect your worksheet at the start of the code?

Dom

I think it *is* unprotecting the sheet at the start, no? We set everything to False at the end of the code while we're developing the model and then set Contents to True at the end of the code once we have finished the model.

Do you think that unprotect code could be part of the run time issue I'm getting?

Thanks!

[Domski]

You have this twice in both sets of code:

ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False

It doesn't really seem to be doing anything. Not that it's necessarily causing your problem.

Dom

[Whizbang]

Change

    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False

To

    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    ActiveSheet.UnProtect

[chobson]

Change

    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False

To

    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    ActiveSheet.UnProtect

Thank you Whizbang.

That is certainly more elegant protect code (I assume the corollary is ActiveSheet.Protect when I am ready to protect after the code runs?) but unfortunately I'm still getting the run time error when the page activates...

[Whizbang]

Try putting in a breakpoint near the beginning of your Worksheet_Activate event code and then walk through it step by step. I copied the code from your original post exactly into a blank workbook with only a few zeros in row 1 and column 1. I then created a combobox.

The code runs fine both times. So, the only thing I can think of is somehow your combobox10_Change event is triggering when a row is being hidden (???). I really can't make sense of that, but walk through your code and you should be able to see the point at which this occurs.

Edit***

It may be because your Cells(indx, 1) is returning an error of some sort. This then makes DoHide return an error. And then when you try to hide the row or column, you're saying .hidden = Error.

Just a thought.

[chobson]

Your intuition regarding the Cells(indx, 1) returning an error seems to have been the ticket!

I changed the range in the WorkSheet.Activate code from "2 to 200" to "11 to 200" and it gave me the same error, but then when I changed it to "11 to 150" it worked fine!

The strange thing is, I don't really know why. When this has happened in the past, I have gone through the cells and found that there was a letter or string in a cell when the code was expecting a number. This time, there were no offending characters that I could find anywhere...

But, problem solved so we move on! Thanks everyone, with a special shout-out to Whizbang!