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

**chobson** · 05-19-2011, 08:50 AM

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?

Please Login or Register  to view this content.

**romperstomper** · 05-19-2011, 09:14 AM

Please Login or Register  to view this content.

should work.

**Domski** · 05-19-2011, 09:15 AM

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

Dom

**chobson** · 05-19-2011, 09:44 AM

Originally Posted by romperstomper

Please Login or Register  to view this content.

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.

**chobson** · 05-19-2011, 09:45 AM

Originally Posted by Domski

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!

**Whizbang** · 05-19-2011, 09:48 AM

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

**Domski** · 05-19-2011, 09:52 AM

You have this twice in both sets of code:

Please Login or Register  to view this content.

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

Dom

**chobson** · 05-19-2011, 09:53 AM

Originally Posted by Whizbang

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

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** · 05-19-2011, 10:18 AM

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** · 05-19-2011, 10:40 AM

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!