Worksheet_Change

Macker72

Board Regular
Joined
Mar 5, 2002
Messages
107
Hi

How can i have 2 of the follwoing subs on the same worksheet?:

Private Sub Worksheet_Change(ByVal Target As Range)

When i try, i get the error message:
"Compile Error: Ambiguous name detected: Worksheet_Change"

Thanks
Mac
This message was edited by Macker72 on 2002-10-01 03:59
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
No, just one type of each event sub for each sheet. What are you trying to do, as it should be possible to 'amalgamate' your two routines into one?
 

Macker72

Board Regular
Joined
Mar 5, 2002
Messages
107
Hi
Sheet 1 is my master sheet where i set language and currency. if A1 = £ then multiple ranges in Sheets 2-10 go to £ format, same for $, € and so on. This Worksheet_Change works well. Now i want to do the same for translating column titles, on Sheet 1, if A2 = English, then A4 in Sheet2 = "Mem", A3 = "Type"; if A2 = French, then A4 on Sheet2 = "Type" and A3 = "D'Abo" and so on so forth.
It's long winded but each one works separately.
I can paste the entire sub so far if you think that will help.
Many thanks
Mac
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Yeah, please paste it up, might be easier seeing all the code you have already.
 

Macker72

Board Regular
Joined
Mar 5, 2002
Messages
107

ADVERTISEMENT

Thanks Mudface. here you go:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$7" Then Exit Sub
With Sheet3.Range("Mon")
If Target.Value = "$" Then .NumberFormat = "[$$]#,##0.00"
If Target.Value = "£" Then .NumberFormat = "[$£]#,##0.00"
If Target.Value = "EURO" Then .NumberFormat = "[$€] #,##0.00"
If Target.Value = "CHF" Then .NumberFormat = "[$CHF] #,##0.00"
End With
With Sheet5.Range("Tue")
If Target.Value = "$" Then .NumberFormat = "[$$]#,##0.00"
If Target.Value = "£" Then .NumberFormat = "[$£]#,##0.00"
If Target.Value = "EURO" Then .NumberFormat = "[$€] #,##0.00"
If Target.Value = "CHF" Then .NumberFormat = "[$CHF] #,##0.00"
End With
With Sheet7.Range("Wed")
If Target.Value = "$" Then .NumberFormat = "[$$]#,##0.00"
If Target.Value = "£" Then .NumberFormat = "[$£]#,##0.00"
If Target.Value = "EURO" Then .NumberFormat = "[$€] #,##0.00"
If Target.Value = "CHF" Then .NumberFormat = "[$CHF] #,##0.00"
End With
With Sheet9.Range("Thu")
If Target.Value = "$" Then .NumberFormat = "[$$]#,##0.00"
If Target.Value = "£" Then .NumberFormat = "[$£]#,##0.00"
If Target.Value = "EURO" Then .NumberFormat = "[$€] #,##0.00"
If Target.Value = "CHF" Then .NumberFormat = "[$CHF] #,##0.00"
End With
With Sheet11.Range("Fri")
If Target.Value = "$" Then .NumberFormat = "[$$]#,##0.00"
If Target.Value = "£" Then .NumberFormat = "[$£]#,##0.00"
If Target.Value = "EURO" Then .NumberFormat = "[$€] #,##0.00"
If Target.Value = "CHF" Then .NumberFormat = "[$CHF] #,##0.00"
End With
With Sheet13.Range("Sat")
If Target.Value = "$" Then .NumberFormat = "[$$]#,##0.00"
If Target.Value = "£" Then .NumberFormat = "[$£]#,##0.00"
If Target.Value = "EURO" Then .NumberFormat = "[$€] #,##0.00"
If Target.Value = "CHF" Then .NumberFormat = "[$CHF] #,##0.00"
End With
With Sheet15.Range("Sun")
If Target.Value = "$" Then .NumberFormat = "[$$]#,##0.00"
If Target.Value = "£" Then .NumberFormat = "[$£]#,##0.00"
If Target.Value = "EURO" Then .NumberFormat = "[$€] #,##0.00"
If Target.Value = "CHF" Then .NumberFormat = "[$CHF] #,##0.00"
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$8" Then Exit Sub
If Target.Value = "English" Then Sheet3.Range("$A$4") = "Name &"
If Target.Value = "English" Then Sheet3.Range("$A$5") = "First Name"
If Target.Value = "English" Then Sheet3.Range("$B$4") = "Subs"
If Target.Value = "English" Then Sheet3.Range("$B$5") = "Type"
If Target.Value = "English" Then Sheet3.Range("$C$4") = "Start"
If Target.Value = "English" Then Sheet3.Range("$C$5") = "Date"
If Target.Value = "French" Then Sheet3.Range("$A$4") = "Nom et"
If Target.Value = "French" Then Sheet3.Range("$A$5") = "Prénom"
If Target.Value = "French" Then Sheet3.Range("$B$4") = "Type"
If Target.Value = "French" Then Sheet3.Range("$B$5") = "d'Abo"
If Target.Value = "French" Then Sheet3.Range("$C$4") = "Date"
If Target.Value = "French" Then Sheet3.Range("$C$5") = "Début"
If Target.Value = "German" Then Sheet3.Range("$A$4") = "Namen unt"
If Target.Value = "German" Then Sheet3.Range("$A$5") = "Vorname"
If Target.Value = "German" Then Sheet3.Range("$B$4") = "Type"
If Target.Value = "German" Then Sheet3.Range("$B$5") = "D'Abo"
If Target.Value = "German" Then Sheet3.Range("$C$1") = "Einfach"
End Sub
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Could you give this a try, it seems to work OK: -

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myArray As Variant
Dim myFormat As String

With Target
    If .Count > 1 Then Exit Sub
    If .Address <> "$C" And .Address <> "$C" Then Exit Sub
    
    If .Address = "$C" Then
        myArray = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
        Select Case .Value
        Case "$"
            myFormat = "[$$]#,##0.00"
        Case "£"
            myFormat = "[$£]#,##0.00"
        Case "EURO"
            myFormat = "[$€] #,##0.00"
        Case "CHF"
            myFormat = "[$CHF] #,##0.00"
        Case Else
            ' put in a generic format here just in case
        End Select
        For x = 0 To 6
            Parent.Parent.Range(myArray(x)).NumberFormat = myFormat
        Next x
    End If
    
    If .Address = "$C" Then
        Select Case .Value
        Case "English"
            With Sheet3
                .Range("$A") = "Name &"
                .Range("$A") = "First Name"
                .Range("$B") = "Subs"
                .Range("$B") = "Type"
                .Range("$C") = "Start"
                .Range("$C") = "Date"
            End With
        Case "French"
            With Sheet3
                .Range("$A") = "Nom et"
                .Range("$A") = "Prénom"
                .Range("$B") = "Type"
                .Range("$B") = "d'Abo"
                .Range("$C") = "Date"
                .Range("$C") = "Début"
            End With
        Case "German"
            With Sheet3
                .Range("$A") = "Namen unt"
                .Range("$A") = "Vorname"
                .Range("$B") = "Type"
                .Range("$B") = "D'Abo"
                .Range("$C") = "Einfach"
            End With
        End Select
    End If
End With
End Sub
 

Macker72

Board Regular
Joined
Mar 5, 2002
Messages
107

ADVERTISEMENT

I have simply copied this in (the idiots approach) but it doesn't work. Do i need to change any of the Cell refernces or change something else?
Thanks again.
Mac
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
**** it. The board's truncated some of the code again. Here's the full version without the code tags: -

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myArray As Variant
Dim myFormat As String

With Target
If .Count > 1 Then Exit Sub
If .Address <> "$C$7" And .Address <> "$C$8" Then Exit Sub

If .Address = "$C$7" Then
myArray = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
Select Case .Value
Case "$"
myFormat = "[$$]#,##0.00"
Case "£"
myFormat = "[$£]#,##0.00"
Case "EURO"
myFormat = "[$€] #,##0.00"
Case "CHF"
myFormat = "[$CHF] #,##0.00"
Case Else
' put in a generic format here just in case
End Select
For x = 0 To 6
Parent.Parent.Range(myArray(x)).NumberFormat = myFormat
Next x
End If

If .Address = "$C$8" Then
Select Case .Value
Case "English"
With Sheet3
.Range("$A$4") = "Name &"
.Range("$A$5") = "First Name"
.Range("$B$4") = "Subs"
.Range("$B$5") = "Type"
.Range("$C$4") = "Start"
.Range("$C$5") = "Date"
End With
Case "French"
With Sheet3
.Range("$A$4") = "Nom et"
.Range("$A$5") = "Prénom"
.Range("$B$4") = "Type"
.Range("$B$5") = "d'Abo"
.Range("$C$4") = "Date"
.Range("$C$5") = "Début"
End With
Case "German"
With Sheet3
.Range("$A$4") = "Namen unt"
.Range("$A$5") = "Vorname"
.Range("$B$4") = "Type"
.Range("$B$5") = "D'Abo"
.Range("$C$1") = "Einfach"
End With
End Select
End If
End With
End Sub
 

Macker72

Board Regular
Joined
Mar 5, 2002
Messages
107
Thanks Mudface. i had tried some of this after sending you my last reply and it worked. Many many thanks, now i have to pull this out over 10 different worksheets.
Rock on!
Mac
 

Macker72

Board Regular
Joined
Mar 5, 2002
Messages
107
Mudface
Is there any way i can make this part of the code:
Case "English"
With Sheet3
point to multiple sheets or must i copy out all the lines each time?
Mac
 

Watch MrExcel Video

Forum statistics

Threads
1,123,045
Messages
5,599,488
Members
414,313
Latest member
Oonagh123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top