Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
数式で参照したA1セルの値をシート名に
投稿日時: 19/10/22 13:51:54
投稿者: 園 日暮

いつもお世話になります。
 
Sheet1(現在の名は 年間暦)です。
 
手動で数式の入らないA1セルに何がしの値を入れて下記のVBA構文でシート名の変更は可能です。
 
  が、
   A1=土日祝日除き暦!Q1
  という数式が入った場合は下記の構文ではシート名に反映してくれません。
 
数式が入ったA1セルの値が変わる毎にシート名に反映されるようにするにはどうすれば
いいかご教示を仰ぎたいです。
宜しくお願いいたします。
 
 
参考
Private Sub Worksheet_Change(ByVal Target As Range)
     On Error Resume Next
     If Target.Address = "$A$1" Then
         ActiveSheet.Name = Target.Value
     End If
 End Sub
 

回答
投稿日時: 19/10/22 14:29:38
投稿者: めんたん

数式を見張る場合は
 
Private Sub Worksheet_Calculate()
    MsgBox "数式の結果が変更されたよ!"
End Sub
 
のようにします。
ただし、これがA1以外のセルに入った数式の結果が変更された場合は
判別する方法があるかなー?

回答
投稿日時: 19/10/22 15:11:44
投稿者: WinArrow
投稿者のウェブサイトに移動

> A1=土日祝日除き暦!Q1
>という数式が入った場合は下記の構文ではシート名に反映してくれません。
 
この説明は本当ですか?
 
ActiveSheet.Name
と書かれているが、別のシートがActiveになっていませんか?
 

回答
投稿日時: 19/10/22 15:17:30
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
掲示のコードがシートモジュールに書かれているので
 
>Activesheet.Name
ではなく
Me.Name
の方がよいでしょう。
 
それから
当該シートのA1以外のセルを更新時でも
マクロを実行したいということならば、
A1の数式を
=INDIRECT("土日祝日除き暦!Q1")
とすれば、常に実行されます・・・・無駄な動きにはなりますが・・・
 

投稿日時: 19/10/22 15:40:20
投稿者: 園 日暮

土日祝日除き暦!Q1 には、
 
  =IF(A3=INDIRECT("A3"),INDIRECT(A3&"3"),"")
  ここで言う A3 はシート名が「土日祝日除き暦」にあるセルです。
 
このA3には リストで 「P Q R S}の4つのリストがあります。
 
この4つのリストは列番号を意味しています。
それ故に例えば、
A3のリストで P を選択したとき、土日祝日除き暦の P3 の値を参照します。
 
このP3の値(土日祝日除き暦Q1)をシート名(現在は 年間暦)のA1に。
 
このA1のタイをシート名にと質問しているところです。
 
ご質問のアクティブシートは土日祝日除き暦でセルはA3になり違うシートでのアクティブになりますね。

回答
投稿日時: 19/10/22 16:30:07
投稿者: WinArrow
投稿者のウェブサイトに移動

>ご質問のアクティブシートは土日祝日除き暦でセルはA3になり違うシートでのアクティブになりますね。
 
シートモジュール内では、自シート以外のシートは参照できません。
それはそれとして
 
Activesheetが「土日祝日除き暦」シートとしたとき
>Activesheet.Name = Target.Value
は、「土日祝日除き暦」シートの名前を変更してもよいのですか?
 
>セルはA3になり違うシートでのアクティブになりますね。
の意味は分かりかねます。
 
状況がいまいちわからないので、
掲示のコードが記述してあるシートを説明できますか?

投稿日時: 19/10/22 16:52:17
投稿者: 園 日暮

ご協力ありがとうございます。
この前にコメント(お礼に)しましたがこれでは説明不足でしょうか
 
 
 

投稿日時: 19/10/22 16:55:15
投稿者: 園 日暮

シート名の変更は可能です。
アドバイスいただければそれに変更させていただきます。

回答
投稿日時: 19/10/22 17:03:54
投稿者: WinArrow
投稿者のウェブサイトに移動

追加質問

園 日暮 さんの引用:

土日祝日除き暦!Q1 には、
 
  =IF(A3=INDIRECT("A3"),INDIRECT(A3&"3"),"")
  ここで言う A3 はシート名が「土日祝日除き暦」にあるセルです。
 
このA3には リストで 「P Q R S}の4つのリストがあります。
 
この4つのリストは列番号を意味しています。
それ故に例えば、
A3のリストで P を選択したとき、土日祝日除き暦の P3 の値を参照します。
 
このP3の値(土日祝日除き暦Q1)をシート名(現在は 年間暦)のA1に。
 
このA1のタイをシート名にと質問しているところです。

>  =IF(A3=INDIRECT("A3"),INDIRECT(A3&"3"),"")
この数式は理解が難しい・・・
単純に
=INDIRECT(A3&"3")
ではダメなんですか?
 
よくわからないこと
WorkSheet_Changeイベントが記述されているシートのシート名を
「土日祝日除き暦」シートのQ1セルの値に変更したいということはわかります。
しかし、どのタイミングで実行しようとしているかわからないのです。
そのタイミングは、私が考えるには、
「土日祝日除き暦」シートのA3セルが変わったときだと思いますが・・・ちがうかな?
 
若し、
「土日祝日除き暦」シートのA3セルが変わったとき
と考えるならば、変更後のシート名は、例P3セルにあるから、
変更前のシート名が必要ではないでしょうか?
 

回答
投稿日時: 19/10/22 17:20:45
投稿者: WinArrow
投稿者のウェブサイトに移動

変更前のシート名は
>年間暦
固定でよいのですか?
 
」固定でよければ
 
Thisworkbookモジュールに
↓のようなコードで対応できると思います。
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sht As Worksheet
    If Sh.Name = "土日祝日除き暦" And Target.Address = "$A$3" Then
        For Each sht In Sheets
            If sht.Name = "年間暦" Then
                sht.Name = Sh.Range(Target.Value & "3").Value
                Exit For
            End If
        Next
    End If
End Sub
 
但し1回のみ実行できます。
※シートモジュールの中のコードは不要です。

投稿日時: 19/10/22 17:53:35
投稿者: 園 日暮

 1 =INDIRECT(A3&"3")
   ではダメなんですか?
 
   OKです
 
 2 「土日祝日除き暦」シートのA3セルが変わったときだと思いますが・・・ちがうかな?
 
   そうです アクティブセル(変更元)とシート名にしたいセルとは違うシートです
  
 3  変更後のシート名は、例P3セルにあるから、
    変更前のシート名が必要ではないでしょうか?
  
    今現在のシート名は「年間暦」になっています
 
 
 ※
  土日祝日除き暦の、
  P3 = 土日祝休
    Q3 = 土隔週日祝休
    R3 = 2/4週土日祝休
    S3 = 1/3/5週土日祝休
 
   例えば、
   土日祝日除き暦A3 に P がリストされれば 上記の 「土日祝休」が
   年間暦B3 に 「土日祝休」 参照されます
 
   土日祝日除き暦P4 からは土日祝のデーターがあります

回答
投稿日時: 19/10/22 18:01:23
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
 
  土日祝日除き暦の、
   P3 = 土日祝休
    Q3 = 土隔週日祝休
    R3 = 2/4週土日祝休
    S3 = 1/3/5週土日祝休

 
↑の中に、「シート名」として使えないものがあります。
 
確認してみてください。

投稿日時: 19/10/22 18:06:29
投稿者: 園 日暮

変更前のシート名は
>年間暦
 固定でよいのですか?
  
   このブックを作成していたときに使用したシート名で今質問のことが可能ならこのシート名は不要で固定  ではありません
 
   前回のコメントで P3 〜 S3 のいずれかにその都度変更します

回答
投稿日時: 19/10/22 18:08:39
投稿者: WinArrow
投稿者のウェブサイトに移動

アクティブセルを勘違いしていませんか?
 
シートモジュールの
Worksheet_Changeイベントで
アクティブセルは、Targetのことです。
 
ですから
アクティブセル(変更元)
は、認識が異なります。

投稿日時: 19/10/22 18:11:58
投稿者: 園 日暮

そでしたね
 
2/4週土日祝休
 土2日祝休
 
1/3/5週土日祝休
 土1日祝休
 
 は可能ですか

投稿日時: 19/10/22 18:24:19
投稿者: 園 日暮

大変ご苦労かけています。
ありがとうございます。
  
但し1回のみ実行できます。
※シートモジュールの中のコードは不要です。
 
 その都度変更しますので何回とは言えませんが1回のみではないのでご考量ください。

回答
投稿日時: 19/10/22 19:37:37
投稿者: WinArrow
投稿者のウェブサイトに移動

園 日暮 さんの引用:

 
 その都度変更しますので何回とは言えませんが1回のみではないのでご考量ください。

でしたら、変更前のシート(シート名不明)を
セルP3が示すシート名に変更するのか・・・
 
ということになるから、変更前のシート名を
どのように取得するのか?
をお考えください。

回答
投稿日時: 19/10/22 19:39:25
投稿者: WinArrow
投稿者のウェブサイトに移動

園 日暮 さんの引用:
そでしたね
 
2/4週土日祝休
 土2日祝休
 
1/3/5週土日祝休
 土1日祝休
 
 は可能ですか

 
人に聞くまでもありません。
手操作でも確認できますので
確認してみましょう。

投稿日時: 19/10/22 20:10:40
投稿者: 園 日暮

WinArrow さんの引用:
園 日暮 さんの引用:

 
 その都度変更しますので何回とは言えませんが1回のみではないのでご考量ください。

でしたら、変更前のシート(シート名不明)を
セルP3が示すシート名に変更するのか・・・
 
ということになるから、変更前のシート名を
どのように取得するのか?
をお考えください。
]お言葉を返すようですがこのシートにはまず名前を付けていてそれから質問のVBAと思っていましたのでまず名前は付けなければなりませんよね
それが取り合え幡豆付けたシ―ト名が「年間暦」です
おかしいでしょうか

回答
投稿日時: 19/10/22 20:25:50
投稿者: WinArrow
投稿者のウェブサイトに移動

園 日暮 さんの引用:

お言葉を返すようですがこのシートにはまず名前を付けていてそれから質問のVBAと思っていましたのでまず名前は付けなければなりませんよね
それが取り合え幡豆付けたシ―ト名が「年間暦」です
おかしいでしょうか

おかしくはありません。
だから、1回だけ実行できると申し上げたんです。
2回目は、どのような名前になっているのでしょうか?
 
 
追加
どうでもよいことなので
この問題が解決したら、改善したほうがよいところをアドバイスします。
 
「土日祝日除き暦」シートのA3得るの入力規則の設定
現在は:,列番号(P,Q,R,S)
改善後:=$P$3:$S$3
 
これにより
>sht.Name = Sh.Range(Target.Value & "3").Value

sht.Name = Target.Value
のように、素直なコードになります。
ワークシート関数でいえば
>=INDIRECT(A3&"3")
という回りくどく、レスポンスが悪い関数を
=A3
というようになります。
 

回答
投稿日時: 19/10/22 20:42:08
投稿者: WinArrow
投稿者のウェブサイトに移動

ところで
 
当該ブックには、
「年間暦」(初期値)シート

「土日祝日除き暦」シート
の2つだけですか?
 
若し、2つだけだったら、
変更前のシート名の取得は難しくないと思います。
 

投稿日時: 19/10/22 21:02:01
投稿者: 園 日暮

[quote="WinArrow"]

園 日暮 さんの引用:

お言葉を返すようですがこのシートにはまず名前を付けていてそれから質問のVBAと思っていましたのでまず名前は付けなければなりませんよね
それが取り合え幡豆付けたシ―ト名が「年間暦」です
おかしいでしょうか

おかしくはありません。
だから、1回だけ実行できると申し上げたんです。
2回目は、どのような名前になっているのでしょうか?
 
 
追加
どうでもよいことなので
この問題が解決したら、改善したほうがよいところをアドバイスします。
 
「土日祝日除き暦」シートのA3得るの入力規則の設定
現在は:,列番号(P,Q,R,S)
改善後:=$P$3:$S$3
 
これにより
>sht.Name = Sh.Range(Target.Value & "3").Value

sht.Name = Target.Value
のように、素直なコードになります。
ワークシート関数でいえば
>=INDIRECT(A3&"3")
という回りくどく、レスポンスが悪い関数を
=A3
というようになります。
 
[だから、1回だけ実行できると申し上げたんです。
2回目は、どのような名前になっているのでしょうか?
 
  もし1回は リストを P を選択すると 「土日祝休」 シート名にしたい
  又は例えば2回目に R {土2日祝休} 3回目にS {土1日祝休} リストを選択すればシート名が
  そのたびに変更させたい。
 
 
 
 
土日祝日除き暦」シートのA3得るの入力規則の設定
現在は:,列番号(P,Q,R,S)
改善後:=$P$3:$S$3
 
  ありがとうございます。
  採用させていただきます。
 
  素人なりに完成させましたので未熟なところは多々あるかと思います。/quote]

回答
投稿日時: 19/10/22 21:19:14
投稿者: WinArrow
投稿者のウェブサイトに移動

何回も言いますが、
 
1回目は、シート名お初期値が「年度暦」だから
変更する対象のシートは認識できます。
 
しかし、1回目でシート名が変わっているので
2回目に変更する場合、
1回目で変更したシート名は、どこに保持されているんですか?
 
3回目の変更は、2回目の変更したシート名はどこに?
 
ということで、前回変更したときのシート名がどこかに保持されていないと
変更対象のシートが取得できないことになります。
従って、変更は1回だけとなるのではないでしょうか?

投稿日時: 19/10/22 21:27:07
投稿者: 園 日暮

WinArrow さんの引用:
何回も言いますが、
 
1回目は、シート名お初期値が「年度暦」だから
変更する対象のシートは認識できます。
 
しかし、1回目でシート名が変わっているので
2回目に変更する場合、
1回目で変更したシート名は、どこに保持されているんですか?
 
3回目の変更は、2回目の変更したシート名はどこに?
 
ということで、前回変更したときのシート名がどこかに保持されていないと
変更対象のシートが取得できないことになります。
従って、変更は1回だけとなるのではないでしょうか?

保存はしませんが順不同でP〜Sに変わります
そんな頻繁には変わりませんが何回かは変わります
 
変わる例えば前回で説明させていただいたように

回答
投稿日時: 19/10/22 21:41:50
投稿者: WinArrow
投稿者のウェブサイトに移動

園 日暮 さんの引用:

保存はしませんが順不同でP〜Sに変わります
そんな頻繁には変わりませんが何回かは変わります
変わる例えば前回で説明させていただいたように

 
頻繁に変わるか、たまに変わるかという問題ではありません。
 
前回変更したシート名をどのようにして認識するのか?
ということです。
これは、貴方が考えることです。
頑張って考えてください。
 
これが解決しないと、これ以上進展しないと思います。
 

回答
投稿日時: 19/10/22 21:46:29
投稿者: めんたん

なんだか良く分からないお話ですね。私が最初に書いたコメントへの反応は無いようですが
 
Private Sub Worksheet_Calculate()
 
を使ってA1セルの内容が変わったタイミングでコードを実行できますが、それではダメだったんですかね?
手動でいけるなら大丈夫だと思いますが。
(Activesheet.Name は Me.Name に直したうえで実行)

回答
投稿日時: 19/10/22 21:48:55
投稿者: WinArrow
投稿者のウェブサイトに移動

前回変更したシート名

「土日祝日除き暦」シートの適当なセルに格納しておくようなことはかんがえられませんか?
 

投稿日時: 19/10/23 05:39:14
投稿者: 園 日暮

WinArrow さんの引用:
前回変更したシート名

「土日祝日除き暦」シートの適当なセルに格納しておくようなことはかんがえられませんか?
 

前回のシート名の保存は必要はないですがもしVBAでセルの文字列がシート名になるなら新たなシートにに保存してもいいです。

投稿日時: 19/10/23 05:41:15
投稿者: 園 日暮

めんたん さんの引用:
なんだか良く分からないお話ですね。私が最初に書いたコメントへの反応は無いようですが
 
Private Sub Worksheet_Calculate()
 
を使ってA1セルの内容が変わったタイミングでコードを実行できますが、それではダメだったんですかね?
手動でいけるなら大丈夫だと思いますが。
(Activesheet.Name は Me.Name に直したうえで実行)

お答えしていますが

回答
投稿日時: 19/10/23 06:40:13
投稿者: めんたん

Sheets1(名称不定)が必ず左の一番目にあるなら「土日祝日除き暦」シートのシートモジュール
のWorksheet_ChangeでもWorksheet_Calculateでも拾って、Sheets(1).Name を変更してはどうですかね?

投稿日時: 19/10/23 06:52:41
投稿者: 園 日暮

めんたん さんの引用:
Sheets1(名称不定)が必ず左の一番目にあるなら「土日祝日除き暦」シートのシートモジュール
のWorksheet_ChangeでもWorksheet_Calculateでも拾って、Sheets(1).Name を変更してはどうですかね?

おはようございます。
ご協力感謝いたします。
 
誠に申し訳ありません。
お手数ですが具体的にご教示いただけ線でしょうか。
あまりVBAは詳しくないのでお許しください。

回答
投稿日時: 19/10/23 06:59:47
投稿者: めんたん

VBEの画面で変更しようとしているシートオブジェクトを調べて指定したらいいです。
 
VBAProject(Book1)
  Microsoft Excel Object
    Sheet1(現在の名は 年間暦) こんなシート構成だと仮定
    Sheet2(土日祝日除き暦)
    Thisworkbook
 
sheet(1)としなくても、Sheet1.Name = Target.Value でいけるか。
 
 

投稿日時: 19/10/23 08:37:23
投稿者: 園 日暮

WinArrowさん
めんたんさん
 
ご面倒をおかけしました。
色々と迷い考えた末調べて下記の様にしたらうまくできました。
 
ご協力には本当に感謝します。
ありがとうございました。
 
 
ご参考に
Private Sub Worksheet_Change(ByVal Target As Range)
      On Error Resume Next
      If Target.Address = "$B$1" Then
          Me.Name = Target.Value
      End If
  End Sub
  
Sub pastespecialメソッド()
Range("B3").Copy
Range("B1").PasteSpecial xlPasteValues
Range("B1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub