Hide/Unhide Columns representing dates as per Current System Date!

G

gr8guy

Hi,

If i have 31 columns having 31 days ( in mm/dd/yy format - made into text) &
i want to hide the columns showing current date - 1 & i also want to hide
the columns showing current date>1( i.e. before midnight), & just show the
column showing current date as Visible, how do i do that on opening the
workbook? Also, since i am working night shifts, after 12:00 midnight ,there
should be 2 columns available that is the previous current date column for
some time say till next 12 hours & the new column which became visible only
after midnight.

How is it possible using VBA?

Rgds,

Eijaz
 
B

Bob Phillips

Elijaz,

Here's some workbook open code

Private Sub Workbook_Open()
Dim iDay As Long
With Worksheets("Sheet1")
.Columns("A:AE").Hidden = True
iDay = Day(Date)
.Columns(iDay).Hidden = False
If Hour(Now) < 12 And iDay > 1 Then
.Columns(iDay - 1).Hidden = False
End If
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

gr8guy

Thanks Bob,

Works Perfect! Exactly what i needed!
wanted to ask you one thing though! If there are checkboxes (using forms
toollbar) in the hidden columns, would they be visible completely or the
last column AE checkboxes will be visible on column AF, which i dont want to
happen.

how to avoid this using code i.e. how to Hide the Checkboxes along with
their respective columns & make only current day checkboxes visible as per
your code?

Rgds,

Eijaz
 
B

Bob Phillips

Elijaz,

Assuming you are using checkboxes form the control toolbox, you can hide
them with

ActiveSheet.OLEObjects("Checkbox1").Visible = False

and show them with

ActiveSheet.OLEObjects("Checkbox1").Visible = True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top