Worksheet_Change

[Macker72]

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

 

[Mudface]

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]

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]

Yeah, please paste it up, might be easier seeing all the code you have already.

 

[Macker72]

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]

Could you give this a try, it seems to work OK: -

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]

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]

**** 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]

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]

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