[[20140731083339]]　『全シートのG列の空白行に同じリストを表示させたａx（hisa）　ページの最後に飛ぶ

『全シートのG列の空白行に同じリストを表示させたい』（hisa）

よろしくお願いします。

 同じ表のシートが20以上入っているブックがあります。
 このG5:G40間の空白セルに同じリストを表示させたいのですが
 マクロで可能でしょうか？
 リストは「リスト」というシートのA5:A15に入っているとし
 入力規制では、日本語入力にして、「エラーメッセージ」では
 「無効なデータが入力されたらエラーメッセージを表示する」を
 オフにしたいと思っています。
 手作業で行うと思っていたのですが、同じようなブックが
 複数あること、すでにG列には複数の入力があり
 一括選択でコピペが困難であることなどで、
 マクロでできれば助かるな～と思った次第でした。
 よろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 「リスト」は名前の定義でリストシートのA5:A15が定義してください。
 全く入力されていないことも考慮して、SpecialCellsを使いませんでした。
 ブックをコピーしてバックアップを取ってから実行してください。
Sub 入力規則()
    Dim sh As Worksheet
    Dim c As Range
    Dim r As Range
    For Each sh In Sheets
        If sh.Name <> "リスト" Then
            Set r = Nothing
            For Each c In sh.Range("G5:G40")
                If c.Value = "" Then
                    If r Is Nothing Then
                        Set r = c
                    Else
                        Set r = Union(r, c)
                    End If
                End If
            Next c
            With r.Validation
                .Delete
                .Add _
                    Type:=xlValidateList _
                    , AlertStyle:=xlValidAlertStop _
                    , Operator:=xlBetween _
                    , Formula1:="=リスト"
                .IgnoreBlank = True
                .InCellDropdown = True
                .IMEMode = xlIMEModeHiragana
                .ShowInput = False
                .ShowError = False
            End With
        End If
    Next sh
End Sub
（稲葉） 2014/07/31(木) 11:36

稲葉さま

 早速の書き込みありがとうございます。
 教えて頂いたものを標準モジュールに貼り付けし
 「リスト」シートのA5:A15を「リスト」で名義の定義をしました。
 しかし、
  .Add _
    Type:=xlValidateList _
    , AlertStyle:=xlValidAlertStop _
    , Operator:=xlBetween _
    , Formula1:="=リスト"
 この部分でエラーが出てしまいました
 （実行時エラー1004 アプリケーションの定義または、オブジェクトの定義エラー）
 何が考えられるでしょうか。（私の理解が足りず変な事してるかも…）

 また私の説明不足で大変申し訳ありませんが
 ブックには「同じ表」が入った以外にもシートが複数あります。
 ですので、「選択したシートのみ」でマクロを発動させたいのですが
 可能でしょうか。
 よろしくお願いします。

（hisa） 2014/07/31(木) 13:37

 ＞しかし、
 ＞ .Add _
 ＞   Type:=xlValidateList _
 ＞   , AlertStyle:=xlValidAlertStop _
 ＞   , Operator:=xlBetween _
 ＞   , Formula1:="=リスト"
 ＞この部分でエラーが出てしまいました
 この部分ですが、複数のシートを選択した状態で実行しましたか？
 試してもらえば分かるのですが、（というか出来ないから質問したのでしょうが）
 複数シートを選択した状態では、条件付き書式の設定がクリック出来ません。

 ですので、一度選択されたシート状態を変数に移し、選択状態を解除して、
 1シートずつ設定しなければなりません。

 下記に置き換えた後、設定したシートを選択してから実行してみてください。
Sub 入力規則()
    Dim sh As Worksheet
    Dim c As Range
    Dim r As Range
    Dim s_sheet As Sheets
    Set s_sheet = ActiveWindow.SelectedSheets
    Sheets(1).Select
    For Each sh In s_sheet
        Set r = Nothing
        For Each c In sh.Range("G5:G40")
            If c.Value = "" Then
                If r Is Nothing Then
                    Set r = c
                Else
                    Set r = Union(r, c)
                End If
            End If
        Next c
        If Not r Is Nothing Then
            With r.Validation
                .Delete
                .Add _
                    Type:=xlValidateList _
                    , AlertStyle:=xlValidAlertStop _
                    , Operator:=xlBetween _
                    , Formula1:="=リスト"
                .IgnoreBlank = True
                .InCellDropdown = True
                .IMEMode = xlIMEModeHiragana
                .ShowInput = False
                .ShowError = False
            End With
        End If
    Next sh
End Sub

（稲葉） 2014/07/31(木) 14:36

稲葉さま

 ありがとうございました。
 選択したシートの設定ができました。
 とても助かります。
 お世話になりました。
（hisa） 2014/07/31(木) 19:18

コメント返信：

[ 一覧(最新更新順) ]

[[20140731083339]] 『全シートのG列の空白行に同じリストを表示させたａx（hisa） ページの最後に飛ぶ

[[20140731083339]]　『全シートのG列の空白行に同じリストを表示させたａx（hisa）　ページの最後に飛ぶ