シート名を変更すると起動するマクロ

質問

エクセルのシート名を変更すると起動するマクロをVBAでつくりたいのですが、わかりません。みなさんのお力をお貸しください。エクセルのバージョンは2002です。

回答No.2

shkwta · ベストアンサー

かなり遠回りな方法になりますが、
まず標準モジュールにシート名を取得するユーザー定義関数を作ります。
Function Sheetname(ByVal Target As Range) As String
　　Application.Volatile
　　Sheetname = Target.Parent.Name
End Function

そして、対象とするワークシートのどこかのセルに
=Sheetname(A1)
などと式を入れて、シート名を表示させます。

ツール→オプションで計算方法を自動にしておきます。

こうしておくと、シート名を変更したときにWorksheet_Calculateイベント、Workbook_SheetCalculateイベントが発生しますので、それを拾って処理します。

Private Sub Worksheet_Calculate()
　　'イベントの発生がシート名の変更によるものかどうかを確認
　　'必要な処理を行う
End Sub

お礼

回答ありがとうございます。
回答のようにしてみたのですが、どうもうまく動きません。
少し質問しますが、よろしければ回答お願いします。
ユーザー定義関数「Sheetname」は動いて、シート名を変更するつど、セルにシート名が表示されるのですが、ワークシートが再計算されているように認識されないようなのです。
なので、
＞Private Sub Worksheet_Calculate()
＞　　'イベントの発生がシート名の変更によるものかどうかを確認
＞　　'必要な処理を行う
＞End Sub
が、起動しません。
「'必要な処理を行う」の所にコマンドボタン「マクロの実行」で動くマクロをそのまま記述しているのですが、是がいけないのでしょうか？

回答No.9

shkwta

No.7です。補足に回答します。
Workbook_SheetChangeではなく、Workbook_SheetCalculateを使用してみてください。

お礼

回答ありがとうございます。
希望するように動きました。
適切に指示を頂いていたのに、私のミスで申し訳ないです。
親切な対応、ありがとうございました。

回答No.8

taocat

こんにちは。
上で似たような質問に下記のように回答しましたので
ここでも同じ回答を載せます。

今回のコードでシート名の変更を捕らえたければ
Calculateイベントではなく、ユーザー定義関数そのものでやったらどうですか。

ユーザー定義関数は、No2.ShtWataさんのを利用しているみたいなのでそのまま利用させてもらいます。

間違いなくする為に、新しいブックで以下のコードをテストしてください。

簡単の為に、"sheet1"だけの変更に対応。

---------------------------------------------
（標準モジュール）

Public OldName As String　'パブリック変数

Function Sheetname(ByVal Target As Range) As String

　Application.Volatile
　Sheetname = Target.Parent.Name

　If OldName = "" Then
　　　OldName = Sheetname
　End If

　If OldName <> Sheetname Then
　　MsgBox OldName & "が " & Sheetname & " に変更されました"
　　OldName = Sheetname
　　End If

End Function
---------------------------------------------

コードを書いたあと、Sheet1のどこかに、=sheetname(A1) を入れておく

ようは、以前のシート名を保持しておき
ユーザー定義関数が再計算されるとき比較するだけです。

もちろん、Calculateイベントで同様のことをしてもＯＫですよね。

何回も言うようですが、このサンプルは、シート１つだけに対応です。

全てのシートに対応したい場合は、ちょっと考えてみてください。
以上です。

お礼

回答ありがとうございました。
希望するように動きました。
すべてのシートに対応する場合も勉強して考えてみます。

回答No.7

shkwta

No.2です。補足への回答です。
(1)書き忘れましたが、Private Sub Worksheet_Calculate()は、標準モジュールのほうではなくて、Sheet1等の対象とするシートのモジュールに記述してください。また、
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
でもよいのですが、この場合はThisWorkbookのモジュールに記述してください。(Shという変数は、再計算が発生したシートを示します）

(2)計算方法が自動になっていることを確認してください。シート名を変更すると表示が変更になるとのことなのでこちらはＯＫだと思いますが。

お礼

丁寧な回答ありがとうございます。
教えていただいたように、標準モジュールにユーザー定義関数を、ThisWorkbookにとりあえず
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox ActiveSheet.Name
End Sub
としてみて、シート名を変更してもメッセージボックスは表示されず、ワークシート内のセルを変更するとメッセージボックスが表示されます。
重ね重ね申し訳ないのですが、気が向きましたら回答お願いします。

回答No.6

WWolf

No4さんの指摘たしかにそうですね。
質問を少し勘違いしていますたが、No5を少し変更すれば可能だとおもいます。

お礼

回答ありがとうございます。
No.5で頂いた回答のようにしてみたのですが、quit123さんのおっしゃるように、一度他のシートをアクティブにした後、目的のシートをアクティブにすると、マクロが走るようなのです。
＞No5を少し変更すれば可能
とありますが、どのように変更すればいいのか、よろしければお教え願いますか？

回答No.5

WWolf

Module1に
↓
Public ShNa(1 To 10)
だけ

ThisBookに
↓
Sub Workbook_Open()
For Each myWB In Workbooks
For i = 1 To myWB.Sheets.Count
ShNa(i) = Worksheets(i).Name
Next i
Next
End Sub

Sub Workbook_SheetActivate(ByVal Sh As Object)
　　If Worksheets(Sh.Index).Name <> ShNa(Sh.Index) Then
　　　　MsgBox ""　’実際はCall文
　　End If
End Sub

こんな感じでいけると思いますよ。

回答No.4

quit123

No.3のWWolfさんへ

シート名の変更はシートがアクティブになった後でしか出来ないのでは？

で、シート名変更後、他のシートをアクティブにし、その後、シート名を変更したシートを再度アクティブにすれば、回答の方法でも出来ると思いますが、それでは質問者の要望とは違いますよね。

回答No.3

WWolf

BookOpen時に各シートのシート名をPublic　の変数に格納し各シートがアクティブになったときのシート名と比較で違えばCall
これをVBAで書けばOK

回答No.1

noname#9268

シート名を変更するイベントが取得できないので
変更するとすぐに起動するマクロは作成できないと思います。

他のアプローチを考えたほうがよいと思います。

お礼

回答ありがとうございました。
VBA等プログラムのようなものは、エクセルのヘルプやgoo等の掲示板で、少し目にした程度ですので、私のしようとしていることへのアプローチは、もっと違ったスマートな方法があるのかもしれないですね。もっと勉強します。