セルの値をワークシート名にする(エクセル2013)

質問

インストラクターのネタ帳さんより

http://www.relief.jp/itnote/archives/003382.php

下記「セルの値をワークシート名にする?Worksheet_Change」
を拝借し利用させていただこうと思いましたが
----------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ERR_HANDLER

If Target.Address(False, False) = "H1" Then
ActiveSheet.Name = Range("H1").Value
End If

Exit Sub

ERR_HANDLER:

MsgBox "現在のH1セルの値はシート名にできません。"

End Sub
----------------------

はそのまま出来るのですが、
H1セルにデータの入力規則：リストを指定しますと
エラーとなりシート名が変わりません

sheet1のリストA1:A50をsheet2のH1セルにリスト表示させ
その表示名をそのままシート名に出来ませんでしょうか？

----------------------
Sub copy
Range("H1").Copy Range("P1")
End Sub

----------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ERR_HANDLER

If Target.Address(False, False) = "P1" Then
ActiveSheet.Name = Range("P1").Value
End If

Exit Sub

ERR_HANDLER:

MsgBox "現在のP41セルの値はシート名にできません。"

End Sub
----------------------

としてH1のセルをP1にコピーしたものを指定して試しましたがやはりエラーとなり
うまくいきませんでした。

全くの素人で恐縮ですがよろしくお願いいたします

回答No.4

kagakusuki · ベストアンサー

>早速別のPC(windows7)でエクセル2013の同じ環境にて試しましたが
>日本語のシート名ですと同じく
>MsgBox "現在のH1セルの値はシート名にできません。"
>のエラーメッセジが出てしまいました

　それで、その

>"現在のH1セルの値はシート名にできません。"

というメッセージが出た際に、[OK]ボタンを押してマクロを終了させたと思われますが、その後でH1セルの値をコピーしてから、シートタブをダブルクリックし、[Ctrl]キー+[V]キーで貼り付ける事で、シート名をその日本語の名称に変える事が出来るのかどうかは試してみられたのでしょうか？
　「マクロで変更出来なかったシート名」にコピー&貼り付けを使って変更しようとすると、

"入力されたシートまたはグラフの名前が正しくありません。次の点を確認して修正してください。"

や

"シートの名前をほかのシート、Visual Basic で参照されるオブジェクト ライブラリまたはワークシートと同じ名前に変更することはできません。"

といった類の注意メッセージが現れて、シート名の変更が出来ないという事はないでしょうか？
　もしコピー&貼り付けによるシート名の変更で、そういった類のメッセージが現れて変更出来ないという場合は、その日本語のシート名に変更出来なかった原因はVBAにあるのではなく、元々、「新しいシート名としては使えない名前」に変更しようとした事が原因という事になりますので、御確認願います。

お礼

kagakusuki 様

何度もご親切にありがとうございます
新しいブックに一からやってみましたら出来ました
しかし漢字の氏名をリストで選択しますと同じ現象が起きました
氏名の漢字がエラーを出すようで
シート名を直接名前の変更でコピーしますと表示できるのですが
このVBAでしますとシート名に出来ない漢字があるようです

ありがとうございました

補足

kagakusuki 様

何度もご教授ありがとうございます
office削除ツールでアンインストールしてから再インストールしてみましたが
同じ現象です
シートの名前をコピーペーストすると漢字の氏名は正常に表示されます
ご指摘のようなエラーメッセージは出ておりませんし
毎回同じ
MsgBox "現在のH1セルの値はシート名にできません。"
が出るだけです

ありがとうございました

回答No.3

kagakusuki

>日本語表示（氏名）ではエラーが出てしまい
>アルファベットですとシート名に反映されました

　うーん、おかしいですね。当方のパソコンで試した限りでは、質問者様の御質問文にある1つ目の方のVBAでも、回答No.2のVBAでも、どちらを用いた場合でも、「斉藤」とか「シート十三」などといった日本語で記述されたシート名にする事が出来ました。
　もしかしますと、単なる氏名だけではなく、何かシート名には使えない文字が含まれている文字列を入力しているなどという事は御座いませんでしょうか？

【参考URL】
　シート名、ブック名につけられない文字 - Microsoftサポート
　　https://support.microsoft.com/ja-jp/kb/401030/ja

　大体、

On Error GoTo ERR_HANDLER

という一文があるのですから、エラーとなった場合には

ERR_HANDLER:
MsgBox "現在のH1セルの値はシート名にできません。"

の所に跳びますので、そこでMsgBoxのOKボタンを押した段階でマクロが終了となるだけ筈なのですが、

>エラーが出てしまい

とは一体どの様な状況になった事を仰っておられるのでしょうか？

　兎に角、当方のパソコンでは問題なく出来たという事は、問題はVBAの方ではなく、質問者様が御使いになられているパソコンの方にある様な気が致します。
　Excelを一旦、終了&再度起動を再度試してみられても駄目だったのでしょうか？
　又、それで駄目だった場合には、パソコンを再起動するという方法は試してみられたのでしょうか？
　もし、パソコンを再起動しても駄目だった場合には、Excelのソフトかパソコン自体が壊れている恐れもあるかと思います。

お礼

kagakusuki 様

ご回答ありがとうございました
早速別のPC(windows7)でエクセル2013の同じ環境にて試しましたが
日本語のシート名ですと同じく
MsgBox "現在のH1セルの値はシート名にできません。"
のエラーメッセジが出てしまいました
これがうまく動作しますすと作業効率がぐっと上がると思いましたが残念です
以上お礼とご報告まで申し上げます
ありがとうございました

補足

kagakusuki 様

ご丁寧にご返信ありがとうございました
仰るとおり
MsgBox "現在のH1セルの値はシート名にできません。"
をエラーと申しておりました

（シート名、ブック名につけられない文字）を参照しましたが見当たらず
従来のブックでは手動で同じ「氏名」をシート名に使用しております

あとはソフトかPCの不具合かも知れません
お忙しいところありがとうございました
別のPCでも一度検証してみます

回答No.2

kagakusuki

後、万が一、Excelを終了&再度起動させても駄目だった場合に備えて、念のために若干異なるVBAの一例も提示しておきます。

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ERR_HANDLER
If Intersect(Target, Range("H1")) Is Nothing Then GoTo labelE
ActiveSheet.Name = Range("H1").Value
GoTo labelE

ERR_HANDLER:
MsgBox "現在のH1セルの値はシート名にできません。"

labelE:

End Sub

補足

kagakusuki 様
ご教授ありがとうございました
万が一の方でうまく動作いたしました
しかし、日本語表示（氏名）ではエラーが出てしまい
アルファベットですとシート名に反映されました
日本語は無理でしょうか？よろしくお願いいたします

回答No.1

kagakusuki

>H1セルにデータの入力規則：リストを指定しますと
>エラーとなりシート名が変わりません

　私がそのVBAを自分のパソコンで試した時には、シート名は変わりましたよ。
　VBAの構文とは別の何かが不具合の原因かもしれませんので、保存した後、Excelを一旦、終了&再度起動させてから、もう一度試してみられては如何でしょうか？

>sheet1のリストA1:A50をsheet2のH1セルにリスト表示させ
>その表示名をそのままシート名に出来ませんでしょうか？

　入力規則を設定される際に、元の値欄に

=Sheet1!$A$1:$A$50

と入力されるか、

=INDIRECT("Sheet1!A1:A50")

と入力すれば宜しいのではないでしょうか？