capture rename event in excel

[y2k1981]

OK, I originally posted this in the office forum at thread68-522511 until somebody advised me to post here. This is my original posting:

I need to capture the worksheet rename event, because it needs to update another worksheet (called lookups).  When the workbook closes, it loops through all of the worksheet names in the "lookups" sheet and hides any sheets who'se names are there.  So if somebody renames it, the lookups sheet needs to be changed to reflect the new name

Can anybody help at all?

 

[SkipVought]

y2k1981,

I don't believe that it is possible to capture the rename of a sheet.

What makes a sheet a candidate for your list? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[nath]

Hi,

I couldn't find an event that would directly be raised at the change of name of the sheet, but the following workaround should work (put the code in the worksheet module):

Public sSht As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If ThisWorkbook.ActiveSheet.Name <> sSht Then UpdateLookupSheet
End Sub

Private Sub Workbook_Open()
    sSht = ThisWorkbook.ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    sSht = ThisWorkbook.ActiveSheet.Name
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Name <> sSht Then UpdateLookupSheet
End Sub

Nath

 

[SkipVought]

y2k,

In addition to a sheet's Name property, which can change, it also has a CodeName property that does not change when the sheet is renamed. So is a sheet has been designated to be on your list and you identify it with its CodeName, that CodeName will not change if the user renames the sheet.

That's why it is important to know if changing the sheet name makes it a candidate for your list, or some other predetermined criteria. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[y2k1981]

thanks for your replies. skip, firstly, somebody told me that you probably could capture the event (bit didn't tell me how!!) I knew that you could refer to the CodeName instead ... however, I wasn't too sure about how I should go about it. Is it possible to tell excel to find the name of the worksheet labeled "myworksheet" (for example), get the CodeName for it and enter it in cell A1 of the sheet lookups? (I really never ask for much, do I!!!)

The reason why I need to do this is because the workbook is already in "production" ... it's a template and over 100 files have been created from it. So I'm going to write a macro which will add in the extra sheet called "lookups" etc etc etc

Second question: I have a function which is called when the user adds a new sheet. It asks whether they want the sheet to be hidden when the workbook closes. If they select yes, the name gets added to the "lookups" sheet, if they say no, nothing happens (well, except the fact that the sheet gets added of course). So, when they're adding a new sheet, how can I tell excel to add the worksheet's CodeName to the "lookup" sheet instead ot the worksheet's "name"?

Sorry, I don't want to seem like I'm being lazy here, I'm just not that well up on VBA, and because I don't use it very often, the stuff I learn, I forget very quickly. I've been searching all day before I posted here but I coulnd't find anything that answered my question.

nath, I haven't tried out your code yet, what does it do as a matter of interest?

I really do appreciate all of your help, and thank you both for responding

Regards
Martin

 

[nath]

Hi Martin,

the code keeps the name of the active sheet in memory(public variable) and as soon as the sheet gets deactivated (or when the workbook is closed), it compares the name of the deactivated sheet to the sheet in memory. If the name has changed, it calls a code to modify your lookup sheet (code that I have left for you to write, and called "UpdateLookupSheet&quot. When this is done, the Sheet_Activate event is fired and the new active sheet name is put instead in the public variable ... and so on.

This being said it is indeed probably more elegant to simply keep the codename in your lookup sheet if this suits your needs.

Nath

 

[y2k1981]

Thanks nath, I understand what you're saying. Some usefull info there that I didn't know before. I figured out how to get the code name - I simple activete the sheet called "myworksheet" and used the following

Sheets("lookups").Range("A1").value = ActiveSheet.CodeName

Again, it's not the most elegant piece of code you'll ever see, but I'm not an expert, so I'm pretty pleased with myself!! i'll also be able to use this when adding in a sheet. There is one thing that I just realized, if you copy a sheet, it doesn't call the NewSheet sub, and it's very possible that they will copy sheets rather than add them, so is there any way that I can call this function whenthey copy a sheet also? I had a look in the declarations but couldn't find anything.

Again, thanks for your help everybody

 

[nath]

Hi again,

1. I had a look at this CodeName business and I don't see how to use it to hide the sheet, on the basis of a string. If you could do it, let me know, but it seems to me that it is again the same business of Evaluating a function which can't be done in VB.

2. The Add_New event is not raised by copying a sheet you are right. I suppose you might have to count the number of sheets at opening and at closing and compare them. But even if you know a new sheet has been added, it might be a pain to find out which. Also if a sheet has been copied and another one deleted, it would not work. I tried to count the sheet on Sheet_Activate event but it crashes my Excel when it is triggered by a copied sheet ...

Let us know how you get on!

Nath

 

[SkipVought]

Back again...

Here's how to capture the names of every sheet in the workbook...

    Dim wsList As Worksheet, ws As Worksheet, r As Long
    Set wsList = Worksheets("WherMyWorksheetListIs")
    r = 1
    For Each ws In Worksheets
       With ws
          wsList.Cells(r, 1).Value = .CodeName
          wsList.Cells(r, 2).Value = .Name
       End With
    Next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com