VBA trouble - Hiding and un-hiding worksheets in excel

[HOLLYJ]

I have some code that hides and unhides a worksheet in excel. The table below is an example of my data. I have yes and no option buttons that open and close other worksheets depending on the answer. Yes would open a worksheet and no would close it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Geotechnical[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]Investigations[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]shallow foundations[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]roads[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

The code I have so far is quite simple and just hides and unhides the worksheets(see below)

Private Sub no2_investigations_Click()
Worksheets(1).Visible = True
Worksheets(2).Visible = True
Worksheets(3).Visible = False
End Sub

My problem is that if I click no for investigations I still want the worksheet to stay open if for example roads is selected as yes.

Is there code that can help me?

I am fairly new to VBA any help will be greatly appreciated, also I hope my post makes sense.

 

[JoeMo]

I have some code that hides and unhides a worksheet in excel. The table below is an example of my data. I have yes and no option buttons that open and close other worksheets depending on the answer. Yes would open a worksheet and no would close it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Geotechnical[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]Investigations[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]shallow foundations[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]roads[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

The code I have so far is quite simple and just hides and unhides the worksheets(see below)

Private Sub no2_investigations_Click()
Worksheets(1).Visible = True
Worksheets(2).Visible = True
Worksheets(3).Visible = False
End Sub

My problem is that if I click no for investigations I still want the worksheet to stay open if for example roads is selected as yes.

Is there code that can help me?

I am fairly new to VBA any help will be greatly appreciated, also I hope my post makes sense.

If I paraphrase your request to: "I don't want to hide Investigations if any other worksheet in the workbook is visible" - would that be correct?

 

[HOLLYJ]

If I paraphrase your request to: "I don't want to hide Investigations if any other worksheet in the workbook is visible" - would that be correct?

Hi JoeMo

I'm not sure. I am having trouble explaining my problem without being able to attach my workbook. The sheet is almost like a questionnaire we need either a yes or no to each question. For example if I press yes for investigations a worksheet called "operations" opens and if i then click no it closes. But then if i click no for another category e.g. Roads I need that worksheet "operations" to stay open. Does this help explain more?
regards

 

[JoeMo]

Hi JoeMo

I'm not sure. I am having trouble explaining my problem without being able to attach my workbook. The sheet is almost like a questionnaire we need either a yes or no to each question. For example if I press yes for investigations a worksheet called "operations" opens and if i then click no it closes. But then if i click no for another category e.g. Roads I need that worksheet "operations" to stay open. Does this help explain more?
regards

Not really. Seems you need to develop a bit more code around your buttons to tell Excel what you want to do with sheets other than the one whose button you select. How would I know that if I select Yes for investigations button that a sheet called operations becomes visible? From your OP it seemed that each sheet had its own set of buttons which determined independently if the sheet was visible or not.

 

[HOLLYJ]

Not really. Seems you need to develop a bit more code around your buttons to tell Excel what you want to do with sheets other than the one whose button you select. How would I know that if I select Yes for investigations button that a sheet called operations becomes visible? From your OP it seemed that each sheet had its own set of buttons which determined independently if the sheet was visible or not.

Thanks for trying. Sorry like I said I'm finding it hard to explain my problem without being able to upload my worksheet. Ill try elsewhere.

 

[JoeMo]

Thanks for trying. Sorry like I said I'm finding it hard to explain my problem without being able to upload my worksheet. Ill try elsewhere.

You can put your workbook on a free site like DropBox and grant access to it, then post the link here. Personally, I don't download from such sites, but there are folks on this forum who do and can probably help you.