Multiple sheets should change..

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blommerse@saz.nl - 30 Aug 2007 08:43 GMT

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

Reply to this Message

Bob Phillips - 30 Aug 2007 10:33 GMT

The code is all over the place.

You have with statements with no action and no end with. You seem to be
trying to hide all columns. You refer to a sheet not in your list.

Explain in simple steps what the code should do.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Dear all,
>
[quoted text clipped - 68 lines]
>
> Berry

Reply to this Message

blommerse@saz.nl - 30 Aug 2007 11:13 GMT

Hi Bob,

I'm trying to keep it simple:
In cel C8 in the insertsheet AM you can fill in a number 1, 2 etc till
12

If C8 is 1
- on insertsheet AM has to be visible: row 31, row 32:39 hidden
- on insertsheet PM has to be visible: column A and B, rest off
columns hidden
- on dataprocessing has to be visible: row 23 , row 24:32 hidden
- on dataprocessing has to be visible: row 37:38, row 39:56 hidden
- on list & media has to be visible: row 25, row 26: 33 hidden

If C8 is 2
- on insertsheet AM has to be visible: row 31:32, row 33:39 hidden
- on insertsheet PM has to be visible: column A, B, C and D, rest off
columns hidden
- on dataprocessing has to be visible: row 23:24 , row 25:32 hidden
- on dataprocessing has to be visible: row 37:40, row 41:56 hidden
- on list & media has to be visible: row 25:26, row 27: 33 hidden

If C8 is 3
- on insertsheet AM has to be visible: row 31:33, row 34:39 hidden
- on insertsheet PM has to be visible: column A, B, C, D, E and F,
rest off columns hidden
- on dataprocessing has to be visible: row 23:25 , row 26:32 hidden
- on dataprocessing has to be visible: row 37:42, row 43:56 hidden
- on list & media has to be visible: row 25:27, row 28: 33 hidden

I hope you can make the code more easier for me!
I'm not so good in making or changing codes...

Thanks Bob

Reply to this Message

Bob Phillips - 30 Aug 2007 11:58 GMT

This should do it

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C8" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

nCols = Target.Value * 2
Select Case Target.Value

Case 1
Me.Rows(31).Hidden = False
Me.Rows("32:39").Hidden = True
With Worksheets("InsertSheet PM")
.Columns("A:B").Hidden = False
.Columns("C:C").Resize(, .Columns.Count - 2).Hidden =
True
End With
With Worksheets("DataProcessing")
.Rows(23).Hidden = False
.Rows("24:32").Hidden = True
.Rows("37:38").Hidden = False
.Rows("39:56").Hidden = True
End With
With Worksheets("List & Media")
.Rows(25).Hidden = False
.Rows("26:33").Hidden = True
End With

Case 2
Me.Rows("31:32").Hidden = False
Me.Rows("33:39").Hidden = True
With Worksheets("InsertSheet PM")
.Columns("A:D").Hidden = False
.Columns("E:E").Resize(, .Columns.Count - 4).Hidden =
True
End With
With Worksheets("DataProcessing")
.Rows("23:24").Hidden = False
.Rows("25:32").Hidden = True
.Rows("37:40").Hidden = False
.Rows("41:56").Hidden = True
End With
With Worksheets("List & Media")
.Rows("25:26").Hidden = False
.Rows("27:33").Hidden = True
End With

Case 3
Me.Rows("31:33").Hidden = False
Me.Rows("34:39").Hidden = True
With Worksheets("InsertSheet PM")
.Columns("A:F").Hidden = False
.Columns("G:G").Resize(, .Columns.Count - 6).Hidden =
True
End With
With Worksheets("DataProcessing")
.Rows("23:25").Hidden = False
.Rows("26:32").Hidden = True
.Rows("37:42").Hidden = False
.Rows("43:56").Hidden = True
End With
With Worksheets("List & Media")
.Rows("25:27").Hidden = False
.Rows("28:33").Hidden = True
End With
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Bob,
>
[quoted text clipped - 30 lines]
>
> Thanks Bob

Reply to this Message

blommerse@saz.nl - 11 Oct 2007 14:36 GMT

Hi Bob,

Thanks for all the work.
I have another sheet called "Creative" which has to change the same
way as other sheets.
IF 1 in B9: Row 1 - 35 has to stay visible, the rest hide
If 2 in B9: Row 1 - 70 has to stay visible, the rest hide
If 3 in B9: Row 1 - 105 has to stay...etc.

Also I have an error in the code.
When I put 1 till 7 in it everything work perfect.
When I put more than 7 (8, 9 10 etc.) is isn't working so good. It
looks like the code thing I put an 7 in it.
Hope I explain it well.

If not...please let me know.

Hope so much you can help.

Regards, Berry

Reply to this Message

Bob Phillips - 30 Aug 2007 12:11 GMT

If the steps are uniform, you could use

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C8" '<== change to suit
Dim nPMCols As Long
Dim nAMRows As Long
Dim nDPRows As Long
Dim nLMRows As Long
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

nAMRows = Target.Value
nPMCols = Target.Value * 2
nDPRows = Target.Value
ndprows2 = Target.Value * 2
nLMRows = Target.Value

With Me.Rows(31)
.Resize(nAMRows).Hidden = False
.Offset(nAMRows).Resize(9 - nAMRows).Hidden = True
End With

With Worksheets("InsertSheet PM")
.Columns(1).Resize(, nPMCols).Hidden = False
.Columns(nPMCols + 1).Resize(, .Columns.Count - nPMCols).Hidden
= True
End With

With Worksheets("DataProcessing")
With .Rows(23)
.Resize(nDPRows).Hidden = False
.Offset(nDPRows).Resize(10 - nDPRows).Hidden = True
End With
With .Rows(37)
.Resize(ndprows2).Hidden = False
.Offset(ndprows2).Resize(20 - ndprows2).Hidden = True
End With
End With

With Worksheets("List & Media").Rows(25)
.Resize(nLMRows).Hidden = False
.Offset(nLMRows).Resize(8 - nLMRows).Hidden = True
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Bob,
>
[quoted text clipped - 30 lines]
>
> Thanks Bob

Reply to this Message

blommerse@saz.nl - 30 Aug 2007 12:37 GMT

Thanks for all fast work Bob, the codes are working perfectly!!

Best regards,

Berry

Reply to this Message