Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : Excel 2016)
データの入力規則で条件とメッセージの設定を自動で変更
投稿日時: 19/03/20 16:31:56
投稿者: okamoto0812

データの入力規則で条件とメッセージを設定しています。
この条件とメッセージの変更を300以上のファイルにかけなければならなくなりました。
書式は統一されています。
理想では一括で変更したいのですが,VBAなどを用いてワンクリックで自動で変更できるような策はないでしょうか?
どんなアイデアでもよいので,この大変な作業をすこしでも楽にできる方法はないでしょうか?
ご指導のほどよろしくお願いします。

回答
投稿日時: 19/03/20 18:14:20
投稿者: sk

引用:
データの入力規則で条件とメッセージを設定しています。
この条件とメッセージの変更を300以上のファイルにかけなければならなくなりました。

基本的には Validation オブジェクトを使用なされば良いでしょう。
 
引用:
書式は統一されています。
理想では一括で変更したいのですが,VBAなどを用いて
ワンクリックで自動で変更できるような策はないでしょうか?

当面の問題は、それぞれのブック/ワークシートにおける
データの入力規則の修正対象となるセル範囲を
どのようにして識別、参照するかです。
(関係のないセルまで修正するわけにはいかない)
 
例えば、アクティブブックの "Sheet1" という名前のワークシートの
「 A1 セルと同じ入力規則が設定されているセル範囲」の
入力規則を修正する場合は、次のようなコードを実行することになるはず。
 
(標準モジュール)
--------------------------------------------------------------------
Private Sub subTest()
     
    Const SheetName As String = "Sheet1"
    Const ValidationCellAddress As String = "A1"
     
    Dim xlsWorkbook As Excel.Workbook
    Dim xlsWorksheet As Excel.Worksheet
    Dim xlsRange As Excel.Range
     
    Set xlsWorkbook = ActiveWorkbook
     
    Set xlsWorksheet = xlsWorkbook.Worksheets(SheetName)
         
    On Error Resume Next
    Set xlsRange = xlsWorksheet.Range(ValidationCellAddress).SpecialCells(xlCellTypeSameValidation)
    If Err.Number = 0 Then
        On Error GoTo 0
        With xlsRange.Validation
            .Modify Type:=xlValidateWholeNumber, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="0", _
                    Formula2:="100"
            .InputTitle = "入力時メッセージのタイトル"
            .InputMessage = "入力時メッセージ"
            .ErrorTitle = "エラーメッセージのタイトル"
            .ErrorMessage = "エラーメッセージ"
        End With
    Else
        MsgBox Err.Number & ": " & Err.Description, _
               vbCritical, _
               "実行時エラー"
    End If
    Err.Clear
    On Error GoTo 0
     
    Set xlsRange = Nothing
    Set xlsWorksheet = Nothing
    Set xlsWorkbook = Nothing
 
End Sub
--------------------------------------------------------------------

回答
投稿日時: 19/03/20 18:25:13
投稿者: WinArrow
投稿者のウェブサイトに移動

「検索と選択」の「ジャンプ」−「セル範囲」−「データの入力規則」
という操作をすると
入力規則設定セルが全て選択することができます。
 
この操作をマクロの記録でコードを作成し、
変更対象・・・・何を以て、変更対象とするかは、自分で決めてください。
の判断して、内容を変更し、
Modifyメソッドで変更する
というオードにカスタマイズする
ことができるでしょう。
 
このコードを対象とするブックに適用すればよいと思います。
 
 

回答
投稿日時: 19/03/21 14:45:43
投稿者: simple

多数のブックを順次処理する例は、例えば、下記の即効テクニックが参考になるかもしれない。
https://www.moug.net/tech/exvba/0060003.html

投稿日時: 19/03/25 12:38:08
投稿者: okamoto0812

みなさんお返事ありがとうございます。
やはり,VBAを使ってしか方法がないようですね・・・
マクロだと一つ一つにかけていくしか方法がなさそうですが・・・
マクロで覚えさせる方法に頑張ってチャレンジしてみようと思います!
またご指導をお願いします。

回答
投稿日時: 19/03/25 15:43:30
投稿者: WinArrow
投稿者のウェブサイトに移動

okamoto0812 さんの引用:
みなさんお返事ありがとうございます。
やはり,VBAを使ってしか方法がないようですね・・・
マクロだと一つ一つにかけていくしか方法がなさそうですが・・・
マクロで覚えさせる方法に頑張ってチャレンジしてみようと思います!
またご指導をお願いします。

 
VBAとマクロを別のものと考えている雰囲気なんですが・・・
 
基本的にVBAとマクロはおないものです。
かたい話のなりますが、VBAはプログラム言語の事です。
VBAで記述したプログラムをマクロといいます。
 
処理手順としては
以下、入力規則の設定内容を変更する対象を「当該」と書きます。
(1)対象とするフォルダ(多分)に入っているファイルの中から当該ファイル(ブック)を取得します。
(2)抽出したファイル(ブック)に含まれるシートごとに当該セルを取得します。
(3)当該セルに設定されている入力規則の設定内容を変更ー更新します。
(4)当該ブックを上書き保存します。
 
このような手順でコードを組み立てればよいと思います。
(3)の入力規則の設定内容変更の際、変更不要なのか、変更要なのかの説明がない・・・・
 この件は回答者が考えることはできません。
 
できれば1つのファイルー1つのシートでを対象にコードを作成し、
検証し、意図した結果になってから、全てのファイルの拡大していく方がよろしいかと思います。
 

回答
投稿日時: 19/03/29 22:12:38
投稿者: simple

既に指摘されているように、

sk さんの引用:
当面の問題は、それぞれのブック/ワークシートにおける
データの入力規則の修正対象となるセル範囲を
どのようにして識別、参照するかです。
(関係のないセルまで修正するわけにはいかない)
がまずはポイントでしょう。
 
そのブックの各シートに設定されている入力規則は書換対象のものだけですか?
・書換対象のものだけなら話は簡単でしょう。
  すべての入力規則を、同一のものに書き換えるだけです。
・問題は、その他のものがある場合。
  書換対象だけに限定するためには、どのような条件で入力規則を見分けるかです。
  → ここは、質問者さんに日本語で説明していただかないと、誰も助言はできません。
 
まずはこのあたりから、例を示して行ったらどうでしょうか。

トピックに返信