Macro to hide sheet based on cell value within the sheet

[a_dub_green]

Hello. I see that there are various posts related to my query, but I'm a novice when it comes to macros, and I'm not able to effectively use the code posted for someone else and modify it to my purposes, so I must apologize if my problem seems redundant.

I'm trying to make a simple macro that will hide a sheet if the value in C1 is "True" based on a logic function that exams whether the date in C3 is equal to today's date. I would like this run when the sheet is opened if possible. I have the below code, but it doesn't seem to be working.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
If Not ws.Range(C1) = "True" Then ws.Visible = False
Next ws
End Sub


Any help would be greatly appreciated. Thanks!

[tom1977]

hi try use such code

Please Login or Register  to view this content.

p.s. the "True" in code is not equal to True as the result of logical function from cell so I put the logical equitation into code

[tom1977]

cancelled post

[a_dub_green]

hi try use such code

Please Login or Register  to view this content.

p.s. the "True" in code is not equal to True as the result of logical function from cell so I put the logical equitation into code

Hi Tom1977,

Thanks for the quick reply. I put this code in the VBA editor, saved and closed the file. However, when I re-open the file, all of the tabs are showing, not just the ones where C1=True. Any thoughts on what I'm doing wrong? Thanks again!

[tom1977]

look at attachment and tell me is it ok? if not put your description on my file. And remember that you can’t hide all sheets or workbook.

[a_dub_green]

I opened the file, and I'm seeing 3 tabs. Essentially, what I'm trying to accomplish is to have only one tab visible at a time, and that tab would be "today's" tab. I'm attaching my file because I don't think I've done a very good job of explaining. Again, thanks so much!

[tom1977]

Hi

First You choosed wrong place to insert the code: You should put this into Thisworkbook module
Second I changed code to this:

Please Login or Register  to view this content.

with this You do not need any value in C1

[a_dub_green]

I think this is close, but instead of only showing today's tab, it's only hiding today's tab. Thanks!

[tom1977]

so change this
If ws.Range("C2") = Date
into this
If ws.Range("C2") <> Date

[a_dub_green]

It worked! Thank you so much! I know that this was probably frustrating for you, but I really appreciate all of your help and patience!

[tom1977]

Do not worry I am not frustrated good luck with VBA

[a_dub_green]

Hi again,

I'm sorry to bother you, but when I opened the file this morning to make sure that yesterday's sheet would hide, and today's would open, it got a run time error of 1004. When I go in to VBA, it appears that the problem is in the "ws.visible=false" line, as it is highlighted in yellow. Do you have any thoughts on what would be causing that error?

Thanks so much!

[tom1977]

Are you sure at my computer is ok
which line is indicated as error?

[a_dub_green]

Are you sure at my computer is ok
which line is indicated as error?

It's working fine now. Sorry! But I do have another question. It came up today that people would like to see all the tabs for the coming week, so it would be a rolling 7 days worth of tabs showing. Is that possible to accomplish that by adjusting the code you've already given me? Again, thank you so much!

[a_dub_green]

Hi again Tom1977,

I'm getting the debut error when I try to open the file and have the macro run instantly. It's saying that the "ws.visible=False" line isn't working right. Any thoughts?

Thanks!

[tom1977]

Hi

If you use this version of code

Please Login or Register  to view this content.

and any of C2 (in each worksheet) is not equal to present day (date) then will be the error because macro cannot hide all sheets in workbook. (try to hide by hand all sheets in workbook)

[a_dub_green]

Thanks again! I don't know what's happened. I have a "Summary" tab where the formula in C2 is "=today()", so that tab should always be visible (I would think). It's working again now. However, I was wondering if you had any ideas on how I could show tabs based on a rolling date. For example, I'd want to show today's tab and all the tabs for the next 7 days. Then tomorrow, I'd like today's tab to go away and show the next 7 days worth of tabs. I thought I could do this by adding more logic like <> Date + 1, <> Date + 2, etc, but that didn't work. Any suggestions?

Thank you so much!

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[a_dub_green]

Hi

If you use this version of code

Please Login or Register  to view this content.

and any of C2 (in each worksheet) is not equal to present day (date) then will be the error because macro cannot hide all sheets in workbook. (try to hide by hand all sheets in workbook)

Hi Tom1977,

I was wondering if you had a chance to review the question I posed a couple of weeks ago. What I'm need to accomplish is to show tabs for a rolling 7 day period, meaning not just today's but also all tabs for the next week, while hiding the tabs for yesterday and previous. Do you know how I could accomplish this by modifying the code you've already been so kind to provide?

Thanks!