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.
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.