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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,086
Messages
5,857,280
Members
431,868
Latest member
servox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top