Next question about the Workbook_SheetChange sub

AlohaJulio

Board Regular
Joined
Feb 10, 2004
Messages
188
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

Board Regular
Joined
Feb 10, 2004
Messages
188
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

Well-known Member
Joined
Dec 10, 2002
Messages
1,199

ADVERTISEMENT

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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
you could create a macro as earlyd suggested, perhaps something like this:

Code:
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

Board Regular
Joined
Feb 10, 2004
Messages
188

ADVERTISEMENT

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

Board Regular
Joined
Feb 10, 2004
Messages
188
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

Well-known Member
Joined
Dec 10, 2002
Messages
1,199
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,705
Messages
5,597,663
Members
414,162
Latest member
jborjal1967

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top