1246767769 【ExcelVBA 入力規則】

良回答には200ポイント差し上げます。
(ただし、当方初心者なので、注釈をかなりたっぷりめにつけていただけると幸いです。)

画像をご覧ください。


ボタン一発で全てのシートに対して、入力規則を設定したいです。
シート[マスタ]は、入力規則に設定するリストです。
A列は、設定したいシート名。
B列は、リストの内容です。

なおかつ、A列の値が連続していさえすれば、
行を挿入・削除しても、
それぞれのシートに入力可能な値が
自動的に変化するようにする方法をご教示願います。

(セル範囲に名前を付ける方法は、
挿入する場所によっては意図するように動きませんでした。)


よろしくお願いいたします。

回答の条件
  • 1人2回まで
  • 登録:2009/07/05 13:22:50
  • 終了:2009/07/12 13:25:02

回答(1件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692009/07/05 14:56:30

ポイント60pt

まず、それぞれのシートには既に入力規則が設定されているとします。

(仮に設定されていないとすると、後から設定するにはセルが指定されていないので)

それぞれのシートの入力規則は =シート名 の名前を利用します。


それで、動的に名前の範囲を変更する為にマスターシートのチェンジイベントに次のコードを記入します。

(VBEでプロジェクトエクスプローラの画面でSheet?(マスタ)をクリックして開くエリアです)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastRow As Long
    Dim i As Long
    Dim startRow As Long
    Dim nc As Integer
    
    '名前の削除
    nc = Names.Count
    For i = 1 To nc
        Names(1).Delete
    Next
    
    With Worksheets("マスタ")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        startRow = 2
        For i = 3 To lastRow + 1
            If .Cells(i - 1, 1).Value <> .Cells(i, 1).Value Then
                If .Cells(i - 1, 1).Value <> "" Then
                    Names.Add Name:=.Cells(i - 1, 1).Value, RefersTo:=.Range(Cells(startRow, 2), Cells(i - 1, 2))
                End If
                startRow = i
            End If
        Next
    End With
End Sub


オーバーヘッドではありますが、コードではシートが変化するたびに名前を定義しなおします。

A列の名前は連続してあることが前提です。途中で切れたり飛び飛びで同じ名前が2箇所以上あると最後のまとまりが名前の範囲となります。

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません