How do I solve this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
|
 
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
 
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
| > |
| >
| >
| >
 
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
 
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
Click to expand...
switch to the same number.If I input the number in WS4, again all the other
will change, and so on.
 
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)
 
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?
 
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)
 
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
 
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
 
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
Click to expand...
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",
Click to expand...
"Sheet13", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet20")
 
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
 
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)
 
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
 
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...
Click to expand...
 
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
Click to expand...
 
You must log in or register to reply here.

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

Access Control With UserName 2
Help with email workbook 4
Delete sheets code error requires object 2
Worksheet name 4
VBA: Generate listing based on another worksheet in the same workb 4
Getting correct labels in Excel pivot 2
Worksheet selection 2
Select not actualy selecting the cell 2

Back
Top