• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing a range of cells if sheetnames change

Hello,
In a sheet called data i have in c3 : c34 20 sheetnames (manualy input).
I would like a piece of code to change a cell if the sheetname changes.
For example if i change the sheet name sheet 1 into sheet 10 then the value of c3 in the sheet data should also change into sheet 10
Any suggestions welcome
 
Try using the following:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
  If Target.Row = 1 And Target.Column = 1 Then
  For i = 1 To Sheets.Count
  Cells(i, 2) = Sheets(i).Name
  Next i
  End If
 
End Sub

Could not find an Event for changing sheet name... For the code to run, you will have to select Cell A1 each time. Hope this is not to much of a constraint for what you doing...

See attached example. Add a new sheet or change name of an existing sheet and then go to Sheet Master and Select Cell A1.

Hope this suit your needs!

Cheers,
Velen
 

Attachments

  • List of Sheets.xls
    41 KB · Views: 3
Hi ,

I am not sure this is foolproof , but you can test it thoroughly.

Narayan
 

Attachments

  • temporary.xlsm
    17.2 KB · Views: 6
Thank, you both for helping me, i was trying to make a little example, but I will look trough both solutions an come back to you
You are great helpers
 
Hi ,

See the uploaded file. I had defined a named range called List_of_Sheets , which I have now defined in your workbook.

Narayan
 

Attachments

  • example.xlsb
    18.9 KB · Views: 7
Hello,
@NARAYANK991
I get an error in this line (424 object recquired)
Code:
 .Index([List_of_Sheets], .Match(PrevShtName, [List_of_Sheets], 0)).Value = Sh.Name
I made the list (name is correct) What am I dowing wrong?
 
Hi ,

I tried protecting the tab named Data , which I assume you are referring to as Master ; I did get an error , but it was not the error you mention ; because the tab was protected , and because the code was trying to change the contents of a cell on a protected tab , Excel generated a valid error.

I tried protecting the tabs whose names get changed , but Excel did not generate any error in this case ; the list of tab names on the Data tab reflected the changes correctly.

Narayan
 
Hello Narayan,
I can not find what I am doinging wrong, I still get an error message, here is the real sheet, can you please have a look at it
 

Attachments

  • 2016 - Prognosemodel - LEEG - V1.28 - NULMETINGlijst.xlsb.zip
    847 KB · Views: 1
Hello Narayan,
Thank you for your efford
The ones in the list B3 tot B32
Note the sheet called peer is 1 in the list and banaan is 4 in the list
 
Hi ,

See the uploaded file.

The problem was because the tab names are text strings , while the data entered in the Master were numeric.

Narayan
 

Attachments

  • 2016 - Prognosemodel - LEEG - V1.28 - NULMETINGlijst.zip
    788.9 KB · Views: 2
Hi,
Narayan, I implemented it in the protected workbook and it works perfect. You are a great teacher , thank you so much for all your efforts.
 
Hi Narayan,
I have another question about the same file but another piece of code, Can I ask it here or is against the forumrules and should I start a new topic?
 
Hi ,

If it is on a piece of code which deals with something different , then it may be better to start a new thread ; if it has to do with the same topic , then you can continue posting in this same thread.

Narayan
 
Hi,
Thank you Narayan, I was in doubt.
I have these 2 pieces of code, that changes the sheet name if you change cell D2 in a sheet (this code works fine), now I would like that the code only works for the sheets that are listed in the list (b3:B34) used in the other code
This is the code
Code:
Function GoedeNaam(st As String) As String
  Dim Fout() As String
  Dim i As Integer
  Dim Sh As Object
   
  Fout = Split("\,/,?,*,[,]", ",")
  For i = 0 To 5
  st = Replace(st, Fout(i), vbNullString)
  Next i
   
  For Each Sh In ActiveWorkbook.Sheets
  If Sh.Name = st Then
  st = ""
  End If
  Next Sh
  GoedeNaam = Left(st, 31)
End Function
And
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim Bladnaam As String
  ThisWorkbook.Unprotect Password:=pwd1
  Application.EnableEvents = False
  If Target.Address(0, 0) = "D2" And Target <> "" And Target.Count = 1 Then
  Bladnaam = GoedeNaam(Target.Value)
  If Bladnaam <> "" Then
  ActiveSheet.Name = Bladnaam
  Target.Value = Sh.Name
  Else
  Target.ClearContents
  MsgBox "bladnaam bestaat al"
  End If
  End If
  Application.EnableEvents = True
  ThisWorkbook.Protect Structure:=True, Windows:=False, Password:=pwd1
End Sub
 
Back
Top