Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


11871 / 13645 ツリー ←次へ | 前へ→

【13630】シート名の変更がかかるとマクロ内のコー... megu 04/5/10(月) 10:04 質問[未読]
【13632】Re:シート名の変更がかかるとマクロ内のコ... ichinose 04/5/10(月) 10:40 回答[未読]
【13633】Re:シート名の変更がかかるとマクロ内のコ... Jaka 04/5/10(月) 10:41 回答[未読]
【13634】忘れ。 Jaka 04/5/10(月) 10:51 発言[未読]
【13636】Re:シート名の変更がかかるとマクロ内のコ... ぴかる 04/5/10(月) 11:31 回答[未読]
【13640】Re:シート名の変更がかかるとマクロ内のコ... BOTTA 04/5/10(月) 13:24 回答[未読]
【13645】Re:シート名の変更がかかるとマクロ内の... megu 04/5/10(月) 15:10 お礼[未読]

【13630】シート名の変更がかかるとマクロ内のコー...
質問  megu  - 04/5/10(月) 10:04 -

引用なし
パスワード
   おはようございます。
ちょっと困った問題が起こってしまいました。

ユーザーにシート名を変更されたときに起こる問題です。
シート名が『Sheet1』のシートの名前をユーザーが別の名前(例えば『Sheet2』)に変えた場合に、Excelシート内の計算式に埋め込まれている『Sheet1』という記述は『Sheet2』に自動変更されるのでこちらは問題ないのですが、
マクロ内の『Sheet1』という記述はそのまま残るため、マクロ実行時に『Sheet1』という名前のシートが無い為エラーが起こってしまいます。

これに対処するためにシート名を変更されてもマクロから参照できるようにしたいのですが、どのようにすれば良いのでしょうか。

ちなみに、一つのシートをxlVeryHiddenに設定しておいて、そのシートからPrevious・Nextで参照するようにしようかと思いましたが、その場合はシートの並び順を変更された場合に誤動作を起こしてしまいました。

「シート名を変えないでね」とユーザーにお願いするしかないのでしょうか・・・。

【13632】Re:シート名の変更がかかるとマクロ内の...
回答  ichinose  - 04/5/10(月) 10:40 -

引用なし
パスワード
   ▼megu さん:
おはようございます。


>ユーザーにシート名を変更されたときに起こる問題です。
>シート名が『Sheet1』のシートの名前をユーザーが別の名前(例えば『Sheet2』)に変えた場合に、Excelシート内の計算式に埋め込まれている『Sheet1』という記述は『Sheet2』に自動変更されるのでこちらは問題ないのですが、
>マクロ内の『Sheet1』という記述はそのまま残るため、マクロ実行時に『Sheet1』という名前のシートが無い為エラーが起こってしまいます。
>
>これに対処するためにシート名を変更されてもマクロから参照できるようにしたいのですが、どのようにすれば良いのでしょうか。
>
>ちなみに、一つのシートをxlVeryHiddenに設定しておいて、そのシートからPrevious・Nextで参照するようにしようかと思いましたが、その場合はシートの並び順を変更された場合に誤動作を起こしてしまいました。
>
>「シート名を変えないでね」とユーザーにお願いするしかないのでしょうか・・・。

そうですね。ブックの保護を行ってシート名の変更やシートの移動をさせない方法も
あります。これは私はよくやります。

シート名を変更したり、移動しても良い方法と言うと
思いついたのは、名前の定義を使用する方法です。

以下のコードで名前の定義を行います。

Sub 定義()
  ActiveWorkbook.Names.Add Name:="sheet1", RefersToR1C1:="=Sheet1!R1C1"
End Sub
もちろん、手動操作でも可能です。

参照するときは、

Sub test()
  Range(Names("sheet1").Value).Parent.Select
End Sub

という方法です。
参考にしてください。

【13633】Re:シート名の変更がかかるとマクロ内の...
回答  Jaka  - 04/5/10(月) 10:41 -

引用なし
パスワード
   こんにちは。

こういう書き方をしたく無いと言うか、した事無いので、どんな不具合が出るのか解りませんし、あまし進めたくないけど...。

シートのコード名で指定してやれば.....。
こんな感じ。

Sheet1.Range("A1:A10").Value = 888

【13634】忘れ。
発言  Jaka  - 04/5/10(月) 10:51 -

引用なし
パスワード
   >「シート名を変えないでね」とユーザーにお願いするしかないのでしょうか・・・。
私だったらこれですね!
「変えないでね」と、言うより変えたら動かなくなりますよ、と言います。
んで、動かなくなったら、ブーたれます。
Winのアプリケーションソフトって、こんなもんじゃないのかなぁ?

【13636】Re:シート名の変更がかかるとマクロ内の...
回答  ぴかる  - 04/5/10(月) 11:31 -

引用なし
パスワード
   こんにちは。

シート名を隠すってのは、どうですか?。シートの切替は、マクロで行う様にする。
結構、こんなんでいけるかも?。100%とちゃうけどネ・・・。

【13640】Re:シート名の変更がかかるとマクロ内の...
回答  BOTTA  - 04/5/10(月) 13:24 -

引用なし
パスワード
   meguさん、皆さん、こんにちは。

たとえば、強制的にシート名を変えない様にする。
まず、シートのA1に
=CELL("filename",A1)
と入力します。
("C:\My Documents\[Book1.xls]Sheet1"などと表示されるはず)

そのシートのシートモジュールに
Private Sub Worksheet_Calculate()
Dim ShName As String, MyPos As Long
  ShName = Me.Range("A1").Value
  MyPos = InStr(ShName, "]")
  ShName = Right(ShName, Len(ShName) - MyPos)
  If ShName <> "Sheet1" Then
    MsgBox "変えちゃダメッ!!"
    Me.Name = "Sheet1"
  End If
End Sub

シート名を変えようとすると"Sheet1"に戻ります。

【13645】Re:シート名の変更がかかるとマクロ内の...
お礼  megu  - 04/5/10(月) 15:10 -

引用なし
パスワード
   ▼ichinoseさん、Jakaさん、ぴかるさん、BOTTAさん:
すばやい回答ありがとうございます。
さっそく一部ですが試しました。

Jakaさんの
>「変えないでね」と、言うより変えたら動かなくなりますよ、と言います。
>Winのアプリケーションソフトって、こんなもんじゃないのかなぁ?
ってこれが一番ラクでいいですね。
これくらい強引でもいいのかも・・・
でも、どれだけ言っても話を聞かない人もいるので、上司にダメって言われました(TT
>シートのコード名で指定してやれば.....。
っていうのは・・・ちょっぴりこわいですね・・・(汗

BOTTAさんの
>強制的にシート名を変えない様にする。
って言うのも個人的にはいいと思うのですが、なんでシート名が変えれないんだって言われるからダメって言われました(TT

ぴかるさんの
>シート名を隠すってのは、どうですか?
>シートの切替は、マクロで行う様にする。
これすごくいいですね。
あまり難しくなさそうだし。(ぉぃ
・・・と思って、これで作ったのですがなんでシート名が隠れてるんだって言われて・・・(略

ということで、ichinoseさんの名前を定義する方法を今回とることにしました。
なんか消去法みたいなことしてしまってゴメンナサイ。
ichinoseさんの方法がExcelシートの見た目も変わらず、ユーザーに強要もしないのでスマートでいい感じな気がします。
これから試そうと思います。
試したらまた結果を報告させていただきます。(^^

11871 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free