B
blommerse
Dear all,
I have 4 workbooks:
- insertsheet AM
- insertsheet PM
- Dataprocessing
- List & Media
In insertsheet AM cell C8 you can fill in 1 till 12 with validation.
In the other sheets rows/colums should hide. But I don't get it done
right.
This should happen:
Private Sub Worksheet_Change(ByVal Target As Range)
Const nMAX As Long = 10
Dim nRows As Long
Application.ScreenUpdating = False
With Me
With .Range("C8")
If Intersect(Target(1), .Cells) Is Nothing Then Exit
Sub
nRows = .Value
End With
With Sheets("Insertsheet PM").Range(.Cells(1,
5), .Cells(1, _
.Columns.Count)).EntireColumn.Hidden = True
With Sheets("Insertsheet PM").Range(Cells(1, 5), .Cells(1,
_
nRows * 2)).EntireColumn.Hidden = False
With Sheets("List & Media").Range("25:25")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("Briefing").Range("31:31")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
Application.ScreenUpdating = False
If Target.Address <> "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
.Rows("1:" & .Rows.Count).Hidden = False
Select Case Target.Value
Case 1: .Rows("39:56").Hidden = True
.Rows("24:32").Hidden = True
Case 2: .Rows("41:56").Hidden = True
.Rows("25:32").Hidden = True
Case 3: .Rows("43:56").Hidden = True
.Rows("26:32").Hidden = True
Case 4: .Rows("45:56").Hidden = True
.Rows("27:32").Hidden = True
Case 5: .Rows("47:56").Hidden = True
.Rows("28:32").Hidden = True
Case 6: .Rows("49:56").Hidden = True
.Rows("29:32").Hidden = True
Case 7: .Rows("51:56").Hidden = True
.Rows("30:32").Hidden = True
Case 8: .Rows("53:56").Hidden = True
.Rows("31:32").Hidden = True
Case 9: .Rows("55:56").Hidden = True
.Rows("32:32").Hidden = True
End Select
End With
Application.ScreenUpdating = True
End Sub
Can anybody make this code right>???
Thanks in advanced!!
Berry
I have 4 workbooks:
- insertsheet AM
- insertsheet PM
- Dataprocessing
- List & Media
In insertsheet AM cell C8 you can fill in 1 till 12 with validation.
In the other sheets rows/colums should hide. But I don't get it done
right.
This should happen:
Private Sub Worksheet_Change(ByVal Target As Range)
Const nMAX As Long = 10
Dim nRows As Long
Application.ScreenUpdating = False
With Me
With .Range("C8")
If Intersect(Target(1), .Cells) Is Nothing Then Exit
Sub
nRows = .Value
End With
With Sheets("Insertsheet PM").Range(.Cells(1,
5), .Cells(1, _
.Columns.Count)).EntireColumn.Hidden = True
With Sheets("Insertsheet PM").Range(Cells(1, 5), .Cells(1,
_
nRows * 2)).EntireColumn.Hidden = False
With Sheets("List & Media").Range("25:25")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("Briefing").Range("31:31")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
Application.ScreenUpdating = False
If Target.Address <> "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
.Rows("1:" & .Rows.Count).Hidden = False
Select Case Target.Value
Case 1: .Rows("39:56").Hidden = True
.Rows("24:32").Hidden = True
Case 2: .Rows("41:56").Hidden = True
.Rows("25:32").Hidden = True
Case 3: .Rows("43:56").Hidden = True
.Rows("26:32").Hidden = True
Case 4: .Rows("45:56").Hidden = True
.Rows("27:32").Hidden = True
Case 5: .Rows("47:56").Hidden = True
.Rows("28:32").Hidden = True
Case 6: .Rows("49:56").Hidden = True
.Rows("29:32").Hidden = True
Case 7: .Rows("51:56").Hidden = True
.Rows("30:32").Hidden = True
Case 8: .Rows("53:56").Hidden = True
.Rows("31:32").Hidden = True
Case 9: .Rows("55:56").Hidden = True
.Rows("32:32").Hidden = True
End Select
End With
Application.ScreenUpdating = True
End Sub
Can anybody make this code right>???
Thanks in advanced!!
Berry