Multiple sheets should change..

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
 
B

Bob Phillips

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.

--
HTH

Bob

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

blommerse

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
 
B

Bob Phillips

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



--
HTH

Bob

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

Bob Phillips

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



--
HTH

Bob

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

blommerse

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

Best regards,

Berry
 
B

blommerse

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hiding rows with VBA 9
How can I make this code work? 7
when certain rows are hidden, can I also hide a combobox? 6
Noobee Case Select Question 7
Macro works Macro does not work 4
Help with Autofit Row Height Macros 2
VBA group the number in the Column and Copy to the new worksheet. 0
Array macro question 4

Top