Next question about the Workbook_SheetChange sub

[AlohaJulio]

I want to be able to use this frequently, but not all the time. So, what can I do differently to make this a macro?

The code I'm refering to is:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target
End Sub

AJ

 

[kskinne]

could you be more specific, i.e., in what instances do you NOT want your editing cell A1 to trigger the code? there may be a way to edit the event so that it is only triggered when you want to use it


kevin

 

[Zack Barresse]

if you want to keep everything same-same, why not data validation w/ an info alert when cell is selected to warn user of change. with the current code, it should run on that worksheet anytime the cell is edited.

 

[AlohaJulio]

The reason I don't want to have it happen every time, is that I use essbase for downloading information and having non-essbase text can cause problems.

What I'm looking for is a way to change the names of all tabs in a workbook in an automated way for reports that I send out. My first thought was to create a macro, so any time I wanted to change the tab names, I could just click a button. The Workbook_SheetChange routine is the functionality that I'm looking for, but I want it available when I want it, not active at all times.

The data validation isn't an effective approach for me, because I'm the one that is doing the creating and data input in the spreadsheet, the recipients are just viewers.

AJ

 

[earlyd]

So why not create a button from the Forms toolbar and attach a SUB which does the same thing? Once you have the button on your sheet, right-click on it and assign the macro.

 

[kskinne]

you could create a macro as earlyd suggested, perhaps something like this:

Sub test()
On Error Resume Next
MySheet = InputBox("Enter new sheet name")
ActiveSheet.Name = MySheet
End Sub

I inserted the error handling line so that if you enter an invalid character such as a "/", or if you leave the field blank, obviously the sheet name will remain unchanged, but also you will not get a visual basic error message. rather than a button on each sheet, you could just assign a shortcut key for this macro


hth
kevin

 

[AlohaJulio]

I think the macro is a step in the right direction, but not quite there. In this case you'd have to run the macro for each worksheet, and enter a new name each time. Editing the tab directly gets the same if less elegant result.

Is there a way to change the macro so that it would change the tab names on all worksheets to be the value in cell A1, and if there is no value there to stop and have the reslt be an error message?

 

[AlohaJulio]

I've tried creating a sub with the same language in my personal.xls to make it available to assign to a macro button. But I wasn't able to get it to work. Von Pookie suggested "And actually, since it's a Workbook_SheetChange sub, you should put it in the ThisWorkbook module to work correctly. Not just one you insert on your own."
Being new to excel code writing, I'm not sure what things I'd have to change to make it work as a macro.

 

[earlyd]

Try this code; run from button on 1st sheet or a ctrl + character.

Sub RenameFromA1()
Dim Msg As String, i As Integer
For i = 1 To Sheets.Count
If Sheets(i).Range("A1").Value = "" Then
Msg = "Sheet " & i & "(" & Sheets(i).Name & ") has no value in A1. Fix sheet, then rerun."
MsgBox Msg, vbExclamation
Exit Sub
Else
On Error GoTo ErrSheetName
Sheets(i).Name = Sheets(i).Range("A1").Value
On Error GoTo 0
End If
Next i
Exit Sub
ErrSheetName: Msg = "Sheet " & i & "(" & Sheets(i).Name & ") could not be renamed. Check if name already used."
MsgBox Msg, vbExclamation

End Sub

 

[AlohaJulio]

This works perfectly! Thanks very much to everyone for your patience and efforts.