Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2013)
入力規則のプルダウンリストを設定したい
投稿日時: 20/05/31 09:31:29
投稿者: くろんぼ

EXCEL VBA でValidationオブジェクトを使用し、入力規則のリストを設定しようとしていますが
思った通りの事ができません。
 
Sheet1に以下のようなリストがあり、
 
項目1 項目2
 A    a
      b
           c
  B d
           e
           f
  C g
           h
  D i
          j
          k
 
Sheet2でも同様の項目(項目1、項目2)を作成し、これをプルダウンリストで設定したいです。
そこで2点困っています。
 
1.Sheet1で項目1のリスト設定するのに
 
With Workbooks(ActBook)
    .Worksheets("Sheet2").Cells(7, 1).Validation.Delete
    .Worksheets("Sheet2").Cells(7, 1).Validation.Add _
        Type:=xlValidateList, _
        Formula1:="=Sheet1!" & Range(Cells(7, 1), Cells(17, 1)).Address
End With
というコードを記載したところ、空白もリストに入ってしまいます。
 A
 
    
  B
    
    
  C
    
  D
 
 
というリストになってしまいます。空白行はリストに含めないようにするにはどうしたら良いでしょうか?
 
2.Sheet2で項目1のAを選択した場合、項目2のリストにはa,b,cを、Bを選択した場合には
d,e,fをリストに表示させたいのですが、どのようにしたら良いでしょうか?

 
教えて頂けますよう宜しくお願い致します。

投稿日時: 20/05/31 09:35:36
投稿者: くろんぼ

くろんぼ さんの引用:
EXCEL VBA でValidationオブジェクトを使用し、入力規則のリストを設定しようとしていますが
思った通りの事ができません。(一部、間違いがありましたので、赤字で訂正します)
 
Sheet1に以下のようなリストがあり、
 
項目1 項目2
 A    a
      b
           c
  B d
           e
           f
  C g
           h
  D i
          j
          k
 
Sheet2でも同様の項目(項目1、項目2)を作成し、これをプルダウンリストで設定したいです。
そこで2点困っています。
 
1.Sheet2で項目1のリスト設定するのに
 
With Workbooks(ActBook)
    .Worksheets("Sheet2").Cells(7, 1).Validation.Delete
    .Worksheets("Sheet2").Cells(7, 1).Validation.Add _
        Type:=xlValidateList, _
        Formula1:="=Sheet1!" & Range(Cells(7, 1), Cells(17, 1)).Address
End With
というコードを記載したところ、空白もリストに入ってしまいます。
 A
 
    
  B
    
    
  C
    
  D
 
 
というリストになってしまいます。空白行はリストに含めないようにするにはどうしたら良いでしょうか?
 
2.Sheet2で項目1のAを選択した場合、項目2のリストにはa,b,cを、Bを選択した場合には
d,e,fをリストに表示させたいのですが、どのようにしたら良いでしょうか?

 
教えて頂けますよう宜しくお願い致します。

回答
投稿日時: 20/05/31 14:10:51
投稿者: 半平太

元リストは連続していると楽なんですが、
そう言うレイアウトにする訳には行かないですか?
つまり、

            
行  _A_  _B_  _C_  _D_   または 行  _A_  _B_  _C_  _D_ 
  1   A    a    b    c               1   A    B    C    D  
  2   B    d    e    f               2   a    d    g    i  
  3   C    g    h                    3   b    e    h    j  
  4   D    i    j    k               4   c    f         k  

回答
投稿日時: 20/05/31 16:55:00
投稿者: takesi

こんなかんじで
 

kWs = "Sheet5"
With Worksheets(kWs).Range("A1")
  Set setRang = .Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues Or xlNumbers)
  setKmoku = ""
    For Each trgItem In setRang.Areas
      For i = 1 To trgItem.Count
        setKmoku = setKmoku & .Cells(trgItem.Row + i - 1, 1).Text & ","
      Next
    Next
    Worksheets("Sheet3").Cells(7, 1).Validation.Delete
    Worksheets("Sheet3").Cells(7, 1).Validation.Add _
        Type:=xlValidateList, _
        Formula1:=setKmoku
End With
[/code]

回答
投稿日時: 20/05/31 17:21:25
投稿者: takesi

項目2は
http://office-qa.com/Excel/ex164.htm
とかの方法で

投稿日時: 20/06/02 00:49:47
投稿者: くろんぼ

takesiさん
 
ありがとうございます。
1につきましては、出来ました!
が、以下の部分のコードの意味が理解できません。
もし可能であれば、教えて頂けないでしょうか。
 
     For i = 1 To trgItem.Count
        setKmoku = setKmoku & .Cells(trgItem.Row + i - 1, 1).Text & ","
      Next
 
また2に関しまして、INDIRECT関数を使用するということでしょうか?
 
 
半平太さん
 
ありがとうございます。
今後、レイアウト変更についても考えてみたいと思います。

回答
投稿日時: 20/06/02 07:13:44
投稿者: MMYS

くろんぼ さんの引用:

     For i = 1 To trgItem.Count
        setKmoku = setKmoku & .Cells(trgItem.Row + i - 1, 1).Text & ","
      Next

ステップ実行しながら、変数値を確認しましょう。
内容ですが、下記の意味は下記で理解出来ますか。
 
  With Worksheets("Sheet3").Cells(7, 1).Validation
    .Delete
    .Add Type:=xlValidateList, _
               Formula1:="りんご,苺,バナナ,桃"
  End With

また、リンク先の仕組みも理解しましょう。
リスト1が行(見出し)
リスト2がそれぞれの列
つまり、表自体が作りやすくなってる。
 
 

投稿日時: 20/06/02 23:37:55
投稿者: くろんぼ

  With Worksheets("Sheet3").Cells(7, 1).Validation
    .Delete
    .Add Type:=xlValidateList, _
               Formula1:="りんご,苺,バナナ,桃"
  End With

 
上記のコードは理解できますが、
     For i = 1 To trgItem.Count
        setKmoku = setKmoku & .Cells(trgItem.Row + i - 1, 1).Text & ","
      Next
のiが1〜3となり、trgItemが何を表しているのか、よくわからなくなってきました。

回答
投稿日時: 20/06/03 20:28:45
投稿者: MMYS

くろんぼ さんの引用:

のiが1〜3となり、trgItemが何を表しているのか、よくわからなくなってきました。

私が作成したコードでは有りませんが、
コードを理解するには、ステップ実行と変数値の確認です。
 
    Set setRang = .Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues Or xlNumbers)
    Debug.Print setRang.Address
    setKmoku = ""
    For Each trgItem In setRang.Areas
      Debug.Print trgItem.Address, trgItem.Count
      For i = 1 To trgItem.Count
        setKmoku = setKmoku & .Cells(trgItem.Row + i - 1, 1).Text & ","
      Next
    Next
    Debug.Print setKmoku
 
そして、セル値が下記の場合、setRangのアドレスはどうなりますか。
特に[A7:A9]の連続はどう処理しますか。
 
  A
1 項目
2
3 a
4
5 b
6
7 c
8 d
9 e
 

回答
投稿日時: 20/06/03 20:37:27
投稿者: simple

横から失礼します。

    Set setRang = .Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues Or xlNumbers)
    setKmoku = ""
    For Each trgItem In setRang.Areas
        For i = 1 To trgItem.Count
            setKmoku = setKmoku & .Cells(trgItem.Row + i - 1, 1).Text & ","
        Next
    Next
のところですね。
 
意図としては、
setRangを構成している各Areaに対して、
さらに、そのAreaの各要素の文字列を連結する、
という処理かと思います。(Areaはヘルプを参照)
 
しかし、2段階にする必要もなく、
単に、
    For Each e In setRang
        setKmoku = setKmoku & e.Text & ","
    Next
と連結すればよいと思います。( Dim e As Rangeが必要)
これで同じ結果が得られます。
 
なお、最後にくっついている","を取ったほうがよいかも知れません。
setKmoku = Left(setKmoku, Len(setKmoku)-1)

投稿日時: 20/06/05 00:02:51
投稿者: くろんぼ

MMYSさん、simpleさん
 
ありがとうございます。
理解できました。