ActiveX Combobox "Unable to get the Hidden prperty of the range Class"

    Hello, This is my first time posting so I hope I follow all the rules and am looking forward to your all's expertise. I've scoured the internet and many forums to try to resolve many issues and until now I've found the answers to my woes but this problem however has me at my wits end.


    The code is located in the worksheet "Dashboard". It first unhides a named range based on the selection of a Active X combo box in the same sheet then hides a series of other named ranges on the same sheet. 20 named ranges in total, 19 hidden and 1 visible at any giving time and a different chart is displayed within the area that is visible based on selection of the Active X combobox.


    The problem that I am having is that when you select a value from the Active X Combobox and the Change event code runs I get a Run-Time error '1004': Unable to set the Hidden property of the Range Class
    [ATTACH=CONFIG]70498[/ATTACH]


    Using the Debuger it will highlight the first line that refers to the named range to become visible:
    [ATTACH=CONFIG]70499[/ATTACH]


    If I step through the code it still ends in this result but its doing something I don't understand. When it reaches the TRUE case and starts running that part of code it will go past the first range as if it would unhide it then goes back to the beginning of the sub and starts to run as if it detected another Change even for the combobox and fired the code again. Once it gets back down to the TRUE case it bugs out.


    Things I have tried to fix this based off my so far fruitless search for a solution:


    • Ive made sure my workbook or any worksheets within are NOT protected
    • Ive made sure there are NO COMMENTS IN ANY CELLS that would be hidden (there are no comments at all in the workbook this is running in)
    • Ive tried moving the "ActiveX_ComboBox_Chart_Titles" (named range) to a different sheet
    • Ive made sure there is nothing that is being pushed off the sheet by hiding the cells
    • Ive tried removing all the charts from the "Dashboard" Worksheet and code still behaves the same when selecting from the combobox
    • Ive made sure all my named ranges are spelled correctly
    • Ive tried Option Explicit at the top of the worksheet, although im not entirely sure how this works on sheets but I saw as a suggestion to someone else's problem in module code
    • Ive tried running this as a Private and Public sub
    • Ive tried banging my head against the wall.. ect.


    The code was originally written by me for another workbook that does the same thing and the code works there, ive even made sure all the Properties for the workbook, worksheet and combobox match between the two files.


    One thing I have not tried and am desperately trying to avoid is starting from scratch with a new workbook and recreating the file. The Charts and data feeding them was quite cumbersome to set up.


    Any help with this matter would be greatly appreciated!


    Re: ActiveX Combobox "Unable to get the Hidden prperty of the range Class"


    Welcome to Ozgrid.


    Are those named ranges all definitely on the Dashboard sheet?


    Would it be possible to post the workbook, or a simplified copy of it?

    Re: ActiveX Combobox "Unable to get the Hidden prperty of the range Class"


    Thanks for the welcome! Im pretty sure that they are per the screen shot of the Name Manager. Ive stripped down the Workbook and attached it as well.[ATTACH=CONFIG]70503[/ATTACH]


    I have to say it was a little difficult figuring out how to attach that workbook...


    Thanks again n advance for any and all help!

    Re: ActiveX Combobox "Unable to get the Hidden prperty of the range Class"


    Ok, nothing has jumped out at me. I'll take another look later when I have some time, but in the meantime I am aware that Active X controls are regarded as quite buggy so you might like to try using a combobox from the Form Controls menu (also via the Developer tab). Shout if you are unclear about how to do that.

    Re: ActiveX Combobox "Unable to get the Hidden prperty of the range Class"


    I dont know why this is true but it has something to do with the ActiveX control and the change event. As far as I know you cant prevent ActiveX Controls (Combo Boxes anyway) from firing even using DisableEvents = true. Whats happening is you pick a value from your combo box and the change event fires. Your code reaches the line below from any case


    Code
    DBrng1.EntireRow.Hidden = False


    and causes another change event and a subsequent re-firing of the code. Its on this second instance that something happens to your ranges. I tried explicitly declaring all your ranges and their use in the select case to no avail. The only thing I was able to do to get your code to fire as expected was to create a global variable that I called count. Just before the change event fires count is incremented by 1. I added and if statement to test that count is = 1 and if so fire your code, if not then exit. That got your code to work but StepenR's is the best solution.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login