Hide sheets based on the computer's date

[raef.qawasmi]

Hello guys

I have one workbook for each month, contains a worksheet for each day (31 sheets) Cell A1 of each sheet is a date cell.

what i need to do is to hide the sheet IF the sheet's date (A1) is not equal the computer's date:

If A1 <> today() then sheet.visible = xlSheetVeryHiden (this line only for clarification)


Thank you in advance

Raef

 

[Hui]

Try this in a Code Module

[pre]

Sub hide_sht()
Dim sht As Worksheet
For Each sht In Application.Worksheets
If sht.Cells(1, 1).Value <> Date Then
sht.Visible = msoFalse
Else
sht.Visible = msoTrue
End If
Next
End Sub

[/pre]

 

[raef.qawasmi]

Its working ,Thank You Very Much But i need run macro every 5 Hour to refresh visible sheet.

 

[raef.qawasmi]

Hello Hui

Thank you so very much, it's exactly what I need, you're amazing.


Raef

 

[Hui]

Raef


Add the following 2 macros to the Code Page for the Workbook object in VBA

[pre]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Hide_Sht", , False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("05:00:00"), "Hide_Sht"
End Sub

and change your Hide_Sht macro to this


Public dTime As Date
Sub Hide_Sht()
Dim sht As Worksheet
dTime = Now + TimeValue("05:00:00") 'Time is Hrs:Min:Sec
Application.OnTime dTime, "Hide_Sht"

For Each sht In Application.Worksheets
If sht.Cells(1, 1).Value <> Date Then
sht.Visible = msoFalse
Else
sht.Visible = msoTrue
End If
Next

End Sub

[/pre]
Change all the Times from 05:00:00 to say 00:02:00 to check it works every 2 minutes etc

Change back once your happy it works

 

[raef.qawasmi]

Hello again


the five hrs thing was not a good idia,

i was trying the first one:

----------------------------------------------

Sub hide_sht()

Dim sht As Worksheet

For Each sht In Application.Worksheets

If sht.Cells(1, 1).Value <> Date Then

sht.Visible = msoFalse

Else

sht.Visible = msoTrue

End If

Next

End Sub

------------------------------------------

but for some reason, it can't be executed every time i change the computer date, it works only once and then it doesn't check the computer date again when I open the workbook for the 2nd time !

if you can help me with this, it will be great, Thanks

 

[raef.qawasmi]

PS. I did the code for the macro auto run

-----------------------------

private sub woorkbook_open

run "myMacro"

end sub

-----------------------------


Again, Thank you


Ra'ef