How do I solve this?

Metallo · Jul 6, 2004

Hi,

I've got a WB that includes 10 WS (1 to 10), they all have the same format.
In every WS, there is the possibility to enter a numeric reference to a month and get the corresponding YTD results. For instance, if I want to see the April YTD, I input 4 and all the numbers change accordingly.

My question: How can I link the 10 WS in a way that when I input "4" in say, WS1 all the other WS (2 to 10) change also to 4?
This is necessary in order to get data consistency.

Thank you
Alex

Jim Rech · Jul 6, 2004

Formulas can refer to cells on other worksheets. Enter a formula like
=WS1!A1 in the appropriate cell on each worksheet.

--
Jim Rech
Excel MVP
| Hi,
|
| I've got a WB that includes 10 WS (1 to 10), they all have the same
format.
| In every WS, there is the possibility to enter a numeric reference to a
month and get the corresponding YTD results. For instance, if I want to see
the April YTD, I input 4 and all the numbers change accordingly.
|
| My question: How can I link the 10 WS in a way that when I input "4" in
say, WS1 all the other WS (2 to 10) change also to 4?
| This is necessary in order to get data consistency.
|
| Thank you
| Alex
|

Metallo · Jul 6, 2004

Jim,

What you say is true, however, it doens't solve the problem.
If I link all WS (2 to 10) to WS1 the change will only occur if I change the month in WS1.
What I want to get, is to have a change regardless of the WS you input the number.
For instance, I input the month number in WS3, all the others will also switch to the same number.If I input the number in WS4, again all the other will change, and so on.

I hope this is now clearer.

Thank you
Alex

Jim Rech · Jul 6, 2004

The simplest answer (that is, without a macro) is to group all the sheets
before changing the month. Right click the active sheet's tab and pick
Select All Sheets. Then a change on the active sheet also occurs on all the
others. After the change, you can right click again and pick Ungroup, or
just click any other tab.

--
Jim Rech
Excel MVP
| Jim,
|
| What you say is true, however, it doens't solve the problem.
| If I link all WS (2 to 10) to WS1 the change will only occur if I change
the month in WS1.
| What I want to get, is to have a change regardless of the WS you input the
number.
| For instance, I input the month number in WS3, all the others will also
switch to the same number.If I input the number in WS4, again all the other
will change, and so on.
|
| I hope this is now clearer.
|
| Thank you
| Alex
|
| "Jim Rech" wrote:
|
| > Formulas can refer to cells on other worksheets. Enter a formula like
| > =WS1!A1 in the appropriate cell on each worksheet.
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > | Hi,
| > |
| > | I've got a WB that includes 10 WS (1 to 10), they all have the same
| > format.
| > | In every WS, there is the possibility to enter a numeric reference to
a
| > month and get the corresponding YTD results. For instance, if I want to
see
| > the April YTD, I input 4 and all the numbers change accordingly.
| > |
| > | My question: How can I link the 10 WS in a way that when I input "4"
in
| > say, WS1 all the other WS (2 to 10) change also to 4?
| > | This is necessary in order to get data consistency.
| > |
| > | Thank you
| > | Alex
| > |
| >
| >
| >

Art · Jul 6, 2004

Metallo,

Let's assume that the cell in question is $B$2. Try putting this in the change event for every sheet of interest. If there are some sheets that you do not want to change you'll probably have to check for the names and exclude them inside the "for" loop.

Art

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim mValue As Variant
If Target.Address = "$B$2" Then
mValue = Target.Value
For Each ws In Worksheets
ws.Range("$B$2").Value = mValue
Next ws
End If
End Sub

Bob Phillips · Jul 6, 2004

Alex,

Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
dim oSheet as worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
For Each osheet In ActiveWorkbook.Worksheets
If osheet.Name <> Sh.Name Then
osheet.Range("A1").Value = Sh.rnage("A1").Value
End If
Next osheet
End If

ws_exit:
Application.EnableEvents = True

End Sub

Put the coide in ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo said:
Jim,

What you say is true, however, it doens't solve the problem.
If I link all WS (2 to 10) to WS1 the change will only occur if I change the month in WS1.
What I want to get, is to have a change regardless of the WS you input the number.
For instance, I input the month number in WS3, all the others will also

switch to the same number.If I input the number in WS4, again all the other
will change, and so on.

Bob Phillips · Jul 6, 2004

This line

osheet.Range("A1").Value = Sh.rnage("A1").Value

shouyld read

osheet.Range("A1").Value = Sh.Range("A1").Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo · Jul 7, 2004

Bob,

Your solution works irregularly.
By this I mean that when I input the number (1 to 12, although it accepts higher numbers, maybe you can tell me how to fix this so that it only accepts numbers till 12), sometimes it works and change them in all the other WS, sometimes, instead it just blocks Excel and I have to press CTR+ALT+DEL.
This worries me because having to deliver the application to other users, it can be a problem.
Is it possible a conflict with other codes and macro in the application?
As I mentioned in my previous mail, I have the following code to protect the sheets and guarantee the outlining:

Private Sub Workbook_Open()
'''Enable Outlining navigation and protect everything on the sheet with UserInterfaceOnly.

Sheet2.EnableOutlining = True
Sheet2.Protect , True, True, True, True
Sheet8.EnableOutlining = True
Sheet8.Protect , True, True, True, True
Sheet9.EnableOutlining = True
Sheet9.Protect , True, True, True, True
Sheet10.EnableOutlining = True
Sheet10.Protect , True, True, True, True
Sheet11.EnableOutlining = True
Sheet11.Protect , True, True, True, True
Sheet12.EnableOutlining = True
Sheet12.Protect , True, True, True, True
Sheet13.EnableOutlining = True
Sheet13.Protect , True, True, True, True
Sheet15.EnableOutlining = True
Sheet15.Protect , True, True, True, True
Sheet16.EnableOutlining = True
Sheet16.Protect , True, True, True, True
Sheet17.EnableOutlining = True
Sheet17.Protect , True, True, True, True
Sheet18.EnableOutlining = True
Sheet18.Protect , True, True, True, True
Sheet19.EnableOutlining = True
Sheet19.Protect , True, True, True, True
Sheet20.EnableOutlining = True
Sheet20.Protect , True, True, True, True

End Sub

This is instead the one you gave me with the cell changed to B5 because is the one to input the number.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$B$5" Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name Then
oSheet.Range("B5").Value = Sh.Range("B5").Value
End If
Next oSheet
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hope you can help

Alex

Bob Phillips · Jul 7, 2004

Metallo,

I never locks for me. With some sheets protected it can bomb out, but not
lock out.

Here is an amended version that checks for a value of 1-12, and also unlocks
the sheet before setting B5, and re-protecting.

See if this improves it

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$B$5" Then
With Target
If .Value >= 1 And .Value <= 12 Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name Then
If oSheet.ProtectContents Then
oSheet.Unprotect
oSheet.Range("B5").Value = .Value
oSheet.Protect
Else
oSheet.Range("B5").Value = .Value
End If
End If
Next oSheet
Else
MsgBox .Value & " is an invalid value"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo said:
Bob,

Your solution works irregularly.
By this I mean that when I input the number (1 to 12, although it accepts

higher numbers, maybe you can tell me how to fix this so that it only
accepts numbers till 12), sometimes it works and change them in all the
other WS, sometimes, instead it just blocks Excel and I have to press
CTR+ALT+DEL.

This worries me because having to deliver the application to other users, it can be a problem.
Is it possible a conflict with other codes and macro in the application?
As I mentioned in my previous mail, I have the following code to protect

the sheets and guarantee the outlining:

Metallo · Jul 10, 2004

Bob,

Thank you for your second option.
It works fine but I have one last issue to solve now:

I need to select a range of sheets to apply the code, as it is now it
applies on all the sheets.

Would you please add, say, Sheet1, Sheet2 and Sheet3 so that I can see where
and how you do it and I will add the rest.

Thank you!
Alex

PS: Have you got any book to suggest me to learn the basics of programming
on VBA?

Bob Phillips · Jul 11, 2004

Hi Alex,

Option Explicit

Dim arySheets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet

On Error GoTo ws_exit:
arySheets = Array("Sheet1", "Sheet3")
Application.EnableEvents = False
If SheetInArray(Sh.Name) Then
If Target.Address = "$B$5" Then
With Target
If .Value >= 1 And .Value <= 12 Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name And
SheetInArray(oSheet.Name) Then
If oSheet.ProtectContents Then
oSheet.Unprotect
oSheet.Range("B5").Value = .Value
oSheet.Protect
Else
oSheet.Range("B5").Value = .Value
End If
End If
Next oSheet
Else
MsgBox .Value & " is an invalid value"
.Value = ""
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

Private Function SheetInArray(Name As String)
Dim fSheet As Boolean
Dim i As Long
fSheet = False
For i = LBound(arySheets, 1) To UBound(arySheets, 1)
If arySheets(i) = Name Then
fSheet = True
Exit For
End If
Next i
SheetInArray = fSheet
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo · Jul 12, 2004

Bob,

I get the following error message when I apply the change:

"Only comments may appear after EndSub, EndFunction or EndProperty"

Then I added a comment delimiter and the result is that I don't get the error but the code doesn't change the numbers in the sheets I have selected.
To make it easier, I enclose the two codes as they appear in the WB:

Private Sub Workbook_Open()
'''Enable Outlining navigation and protect everything on the sheet with UserInterfaceOnly.

Sheet2.EnableOutlining = True
Sheet2.Protect , True, True, True, True
Sheet8.EnableOutlining = True
Sheet8.Protect , True, True, True, True
Sheet9.EnableOutlining = True
Sheet9.Protect , True, True, True, True
Sheet10.EnableOutlining = True
Sheet10.Protect , True, True, True, True
Sheet11.EnableOutlining = True
Sheet11.Protect , True, True, True, True
Sheet12.EnableOutlining = True
Sheet12.Protect , True, True, True, True
Sheet13.EnableOutlining = True
Sheet13.Protect , True, True, True, True
Sheet15.EnableOutlining = True
Sheet15.Protect , True, True, True, True
Sheet16.EnableOutlining = True
Sheet16.Protect , True, True, True, True
Sheet17.EnableOutlining = True
Sheet17.Protect , True, True, True, True
Sheet18.EnableOutlining = True
Sheet18.Protect , True, True, True, True
Sheet19.EnableOutlining = True
Sheet19.Protect , True, True, True, True
Sheet20.EnableOutlining = True
Sheet20.Protect , True, True, True, True

End Sub

Option Explicit

Dim arySheets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet

On Error GoTo ws_exit:
arySheets = Array("Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet20")
Application.EnableEvents = False
If SheetInArray(Sh.Name) Then
If Target.Address = "$B$5" Then
With Target
If .Value >= 1 And .Value <= 12 Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name And SheetInArray(oSheet.Name) Then
If oSheet.ProtectContents Then
oSheet.Unprotect
oSheet.Range("B5").Value = .Value
oSheet.Protect
Else
oSheet.Range("B5").Value = .Value
End If
End If
Next oSheet
Else
MsgBox .Value & " is an invalid value"
.Value = ""
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

Private Function SheetInArray(Name As String)
Dim fSheet As Boolean
Dim i As Long
fSheet = False
For i = LBound(arySheets, 1) To UBound(arySheets, 1)
If arySheets(i) = Name Then
fSheet = True
Exit For
End If
Next i
SheetInArray = fSheet
End Function

Metallo · Jul 12, 2004

Bob,

In the WB I also have 2 macros that allows some formatting option, they are linked to 2 buttons.

In my WB I have, Macros and Codes (below).
Now, I want to add a PSW to avoid that the users do mistakes or changes.

What is the code I can use to add the PSW and where shall I place it?

My intention is to use the VBA project properties to hide the PSW.

Thank you
Alex

I will use then the

Bob Phillips · Jul 12, 2004

Alex,

Re-order the coide

Option Explicit

Dim arySheets

Private Sub Workbook_Open()
'''Enable Outlining navigation and protect everything on the sheet with
UserInterfaceOnly.

Sheet2.EnableOutlining = True
Sheet2.Protect , True, True, True, True
Sheet8.EnableOutlining = True
Sheet8.Protect , True, True, True, True
Sheet9.EnableOutlining = True
Sheet9.Protect , True, True, True, True
Sheet10.EnableOutlining = True
Sheet10.Protect , True, True, True, True
Sheet11.EnableOutlining = True
Sheet11.Protect , True, True, True, True
Sheet12.EnableOutlining = True
Sheet12.Protect , True, True, True, True
Sheet13.EnableOutlining = True
Sheet13.Protect , True, True, True, True
Sheet15.EnableOutlining = True
Sheet15.Protect , True, True, True, True
Sheet16.EnableOutlining = True
Sheet16.Protect , True, True, True, True
Sheet17.EnableOutlining = True
Sheet17.Protect , True, True, True, True
Sheet18.EnableOutlining = True
Sheet18.Protect , True, True, True, True
Sheet19.EnableOutlining = True
Sheet19.Protect , True, True, True, True
Sheet20.EnableOutlining = True
Sheet20.Protect , True, True, True, True

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet

On Error GoTo ws_exit:
arySheets = Array("Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13",
"Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet20")
Application.EnableEvents = False
If SheetInArray(Sh.Name) Then
If Target.Address = "$B$5" Then
With Target
If .Value >= 1 And .Value <= 12 Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name And
SheetInArray(oSheet.Name) Then
If oSheet.ProtectContents Then
oSheet.Unprotect
oSheet.Range("B5").Value = .Value
oSheet.Protect
Else
oSheet.Range("B5").Value = .Value
End If
End If
Next oSheet
Else
MsgBox .Value & " is an invalid value"
.Value = ""
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

Private Function SheetInArray(Name As String)
Dim fSheet As Boolean
Dim i As Long
fSheet = False
For i = LBound(arySheets, 1) To UBound(arySheets, 1)
If arySheets(i) = Name Then
fSheet = True
Exit For
End If
Next i
SheetInArray = fSheet
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo said:
Bob,

I get the following error message when I apply the change:

"Only comments may appear after EndSub, EndFunction or EndProperty"

Then I added a comment delimiter and the result is that I don't get the

error but the code doesn't change the numbers in the sheets I have selected.

To make it easier, I enclose the two codes as they appear in the WB:

Private Sub Workbook_Open()
'''Enable Outlining navigation and protect everything on the sheet with UserInterfaceOnly.

Sheet2.EnableOutlining = True
Sheet2.Protect , True, True, True, True
Sheet8.EnableOutlining = True
Sheet8.Protect , True, True, True, True
Sheet9.EnableOutlining = True
Sheet9.Protect , True, True, True, True
Sheet10.EnableOutlining = True
Sheet10.Protect , True, True, True, True
Sheet11.EnableOutlining = True
Sheet11.Protect , True, True, True, True
Sheet12.EnableOutlining = True
Sheet12.Protect , True, True, True, True
Sheet13.EnableOutlining = True
Sheet13.Protect , True, True, True, True
Sheet15.EnableOutlining = True
Sheet15.Protect , True, True, True, True
Sheet16.EnableOutlining = True
Sheet16.Protect , True, True, True, True
Sheet17.EnableOutlining = True
Sheet17.Protect , True, True, True, True
Sheet18.EnableOutlining = True
Sheet18.Protect , True, True, True, True
Sheet19.EnableOutlining = True
Sheet19.Protect , True, True, True, True
Sheet20.EnableOutlining = True
Sheet20.Protect , True, True, True, True

End Sub

Option Explicit

Dim arySheets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet

On Error GoTo ws_exit:
arySheets = Array("Sheet9", "Sheet10", "Sheet11", "Sheet12",

"Sheet13", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet20")

Metallo · Jul 12, 2004

Bob,

It doesn't work.

There are no errors which is good, but something is wrong in the code
itself, if I enter a number from 1 to 12 in a sheet, the others do not
change.

Thanks

Alex

Bob Phillips · Jul 12, 2004

It's the protection you add on opening. How can you change a sheet at all if
you protect it, in order to trigger the event?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo · Jul 12, 2004

Bob,

The cells where the numbers have to be input are not protected (obviously)
In fact, in the previous version you gave me, the one without sheet range,
works fine.
That's why I cannot understand where the problem is.

Alex

Bob Phillips · Jul 13, 2004

Alex,

Again, just tried it. Check that those cells really are unlocked.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo said:
Bob,

The cells where the numbers have to be input are not protected (obviously)
In fact, in the previous version you gave me, the one without sheet range,
works fine.
That's why I cannot understand where the problem is.

Alex

Bob Phillips said:

It's the protection you add on opening. How can you change a sheet at

Click to expand...

all

if
you protect it, in order to trigger the event?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

get
the

Click to expand...

Target

As

Click to expand...

that

it

only
accepts numbers till 12), sometimes it works and change them in
all
the
other WS, sometimes, instead it just blocks Excel and I have to
press
CTR+ALT+DEL.
This worries me because having to deliver the application to
other
users,
it can be a problem.
Is it possible a conflict with other codes and macro in the
application?
As I mentioned in my previous mail, I have the following

Click to expand...

code

to
protect
the sheets and guarantee the outlining:

Private Sub Workbook_Open()
'''Enable Outlining navigation and protect everything on the
sheet
with
UserInterfaceOnly.

Sheet2.EnableOutlining = True
Sheet2.Protect , True, True, True, True
Sheet8.EnableOutlining = True
Sheet8.Protect , True, True, True, True
Sheet9.EnableOutlining = True
Sheet9.Protect , True, True, True, True
Sheet10.EnableOutlining = True
Sheet10.Protect , True, True, True, True
Sheet11.EnableOutlining = True
Sheet11.Protect , True, True, True, True
Sheet12.EnableOutlining = True
Sheet12.Protect , True, True, True, True
Sheet13.EnableOutlining = True
Sheet13.Protect , True, True, True, True
Sheet15.EnableOutlining = True
Sheet15.Protect , True, True, True, True
Sheet16.EnableOutlining = True
Sheet16.Protect , True, True, True, True
Sheet17.EnableOutlining = True
Sheet17.Protect , True, True, True, True
Sheet18.EnableOutlining = True
Sheet18.Protect , True, True, True, True
Sheet19.EnableOutlining = True
Sheet19.Protect , True, True, True, True
Sheet20.EnableOutlining = True
Sheet20.Protect , True, True, True, True

End Sub

This is instead the one you gave me with the cell changed

Click to expand...

to
B5

because
is
the one to input the number.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
Target
As
Range)
Dim oSheet As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$B$5" Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name Then
oSheet.Range("B5").Value = Sh.Range("B5").Value
End If
Next oSheet
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hope you can help

Alex

:

This line

osheet.Range("A1").Value =
Sh.rnage("A1").Value

shouyld read

osheet.Range("A1").Value =
Sh.Range("A1").Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message
Alex,

Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
Target
As
Range)
dim oSheet as worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
For Each osheet In ActiveWorkbook.Worksheets
If osheet.Name <> Sh.Name Then
osheet.Range("A1").Value =
Sh.rnage("A1").Value
End If
Next osheet
End If

ws_exit:
Application.EnableEvents = True

End Sub

Put the coide in ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message
Jim,

What you say is true, however, it doens't solve the
problem.
If I link all WS (2 to 10) to WS1 the change will only
occur
if
I
change
the month in WS1.
What I want to get, is to have a change regardless

Click to expand...

of
the

Click to expand...

Metallo · Jul 13, 2004

Bob,

I don't know why, but when I changed in the code the name of the sheets to
the way they have been actually named, it works!
For instance, Sheet1 is named "2003 YTD", so I placed 2003 YTD instead of
Sheet1 and it works perfectly.
In the previous version you gave me, I used Sheet1 and it also worked fine.
This is really difficult for me to explain.

Thanks very much for your help.

Alex

Bob Phillips said:
Alex,

Again, just tried it. Check that those cells really are unlocked.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Metallo said:

Bob,

The cells where the numbers have to be input are not protected (obviously)
In fact, in the previous version you gave me, the one without sheet range,
works fine.
That's why I cannot understand where the problem is.

Alex

all

Click to expand...

Target

As Target is
now I
can 1-12,
and that them
in have
to

Click to expand...

application

to

Click to expand...

changed