[[20170622061052]] 『シート名自動変更について』(JK) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『シート名自動変更について』(JK)

いつもお世話になっています。

シート名の自動変更についての質問です。

This Workbookに下記記述
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target.Range("A1").Value
End Sub

各シートに下記記述
Public Sub SheetName()
ActiveSheet.Name = Range("A1").Value
End Sub
をしています。
そしてボタン配置でシート名を変更していますが、
これを自動化できないかの質問となります。

なお、セルA1は棚卸を行った日付が入り
「201706棚卸」と表示形式を変更しております。
   ↑
 このネーミングをシート名に自動変更したいと考えています。

シート構成は1ファイル4シートあり、それぞれ四半期毎に棚卸を行います。

これらの情報で自動変更可否が分かりますでしょうか?
※不足情報のご指摘がありましたら、追記させてもらいます。

どうぞよろしくお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 自動とは、なにかのタイミングで変える、ということだと思いますが、
 何をしたらシート名が変わればよいですか?
(稲葉) 2017/06/22(木) 06:32

表示された(棚卸という文字列がついた)ものをシート名にしたい、ということですか?
もしそうなら、
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Address = "$A$1" Then Sh.Name = Target.Text
End Sub
ではどうですか?
重ねてシートモジュールを使う必要性がわかりません。上だけでよいのでは?

(γ) 2017/06/22(木) 06:36


稲葉様

 こんばんは。こちらの思いだけで書いており、説明不足申し訳ございません。

シートのセルA1に6月なら201706棚卸、9月なら201709棚卸と表示しています。
シート名は今までこれをコピーし、シート名変更をし、貼り付けています。
毎回が面倒なのと、変更漏れがちのため、ファイルを開いた時に
セルA1の値をそのままシート名にできないかというのが質問の主旨となります。

私なりに説明書きをしましたが、伝わりますでしょうか。

γ様

 いつもお世話になります。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Address = "$A$1" Then Sh.Name = Target.Text
End Sub
確かにこれだけで動きますね。そこでもう1点教えてください。
今回の場合、対象セル(セルA1)を編集またはダブルクリック等しないと変化しませんでした。
ワークブックを開いた時もしくはシートを選択した時かに変化させる方法はありますでしょうか?
※それとも私のEXCELの設定がおかしいのか…。

ただ、対象セルをダブルクリックしたりF2キーで編集モードにした後は
希望通り、セルの値がシート名になることは確認できました。

いつもいつもありがとうございます。かなり助かります。

(JK) 2017/06/23(金) 03:39


 横から失礼します。

 >ファイルを開いた時にセルA1の値をそのままシート名にできないかというのが質問の主旨となります

 それでしたらThisWorkbookモジュールやシートモジュールを使わずに標準モジュールに

 Sub Auto_Open()

     Dim ws As Worksheet

     For Each ws In Worksheets
         If ws.Range("A1").Value <> "" Then ws.Name = ws.Range("A1").Value
     Next

 End Sub

 みたいな感じでいいのではないでしょうか?マクロを有効にしたら自動的に実行されます。
(bi) 2017/06/23(金) 05:31

>※それとも私のEXCELの設定がおかしいのか…。
おかしくはないはずです。
コードの指示通りに動いているように見えます。
あなたがコードの意味を理解されていないだけのような気がします。
 
イベントプロシージャの種類、それぞれの意味を理解することが第一です。
例えば、
ワークブックのイベントプロシージャ
http://excel-ubara.com/excelvba1/EXCELVBA424.html
ワークシートのイベントプロシージャ
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_event.html
 
あとは、
ご自分で仕様を整理して、それに沿ってコードを書いてください。
(γ) 2017/06/23(金) 07:17

 よくわかりました。
 ブックを開いた時に開始するイベントは、二つあります。
 ThisWorkbookモジュールに記載するWorkbook_Openイベントと
 標準モジュールに記載するAuto_Openイベント?です。
 私は特段の理由がない限り、ThisWorkBookモジュールのWorkbook_Openをイベントを使用します。
 それぞれの違いは、以下のサイトを参照してください。
http://www.moug.net/tech/exvba/0060077.html

 イベントからプロシージャを呼び出しているので、手動でもイベントでもどちらでも使えるようにしてあります。
 参考にしてください。

    Option Explicit

    Private Sub Workbook_Open()
        Call シート名変更
    End Sub
    Sub シート名変更()
        Dim WS     As Worksheet
        Dim ShNm   As String
        Dim x      As Variant
        Dim ErrMsg As String
        Dim ChkWS  As Worksheet
        For Each WS In Sheets
            If Not WS.Name Like "*棚卸" Then '※17:16追加
                ShNm = WS.Range("A1").Text
                If ShNm Like "*棚卸" Then
                    '//シート名を全角→半角に変換
                    ShNm = StrConv(ShNm, vbNarrow)

                    '//シート名に使用できない文字を空白に置換
                    For Each x In Array(":", "\", "?", "[", "]", "/", ":")
                        ShNm = Application.WorksheetFunction.Substitute(ShNm, x, "")
                    Next x

                    '//シート名の重複チェック
                    On Error Resume Next
                        Set ChkWS = Sheets(ShNm)
                    On Error GoTo 0

                    '//チェック中のシート名以外で重複があれば、エラーメッセージを作る
                    If ChkWS Is Nothing Then
                        WS.Name = ShNm
                    ElseIf WS.Name <> ShNm Then
                        ErrMsg = ErrMsg & WS.Name & vbNewLine
                    End If
                End If
            End If
        Next WS
        If ErrMsg <> "" Then
            MsgBox "次のシートでシート名の重複がありました。" & vbNewLine & ErrMsg
        End If
    End Sub
 17:16少しブラッシュアップ
(稲葉) 2017/06/23(金) 12:40

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.