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

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 date1( 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

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

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)

"gr8guy" wrote in message
...
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 date1( 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

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

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



"Bob Phillips" wrote in message
...
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)

"gr8guy" wrote in message
...
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 date1( 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

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

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)

"gr8guy" wrote in message
...
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



"Bob Phillips" wrote in message
...
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)

"gr8guy" wrote in message
...
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 date1( 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