[[20170728094322]] 『「3つ以上のコンボボックス連携について」(Voume1』(関数がにがて) >>BOT

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

 

『「3つ以上のコンボボックス連携について」(Voume11] について』(関数がにがて)

投稿
[[20081217082039]] 『3つ以上のコンボボックス連携について』(Voume11) 
について...

 データが入っているbookのデータをユーザーフォームを利用し、他の抽出表bookに入力したいと思って、上記のコンボボックスの連携について勉強しております。
 試しにデータbookに抽出sheetを作成し、入力するVBAを『3つ以上のコンボボックス連携について』を利用し書いてみました。

 ユーザーフォームには
 TextBox1〜4、ComboBox1〜5、CommandButton1が配置。TextBoxには日時など入力、ComboBoxでデータを抽出し、CommandButtonで入力する形にしています。

 Option Explicit

 Private dic() As Scripting.Dictionary
 Private dicMax As Long
 Private Const ComboCount = 5 'ComboBoxの数

 Private Sub UserForm_Initialize()

    Dim v, sKey As String
    Dim i As Long, j As Long, k As Long, n As Long

    With Worksheets("データSheet") '対象シート
        v = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)). _
              Resize(, ComboCount).Value
    End With

    dicMax = UBound(v) * ComboCount + 1 ' Dictionaryの数
    ReDim dic(1 To dicMax)
    Set dic(1) = New Scripting.Dictionary
    k = 1
    For i = 1 To UBound(v)
        n = 1
        For j = 1 To ComboCount - 1
            If Not IsEmpty(v(i, j)) Then sKey = v(i, j)
            If Not dic(n).Exists(sKey) Then
                k = k + 1
                dic(n)(sKey) = k       '★ItemにkeySting用dic番号
                Set dic(k) = New Scripting.Dictionary
                n = k
            Else
                n = dic(n)(sKey)
            End If
        Next
        dic(n).Item(v(i, j)) = Empty
    Next
    dicMax = k

    With ComboBox1
        .List = Application.Transpose(Array(dic(1).Keys, dic(1).Items))
    End With
 End Sub

 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim i As Long
    For i = dicMax To 1 Step -1
        Set dic(i) = Nothing
    Next
 End Sub

 Private Sub ComboBox1_Change()
    ComboBox_Update ComboBox1, ComboBox2
 End Sub

 Private Sub ComboBox2_Change()
    ComboBox_Update ComboBox2, ComboBox3
 End Sub

 Private Sub ComboBox3_Change()
    ComboBox_Update ComboBox3, ComboBox4
 End Sub

  Private Sub ComboBox4_Change()
    ComboBox_Update ComboBox4, ComboBox5
 End Sub

 Private Sub ComboBox_Update(ByVal List1 As MSForms.ComboBox, _
                             ByVal List2 As MSForms.ComboBox)
    Dim idx As Long
    Dim n As Long, i As Long
    Dim v
    idx = List1.ListIndex
    If idx < 0 Then Exit Sub
    n = List1.List(idx, 1)
    With List2
        i = 0
        .Clear
        For Each v In dic(n).Keys
            .AddItem v
            .List(i, 1) = dic(n).Item(v)
            i = i + 1
        Next
    End With

 End Sub

 Private Sub ComboBox5_Click()
    Dim idx As Long
    With ComboBox5
        idx = .ListIndex
        If idx < 0 Then Exit Sub
    End With
 End Sub

 Private Sub CommandButton1_Click()

 '抽出表の空欄へ入力
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
        Cells(lastRow, 2) = TextBox2
        Cells(lastRow, 3) = TextBox3
        Cells(lastRow, 4) = TextBox4
        Cells(lastRow, 5) = TextBox1
        Cells(lastRow, 6) = ComboBox2 & " " & ComboBox3 & "→" & ComboBox4
        Cells(lastRow, 8) = ComboBox5
 End Sub

  これをデータbook上ではなく、他bookで使えるようにするにはどうしたら良いのでしょうか?
 過去ログを色々みてみましたがよくわからないので、教えてください。
 よろしくお願いいたします。

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


他ブックというのは、数個ですか? それとも、どこかのフォルダ以下にある数百のブックですか?

数個であれば、フォームをファイルとして「エクスポート」し、他ブックで「インポート」すれば、同じように使えるようになります。マクロの編集画面で、ツリー左側にある「UserForm1」等を右クリックしてみてください。

数百であれば、マクロを使うブックとデータのあるブックに分けて、マクロを使うのは1ブック上だけにするのが良いと思います。
(???) 2017/07/28(金) 11:37


 ???さん

 他bookはマクロbookに出来ないので(.xlsで保存)、作業者PCの個々のPERSONAL_ELSBに
 ユーザーフォームを入れ、データbookをサーバーに入れショートカット&読み取りで開き
 他bookに入力したいと考えています。
 データbookを参照できる方法が知りたいです。
 よろしくお願いいたします。
(関数がにがて) 2017/07/28(金) 14:46

 アドインを調べてみてはいかがでしょうか?
(稲葉) 2017/07/28(金) 15:53

 稲葉さん

 まだまだ勉強中なもので、アドインとは何でしょうか?
 どうやったら調べられますか?
(関数がにがて) 2017/07/28(金) 16:05

現状は Worksheets("データSheet") にある元データを読み込んでいますが、これは ActiveBook を省略しています。 別ブックを参照するのなら、WorkBooks.Open で開き、そこにあるシートを対象にすれば良いです。

ここで少し面倒なのが、データbookは普段開かずにおいて、マクロ内でOpenしCloseする、という流れでOKかどうか、です。 いやいや、誰かが更新している場合があるから、既に手動でブックを開いている場合と開いていない場合があるよ、とかだと、それをチェックするコードも考える必要があるでしょう。

なお、PERSONL を使うのは、関係者全員に同じものを置く必要があり、あとでPCを変えたら使えなくなった、というトラブルの元なので、お薦めはしません。 更に、Excel2010 を使っているのに、.xls の拡張子を使い続けるのは、常に互換モードが動作するので、お薦めしません。 .xlsx、.xlsm に移行すべきです。(互換モードだと、例えば非表示行や列を設定したまま保存し、開き直すと、画面レイアウトが崩れる、とかのトラブルの元です)

そして、アドインですが…、ご自分で探せないようなら、止めておいた方が良いような? これも後で、なんかExcel起動する度にエラーが出る、なんてトラブルの元になりそう。
(???) 2017/07/28(金) 16:09


 横から思いつきですみませんでした。

 ???さんがおっしゃるとおり、
 マクロが使えないブックと同時にマクロが保存されたブックを開き(手動になる)
 データ場所を指定してユーザーフォームに表示させる、が一番無難な解決方法だと思います。

(稲葉) 2017/07/28(金) 16:18


 ???さん、稲葉さん

 色々な方法があるのですね。
 アドインは自分だけで試してみたいと思います。

 データbookはマクロ内でOpenしCloseする流れで大丈夫です。
 更新は私が行うので、他の人が作業中に行いません。
 (参照だけで読み取り専用で開いていて欲しいくらいです。)
 また、拡張子は入力後のエクセルbookを他の会社(報告先)へ渡すからです。
 渡した先のバージョンが2003でも開けるように.xlsでわざわざ保存しています。

 PERSONLも関係者全員にインポートするのでPCを変えたらという心配もありません。

 WoekBooks.Openで開いた時、他の人も開くことはできるのでしょうか?
(関数がにがて) 2017/07/28(金) 16:42

 >(参照だけで読み取り専用で開いていて欲しいくらいです。)
 > WoekBooks.Openで開いた時、他の人も開くことはできるのでしょうか?
 手動で開いた場合と同じですよ
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_130_04.html
https://msdn.microsoft.com/ja-jp/library/office/ff194819.aspx
http://excelvba.pc-users.net/fol4/4_3.html

 マクロブックのthisworkbookモジュールに、Workbook_Openイベント作り
 workbooks.open "データファイルファイルパス" , ReadOnly:=True
 とすると、読み取り専用で開き

 マクロブックのthisworkbookモジュールに、Workbook_BeforeCloseイベント作り
 workbooks("ファイル名").close SaveChanges:=False
 で保存しないで閉じます

 関数が苦手さんならできると思ったので、参考程度に紹介します

(稲葉) 2017/07/29(土) 06:00


 稲葉さん

 ありがとうございます。
 試してみます。
 また、報告に来ます。
(関数がにがて) 2017/07/31(月) 10:51

 ???さん、稲葉さん

 月末・月初の作業があり時間がかかりましたが、
 お二人のご意見を参考に試行錯誤した所、思っていたことができました。
 ありがとうございました。

 もっと勉強したら仕事の手間が減らすことができそうな感覚が出来ました。
 また、わからないことが出来ましたら質問に来ます。
 その時はよろしくお願いいたします。
(関数がにがて) 2017/08/03(木) 14:26

コメント返信:

[ 一覧(最新更新順) ]


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