VBA code to hide/unhide worksheets

**holli** · 10-25-2015, 08:58 PM

My sheet is like a questionnaire the user needs to tick yes or no for each topic. Some of the topics require further information so in these cases other worksheets will appear if yes is ticked.

Some of the sheets require the same sheet to open if yes is ticked. For example 'investigations' and 'dispersive soils' both open the 'site details' worksheet if yes is ticked. My dilemma is that if no is ticked for 'dispersive soils' then I don't want 'site details' to close as it is ticked yes for 'investigations'. For any of the topics I don't want sheets to disappear if they are ticked yes elsewhere.

I have attached my spreadsheet for reference.

I have had help with the code below and used it in my spreadsheet the best I can but cant get it to work quite how I want . Can anyone suggest where I might be going wrong.

Please Login or Register  to view this content.

test 2 (3).xlsm

**Greg M** · 10-26-2015, 11:34 AM

Hi there,

I can't test your workbook as I don't have a 64-bit system installed and therefore cannot create a Reference to Windows\SysWOW64\mscomct2.ocx.

I think the basic problem is that your code is taking actions on the basis of the Click event for each individual OptionButton, and therefore the code associated with an individual OptionButton has no way of "knowing" the worksheet requirements associated with OptionButtons which have been selected/unselected already. Perhaps a better approach would be to call the SAME routine from EACH of the various OptionButton_Click routines, immediately review the selected/unselected states of ALL OptionButtons, and then hide/unhide worksheets on the basis of the current states.

Hope this is (a) understandable, and (b) helpful - please let me know how you get on.

Regards,

Greg M