A reference to a cell on a page whose name changes dynamically

[steni]

In my woorkbook I have sheet with VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("C4")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

In C4 cell I have formula :

=IF(C2="Deutsch_Österreich";"Angebot";IF(C2="Deutsch_Deutschland";"Angebot";IF(C2="Slovenščina";"Ponudba";IF(C2="English";"Offer";""))))

On another sheet I have code where I want :

Set wsA =

here should be reference to a cell C2 on Workshet which name is changed with code above.
I hope I wrote clearly what I want.
How should Code look like?
Thank for your help

 

[Joe4]

This code looks very oddly written to me, what is it supposed to be doing, and when?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("C4")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

You have this set to when cells are selected.
And in these types of procedures, you typically do NOT set the "Target" range to anything - it is already defined - it is the cell that is selected that caused this code to run in the first place.
So you may reference "Target" in your code, but you typically do not set/reassign it to another range. I am not sure that makes any sense.

Regarding the rest of your question, can you describe your situation in more detail?
How many sheets do you have to start? Are you adding or removing sheets, or just renaming some?
What is the ultimate goal of what you are trying to accomplish here?

 

[steni]

This code looks very oddly written to me, what is it supposed to be doing, and when?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("C4")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

You have this set to when cells are selected.
And in these types of procedures, you typically do NOT set the "Target" range to anything - it is already defined - it is the cell that is selected that caused this code to run in the first place.
So you may reference "Target" in your code, but you typically do not set/reassign it to another range. I am not sure that makes any sense.

Regarding the rest of your question, can you describe your situation in more detail?
How many sheets do you have to start? Are you adding or removing sheets, or just renaming some?
What is the ultimate goal of what you are trying to accomplish here?

I am using this code to rename the page. The page name is determined by the formula in cell C4. Which condition will be applied from the formula in cell C4, is defined in cell C2. In cell C2 I have a data validation dropdown list.
What I need: I have toggle buttons on the other 12 sheets. Caption of this toggle buttons is dependent of cell C2, for toggle buttons I have this code:

Private Sub ToggleButton1_Click()
Dim xAddress As String
Dim MyLabel As OLEObject
        Set wsA = ActiveSheet
        xAddress = "G"
        If ToggleButton1.Value Then
            Application.ActiveSheet.Columns(xAddress).Hidden = True
                    If wsA.Range("C2").Value = "Slovenščina" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Pokaži Ceno z DDV"
                    ElseIf wsA.Range("C2").Value = "Deutsch_Österreich" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto anzeigen"
                    ElseIf wsA.Range("C2").Value = "Deutsch_Deutschland" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto anzeigen"
                    ElseIf wsA.Range("C2").Value = "English" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Show Gross price"
                    End If
        Else
            Application.ActiveSheet.Columns(xAddress).Hidden = False
                    If wsA.Range("C2").Value = "Slovenščina" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Skrij Ceno z DDV"
                    ElseIf wsA.Range("C2").Value = "Deutsch_Österreich" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto verstecken"
                    ElseIf wsA.Range("C2").Value = "Deutsch_Deutschland" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto verstecken"
                    ElseIf wsA.Range("C2").Value = "English" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Hide Gross price"
                    End If
        End If
End Sub

This code work, but only if cell C2 is on activesheet.
But this code is written for ActiveSheet (

Set wsA = ActiveSheet

How to change this code, sheet name on which cell C2 and C4 are is changeing its name every time you usr dropdowlist ? I think that problem is how to write (Set wsA= ????) to work on other pages.

 

[Joe4]

I am using this code to rename the page. The page name is determined by the formula in cell C4. Which condition will be applied from the formula in cell C4, is defined in cell C2. In cell C2 I have a data validation dropdown list.

So, when exactly is this sheet renaming supposed to happen?
What is the "event" that is supposed to be triggering this code to run?
(Right now it appears to run anytime any cell on the sheet is selected, which seems to be a bit overkill).

 

[steni]

Page renaming happens only when a new entry is selected from the dropdownlist in cell C2, but only when any cell on the page is clicked.
Of course, you are right that the code is executed every time a cell is selected on the page. But the renaming only happens when the value in cell C2 changes. If there is no change in C2, there is no renaming.

 

[Joe4]

Page renaming happens only when a new entry is selected from the dropdownlist in cell C2, but only when any cell on the page is clicked.
Of course, you are right that the code is executed every time a cell is selected on the page. But the renaming only happens when the value in cell C2 changes. If there is no change in C2, there is no renaming.

OK, if you only want the sheet renamed when cell C2 is updated, and it updates the name based on the value in cell C4, this is what that code should look like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Only run when cell C2 is updated
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
   
'   Rename sheet if cell C4 is not empty
    If Range("C4") <> "" Then ActiveSheet.Name = Left(Range("C4"), 31)
   
End Sub

So does this renaming code exist on every sheet, or just some of your sheets?

 

[steni]

Only on one (first) sheet.

 

[Joe4]

Only on one (first) sheet.

Excellent!

If it is the first sheet that you are trying to reference, you can do it by index instead of name, i.e.:

Set wsA = Sheets(1)

 

[steni]

My problem is that on other sheets I have toggle buttons which caption is dependent of cell C2 value on sheet where the name of sheet is olso dependent of cell C2 value.
I solved the problem like this: I gave the Cell C2 name: LangSel.
On sheet where I have toggle button i have in cell F1

=LangSel

and with code:

Private Sub ToggleButton1_Click()
Dim xAddress As String
Dim MyLabel As OLEObject
        Set wsA = ActiveSheet
        xAddress = "G"
        If ToggleButton1.Value Then
            Application.ActiveSheet.Columns(xAddress).Hidden = True
                    If wsA.Range("F1").Value = "Slovenščina" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Pokaži Ceno z DDV"
                    ElseIf wsA.Range("F1").Value = "Deutsch_Österreich" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto anzeigen"
                    ElseIf wsA.Range("F1").Value = "Deutsch_Deutschland" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto anzeigen"
                    ElseIf wsA.Range("F1").Value = "English" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Show Gross price"
                    End If
        Else
            Application.ActiveSheet.Columns(xAddress).Hidden = False
                    If wsA.Range("F1").Value = "Slovenščina" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Skrij Ceno z DDV"
                    ElseIf wsA.Range("F1").Value = "Deutsch_Österreich" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto verstecken"
                    ElseIf wsA.Range("F1").Value = "Deutsch_Deutschland" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Brutto verstecken"
                    ElseIf wsA.Range("F1").Value = "English" Then
                        ActiveSheet.ToggleButton1.Object.Caption = "Hide Gross price"
                    End If
        End If
End Sub

Caption of this toggle button change when value of cell C2=LangSel is changing.

But I still thing that there is better solution.

 

[steni]

Set wsA = Sheets(1

Thank you
I will try your solution