VBA to hide and unhide ranges in multiple sheets.

batkosta

New Member
Joined
May 27, 2005
Messages
33
Afternoon everyone,

In my workbook I've got 15 sheets, of which 12 contain monthly data. In each sheet columns contain data about days in the corresponding month. Row 1 of each sheet contains the dates. I've got an userform where the user enters a date and all columns are being hidden except for the first one, the one which corresponds to the date entered and the one next to it. Here is the click event:

Private Sub cmdOK_Click()

Dim X As Object
Dim Dt As Date
Dt = CDate(txtDate.Value)

For Each X In Range("B1:HR1")
If IsDate(X.Value) = True Then
If CDate(X.Value) = Dt Then
Range("B1:IV1").EntireColumn.Hidden = True
Range(X.Address).EntireColumn.Hidden = False
Range(X.Address).Offset(0, 1).EntireColumn.Hidden = False
End If
End If
Next X
frmSelectDate.Hide
End Sub


The problem is that this code only hides columns if the date entered falls in the active month (active sheet). I would like the code to work for any date of the year and to enable users to enter data for the relevant date only. I thought of the following:

Sub cmdOK_Click()

Dim X As Object
Dim Dt As Date
Dim ws As Worksheet

For Each ws In Worksheets
Select Case ws.Name
Case "JAN", "FEB", "etc"
Dt = CDate(txtDate.Value)
For Each X In ws.Range("B1:HR1")
If IsDate(X.Value) = True Then
If CDate(X.Value) = Dt Then
ws.Range("B1:IV1").EntireColumn.Hidden = True
ws.Range(X.Address).EntireColumn.Hidden = False
ws.Range(X.Address).Offset(0, 1).EntireColumn.Hidden = False
End If
End If
Next X
Case Else
' do nothing
End Select
Next ws
frmSelectDate.Hide
End Sub

This works, but again, only if the date is on the active sheet.

Any suggestions well appreciated.


Many Thanks,
K.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,118,821
Messages
5,574,515
Members
412,599
Latest member
Schu94
Top