[[20160324150041]]　『イベントの実行について』（ｓｉｇｅ）　ページの最後に飛ぶ

『イベントの実行について』（ｓｉｇｅ）

よろしくお願いします。
　R4セルに日付を入力しA１セルには
1.A1＝=TEXT(R4,"gyyy年m月")←R4セルに1ヵ月加算したい
例R4が2016/6/16の場合　A1は「平成28年7月」としたい
また以下の通り

2.R4日付入力と同時にシート名を以下イベントで変化させたいのですが
　良い案があれば教授お願いします。
　
　以下マクロでは一旦A1をアクティブにしないと動作しないので

Private Sub Worksheet_Change(ByVal Target As Range)

  On Error GoTo ERR_HANDLER

  If Target.Address(False, False) = "A1" Then
    ActiveSheet.Name = Range("A1").Value
  End If
  Exit Sub
ERR_HANDLER:
  MsgBox "現在のA1セルの値はシート名にできません。"
End Sub

Excel2016　OS：Windows10

＜使用 Excel：unknown、使用 OS：unknown ＞

 A1セルはR4セルが変更された場合に同時に変更されるのだからTargetがR4セルかどうかをチェックすればいいのでは？

 A1セルの式は
 =TEXT(EDATE(R4,1),"gyyy年m月")
 で。
（ねむねむ） 2016/03/24(木) 15:20

 すでにねむねむさんから回答がありますが、ちょっと気になったことがあるので書きます。

 通常平成○年と表示する場合は
 =TEXT(EDATE(R4,1),"ggge年m月")
 のように "ggge" にすると思うのですが、なぜ "gyyy" なのかなと･･･。
（se_9） 2016/03/24(木) 15:25

重複しまくりましたが、私からも。
（1）=TEXT(DATE(YEAR(R4),MONTH(R4)+1,DAY(R4)),"gyyy年m月")
（2）A1セルの値はR4セルに応じて変わるのならば、判定するアドレスを"R4"に変えるだけでは？
（???） 2016/03/24(木) 15:28

 se_9さん、
 確かに。
 どちらも結果が同じなので気づいていなかった…
（ねむねむ） 2016/03/24(木) 15:32

 ???さんの式だとたとえばR4セルが2016/1/31・2016/3/31の場合、H28年3月・H28年5月になってしまわないか？
（ねむねむ） 2016/03/24(木) 15:35

あ、そうですね。日までそのまま使うと駄目でした。日は1にしないとです。
=TEXT(DATE(YEAR(R4),MONTH(R4)+1,1),"gyyy年m月")
（???） 2016/03/24(木) 15:41

（ねむねむ）様
（se_9）様
（???）様
ありがとうございます。

 =TEXT(EDATE(R4,1),"gyyy年m月")
A1表示はうまくいきました。
また、TargetをR4に変更しイベントも動作しました。

ここから相談ですが、H28年7月スタートで右側にシートが全部で12枚（1年分）あります
最初のシート変更で、全てのシート名を変更したいのですがマクロで可能でしょうか？
以下にシート名を表記します。
H28年7月＿8月＿9月＿10月＿11月＿12月＿H29年1月＿2月＿3月＿4月＿5月＿6月

よろしくお願いします。
（ｓｉｇｅ） 2016/03/24(木) 18:05

いろいろ試してみたのですがうまくいきませんでした。
試した内容は以下の通りです
一旦フラグを立て別名にしてから"A1"に書き換えるようにしてみました。
Sub シート名変更()
Dim i As Long
Dim j As Long

For i = 1 To 12
Worksheets(i).Name = Worksheets(i).Range("Q2").Value
Next i

For j = 1 To 12
Worksheets(i).Name = Worksheets(i).Range("A1").Value’★
Next j
End Sub
★部分で以下のエラー
実行時エラー1004　入力されたシート又はグラフの名前がが正しくありません。
次の点を確認して修正して下さい。
？文字入力が31文字以内であること
？次の使用できない文字が含まれていないこと。；￥／＊　かっこ［　］
？名前が空白でないこと。

マクロの記録で
Sub Macro2()
'
' Macro2 Macro
’　途中省略

    Sheets("10月").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=TEXT(EDATE(R[3]C[16],1),""gyyy年m月"")"
    Sheets("11月").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=TEXT(EDATE(R[3]C[16],1),""m月"")"

End Sub

上記の場合は各シートモジュールに以下を書き込んでいます

 Private Sub Worksheet_Change(ByVal Target As Range)

  On Error GoTo ERR_HANDLER

  If Target.Address(False, False) = "A1" Then
    ActiveSheet.Name = Range("A1").Value
  End If
  Exit Sub
ERR_HANDLER:
  MsgBox "現在のA1セルの値はシート名にできません。"
End Sub

１シートずつ　"A1"セレクトし"F2"キーでENTERで、実行では変更できた。のですが・・・
以上のような、動作チェックしてみてみたのですが、決定的な事が解りませんでした
お知恵拝借よろしくお願いします。

（sige） 2016/03/25(金) 01:30

 上級者からもっとすっきりしたコードが提示されると思うので参考程度にしてください。

 Private Sub Worksheet_Change(ByVal Target As Range)

     Dim i As Long

     On Error GoTo ERR_HANDLER

     Sheets(1).Name = Format(DateAdd("m", 1, Range("R4").Value), "ge年m月")
     For i = 2 To 12
         If Target.Address(False, False) = "R4" Then
            If Format(DateAdd("m", i - 1, Range("R4").Value), "e") = Format(DateAdd("m", i, Range("R4").Value), "e") Then
               Sheets(i).Name = Format(DateAdd("m", i, Range("R4").Value), "m月")
            Else
               Sheets(i).Name = Format(DateAdd("m", i, Range("R4").Value), "ge年m月")
            End If
         End If
     Next

    Exit Sub

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

 End Sub

 ちなみにA1の関数は =TEXT(EDATE(R4,1),"ge年m月")
 でもいいですね。

 ※追記
 もしかしてA1セルに数式入れる必要ないのかな？
（se_9） 2016/03/25(金) 06:27　修正07:35

（se_9）さん
回答ありがとうございます。
早速実行させていただきましたが
ばっちり希望通りです。が

ちなみに試しに現在はR4に2016/6/16と入力"H28年7月"でスタートしてますが
R4に別の日付　"2016/5/16"を入力した場合"現在のA1セルの値はシート名にできません。"と
出てしまいました。月初めをフリーに変更出来るでしょうか？
何度もすみませんがよろしくお願いします。

（sige） 2016/03/25(金) 09:02

 こちらではR4セルに 2016/5/16 と入力したらH28年6月～(H29)5月のシートが作成されました。
 R4セルが文字列になっているということはありませんよね？
 ちなみに/（スラッシュ）はシート名には使えません。
（se_9） 2016/03/25(金) 09:12

 >Sheets(i).Name = Format(DateAdd("m", i, Range("R4").Value), "m月")
 の前に
 MsgBox Format(DateAdd("m", i, Range("R4").Value), "m月")

 >Sheets(i).Name = Format(DateAdd("m", i, Range("R4").Value), "ge年m月")
 の前に
 MsgBox Sheets(i).Name = Format(DateAdd("m", i, Range("R4").Value), "ge年m月")
 と入れておいてどんなシート名をつけようとしているか確認してはどうか？

 デバッグに慣れていればブレークポイントを設定しておいて確認してもいいが。

（ねむねむ） 2016/03/25(金) 09:18

R4セルが文字列になっているということはありませんよね？無いです。
＞ちなみに/（スラッシュ）はシート名には使えません。
"gyyy年m月"書式ですのでありません。
ただ、シート名が以前のものが残った状態のままマクロ実行しますので
シート名を書き換える作業中に同名処理が発生し、引っかかるのではないかと
思っているのですが、いかがでしょうか？

よろしくお願いします。
（sige） 2016/03/25(金) 12:42

 ＞シート名が以前のものが残った状態のままマクロ実行しますので

 その情報を最初に書いてほしかったです。あまりいい方法ではないかもしれませんが

 Private Sub Worksheet_Change(ByVal Target As Range)

     Dim ws As Worksheet
     Dim i As Long

     For Each ws In Worksheets
         ws.Name = Int(Rnd * 10000)
     Next

     On Error GoTo ERR_HANDLER

     Sheets(1).Name = Format(DateAdd("m", 1, Range("R4").Value), "ge年m月")
     For i = 2 To 12
         If Target.Address(False, False) = "R4" Then
            If Format(DateAdd("m", i - 1, Range("R4").Value), "e") = Format(DateAdd("m", i, Range("R4").Value), "e") Then
               Sheets(i).Name = Format(DateAdd("m", i, Range("R4").Value), "m月")
            Else
               Sheets(i).Name = Format(DateAdd("m", i, Range("R4").Value), "ge年m月")
            End If
         End If
     Next

    Exit Sub

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

 End Sub
（se_9） 2016/03/25(金) 14:34

（se_9）さん
何度もすみません
ありがとうございます。
いい感じで、出来ました。が
同Book内に、Sheet13(祝日)というシートがあるのですが
こちらも名前が書き換えられてしまいます。 i = 2 To 12で指定していると思うのですが
なぜでしょうか？よろしくお願いします。
Sheet構成はSheet1～Sheet13　12迄が表　13は「祝日」設定表です。

（ｓｉｇｅ） 2016/03/25(金) 14:54

 For Each ws In Worksheets
     ws.Name = Int(Rnd * 10000)
 Next

 ↑のコードは乱数を発生させブック内のすべてのシート名を書き換えるというものです。
 それが原因で13枚目のシートも書き換えられてしまうので

 For Each ws In Worksheets
     If ws.Name <> "Sheet13" Then ws.Name = Int(Rnd * 10000)
 Next

 にしてください。
（se_9） 2016/03/25(金) 15:03

（se_9）さん
以下に、修正しましたがダメでした？
シート構成がおかしいのでしょうか　VBエディターで確認しましたが
Sheet13(祝日)は間違ってないのですが・・・

 For Each ws In Worksheets
     If ws.Name <> "Sheet13" Then ws.Name = Int(Rnd * 10000)
 Next
（ｓｉｇｅ） 2016/03/25(金) 15:25

 VBエディタのプロジェクトエクスプローラーで「Sheet13(祝日)」と表示されているのであれば
 そのシートの名前は「Sheet13」ではなく「祝日」ではないのか？
（ねむねむ） 2016/03/25(金) 15:34

 For Each ws In Worksheets
     If ws.Name <> "祝日" Then ws.Name = Int(Rnd * 10000)
 Next
（ねむねむ） さん
ありがとうございました。
上記でうまくいきました。
（se_9）さん
昨日からお世話になり
ありがとうございました。
感謝！

（ｓｉｇｅ） 2016/03/25(金) 18:01

コメント返信：

[ 一覧(最新更新順) ]

[[20160324150041]] 『イベントの実行について』（ｓｉｇｅ） ページの最後に飛ぶ

[[20160324150041]]　『イベントの実行について』（ｓｉｇｅ）　ページの最後に飛ぶ