『テキストボックス連動について』（真由美）

いつも大変お世話になっております
教えて頂けたら幸いです

下記の様にComboBox3にTextboxを紐つけていますが
新規入力の場合　textboxを空白にする方法を教えて下さい
現在は　タイトル行が表示されてしまいます

お手数ですが宜しくお願いいたします

Private Sub ComboBox3_Change()

Dim 行

  
    行 = ComboBox3.ListIndex + 1
    TextBox6.Text = Worksheets("LIST").Cells(行 + 1, 3)
    TextBox7.Text = Worksheets("LIST").Cells(行 + 1, 2)
  
End Sub



＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
----
textbox6.text=""ですか
（HARA） 2017/01/05(木) 11:45
----
HARA様
教えていただいて有難う御座います
教えていただいたように書いてしまうと

ComboBoxの利便性が消えてしまいます


ComboBoxをlistboxのように普段は使用しています
リストに無いものをわざわざ　リストに追加しなくても
良い様にComboBoxを使用していますので


リスト選択で無い場合
キーボード使用による新規に入力の場合に
紐つくtextboxを空白にしたいのです



（真由美） 2017/01/05(木) 11:59
----
 新規入力　ということは、コンボボックスのリストからの選択ではなく、ボックスに手入力ということですね。

 Changeイベントを使う問題点は後回しにして

 ComboBox3.LisnIndex が < 0 （0未満、実際には-1) の場合はリストにないということで
 TextBox の値を "" にしてやればいいと思います。

 で、結果オーライですけど、Changeイベントの注意点を。

 たとえばリストが aaaa と abcd の２件だったとして、リストから選ばずにボックス内に入力する場合、
 a とタイプすると、プロパティ設定が規定値であれば、ボックス内は aaaa が選ばれたことになります。
 ListIndex は 0 、Value は aaaa。

 次に a のあとに b をタイプすると、ボックス内は abcd が選ばれた状態で、ListIndexが1、Valueは　abcd。

 結果オーライなんですが、１文字タイプのたびにイベントが発生します。

 このボックスに xyz をいれたとします。

 x で発生。ListIndex は -1。続いて、y。Value が xy で発生。ListIndex は -1。さらに z。Valueがxyzで発生。
 ListIndex は -1。

 繰り返しますが、今回のテーマでは『結果オーライ』です。
 でも、ユーザーフォームの TextBox や ComboBox では 1文字タイプごとに Changeイベントが発生します。

 このことは、ロジックによっては、重大な不具合の原因になる可能性があります。
 十分注意して使うとともに、できれば Changeイベントではなく AfterUpdate や BeforeUpdate という
 Exit系のイベントを使うことをおすすめします。

（β） 2017/01/05(木) 12:10
----
 >ComboBoxの利便性が消えてしまいます 

あれ？じゃぁなんでテキストボックスとコンボボックスを連動させるなんてめんどくさい
ことやってるんですか？

コンボボックスの機能だけで間に合いませんか？

まぁ、いまのままでいくなら、
手入力時に、

ComboBox3.ListIndex
が何を返すかによってIf文で条件分岐したらいいのですが、
コンボボックスに必要な列数を表示するだけで、
十分用が足りると思いますが。。。。



（まっつわん） 2017/01/05(木) 12:37
----
 失礼します。

 >>コンボボックスに必要な列数を表示するだけで、 
 >>十分用が足りると思いますが。。。。 

 確かにドロップダウンリストには表示されていますけど、別コントロールに移ると
 これらが何であったか、見えなくなりますね。

 なので、操作者に対する利便性ということもあるんだろうと思いますよ。
 βであれば、そういった場合は、TextBox ではなく Label に表示しますけど。

 ただ、まっつわんさんの提言は、重要ですね。リスト内に格納しておけば、シート上のセルからの抽出ではなく
 リスト内の値参照で可能ですから。微々たる差とはいえ、メモリー内参照とオブジェクト参照の効率の差異は
 ありますね。

（β） 2017/01/05(木) 12:45
----
β様
コメント有難う御座います

 ＞十分注意して使うとともに、できれば Changeイベントではなく AfterUpdate や BeforeUpdate という
 Exit系のイベントを使うことをおすすめします。

次の所にカーソルを持って行かないと　イベントが発生しないって
文句言われて　致し方なく Changeイベントで逃げています

 ＞ComboBox3.LisnIndex が < 0 （0未満、実際には-1) の場合はリストにないということで
 TextBox の値を "" にしてやればいいと思います。　　

IF条件で頑張って作ってみます

＞TextBox ではなく Label に表示しますけど。
labelだと直接入力が出来なくなりますから　無理だと思います
ただ表示させるだけでなく　別シートに転記させてます

まっつわん様
コメント有難う御座います

＞テキストボックスとコンボボックスを連動させるなんてめんどくさい 
ことやってるんですか？

製品名	　　　　　　　　　　　　製品コード	　　ロッド
カットレタス　（　６入り）	　　3292416	　　6
ケールサラダ（４入り）	　　　　　　3932560	　　4
千切りｷｬﾍﾞﾂ　（１２入り）	　　3943109	　　12

このようなリストで　製品名をCombBoxに割り当てています
リストも100以上あるのですけどね
その時に　製造コード　ロッドをtextboxに表示させています
新製品等が出れば当然リストにはありません
そのためのリスト更新を行ってくれる担当者もいない為
新規入力が絶対不可欠で　又リスト更新の役割
又　入力ミスを防ぐ為の連動を取っています

IFに関しては有難う御座います
考えてみます

皆様本当に有難う御座います
出来なかったら　もう一度質問させていただきます
今後とも宜しくお願いいたします









（真由美） 2017/01/05(木) 13:41
----
最終的には下記のような処理をしています
リストの更新及び　報告書の作成

笑われそうな　駄作ですけど

Private Sub CommandButton1_Click()
If Me.ComboBox3.Value = "" Then
 MsgBox "製品名が未記入", 16
 Me.ComboBox3.SetFocus
 Exit Sub
 End If
If Me.ComboBox6.Value = "" Then
 MsgBox "ケース数が未記入", 16
 Me.ComboBox6.SetFocus
 Exit Sub
  End If
 If Me.ComboBox7.Value = "" Then
 MsgBox "端数が未記入", 16
 Me.ComboBox7.SetFocus
 Exit Sub
  End If
 If Me.ComboBox1.Value = "" Then
 MsgBox "製造工場が未記入", 16
 Me.ComboBox1.SetFocus
 Exit Sub
  End If
 If Me.ComboBox2.Value = "" Then
 MsgBox "移管先が未記入", 16
 Me.ComboBox2.SetFocus
 Exit Sub
 End If
 If Me.ComboBox5.Value = "" Then
 MsgBox "移管理由が未記入", 16
 Me.ComboBox5.SetFocus
 Exit Sub
 End If

Dim intRow, i, k, kl, kk, km
With Worksheets("報告書")
.Range("B3") = Format(TextBox3.Text, "≪YYYY年　M月分≫")
 End With
With Sheets("報告書")
 intRow = .Cells(Rows.Count, "I").End(xlUp).Row
 i = 1
            .Range("A" & i + intRow) = TextBox8.Text
            .Range("F" & i + intRow) = ComboBox3.Text
            .Range("E" & i + intRow) = TextBox7.Text
            .Range("B" & i + intRow) = ComboBox1.Text
            .Range("C" & i + intRow) = ComboBox2.Text
            .Range("D" & i + intRow) = TextBox1.Text
            .Range("G" & i + intRow) = ComboBox6.Text
            .Range("I" & i + intRow) = TextBox5.Text
            .Range("J" & i + intRow) = ComboBox5.Text
            .Range("H" & i + intRow) = ComboBox7.Text
    End With
            
With Worksheets("LIST")
    intRow = .Cells(Rows.Count, "F").End(xlUp).Row
    k = 1
    .Range("F" & k + intRow) = ComboBox5.Text
.Range("$F$1:$F$100").RemoveDuplicates Columns:=1, Header:=xlYes
 End With
 
With Worksheets("LIST")
    intRow = .Cells(Rows.Count, "B").End(xlUp).Row
    kl = 1
    .Range("B" & k + intRow) = TextBox7.Text
    .Range("A" & k + intRow) = ComboBox3.Text
    .Range("C" & k + intRow) = TextBox6.Text

.Range("A:C").RemoveDuplicates Columns:=Array(2), Header:=xlYes
 End With
 
 With Worksheets("LIST")
    intRow = .Cells(Rows.Count, "D").End(xlUp).Row
    kk = 1
    .Range("D" & kk + intRow) = ComboBox1.Text
.Range("$D$1:$D$100").RemoveDuplicates Columns:=1, Header:=xlYes
 End With

 With Worksheets("LIST")
    intRow = .Cells(Rows.Count, "D").End(xlUp).Row
    km = 1
    .Range("D" & kk + intRow) = ComboBox2.Text
.Range("$D$1:$D$100").RemoveDuplicates Columns:=1, Header:=xlYes
 End With
 
 

 Call リスト並び替え
 
    Dim r As Range
    Set r = Worksheets("LIST").Range("D2:D1000")
    ComboBox1.List = r.Value
    ComboBox2.List = r.Value
    ComboBox3.List = r.Offset(0, -3).Value
    ComboBox5.List = r.Offset(0, 2).Value
     ComboBox6.List = r.Offset(1, 4).Value
     ComboBox7.List = r.Offset(0, 4).Value
      ComboBox7.ListIndex = 0
      
      ComboBox3.Text = ""
 TextBox7.Text = ""
 TextBox6.Text = ""
 TextBox5.Text = ""
 ComboBox6.Text = ""
 ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox5.Text = ""
ComboBox7.Text = 0
      
Sheets("報告書").Select

End Sub
（真由美） 2017/01/05(木) 13:57
----
 >>次の所にカーソルを持って行かないと　イベントが発生しないって 
 >>文句言われて　致し方なく Changeイベントで逃げています 

 そうなんですよね。いくら仕組みが云々といっても、特にComboBoxの場合は、操作者はリストから選択する場合が
 ほとんどですから、「ちゃんと選んでいるじゃないか！！」とクレームが入りますよね。

 なので、COmboBoxの場合は「十分に配慮したコード記述」を行うということが必要でしょうね。
 その最大のポイントは Changeイベントの最初に ListIndex が 0以上か未満かを判定することでしょうね。

 >>labelだと直接入力が出来なくなりますから　無理だと思います 
 >>ただ表示させるだけでなく　別シートに転記させてます 

 そういう要件であれば、もちろん TextBoxですね。


（β） 2017/01/05(木) 14:20
----
＞新規入力が絶対不可欠で　又リスト更新の役割 
なるほど。
リストを見るだけでなく更新も出来るようにしたいということなのですね＾＾
まぁ、やり方はさまざまありますが、一例を。。。

Option Explicit

Dim mEFlag As Boolean

Private Sub UserForm_Initialize()
    Dim rngList As Range
    
    With Worksheets("LIST").Range("A1").CurrentRegion
        Set rngList = Intersect(.Cells, .Offset(1))
    End With
    
    With Me.ComboBox1
        .RowSource = rngList.Address(, , , True)
        .ColumnCount = 1
        .ColumnWidths = "100"
        .ColumnHeads = False
    End With
End Sub

Private Sub ComboBox1_Change()
    Dim ix As Long
    Dim rngRow As Range

    If mEFlag = True Then Exit Sub
    
    ix = Me.ComboBox1.ListIndex
    With Application.Range(Me.ComboBox1.RowSource)

    If ix >= 0 Then
        Set rngRow = .Rows(ix + 1)
    Else
        Set rngRow = .Rows(.Rows.Count + 1)
    End If
    End With
    With rngRow
        Me.TextBox1.Text = .Cells(2).Value
        Me.TextBox1.Tag = .Cells(2).Address(, , , True)
        Me.TextBox2.Text = .Cells(3).Value
        Me.TextBox2.Tag = .Cells(3).Address(, , , True)
        Me.ComboBox1.Tag = .Cells(1).Address(, , , True)
    End With
End Sub

'登録ボタン押下
Private Sub CommandButton1_Click()
    mEFlag = True
    With Me.TextBox1
        Application.Range(.Tag).Value = .Text
    End With
    With Me.TextBox2
        Application.Range(.Tag).Value = .Text
    End With
    With Me.ComboBox1
        Application.Range(.Tag).Value = .Text
        .RowSource = Application.Range(.Tag).CurrentRegion.Address(, , , True)
    End With
    mEFlag = False
End Sub

おっと、並び替えも要るのか。。。
製品コードでいいのかな。。。
ちょっとテストしてみよ＾＾

（まっつわん） 2017/01/05(木) 15:05
----
β様

ご賛同有難う御座います

まっつわん様

いたせりつくせり状態でないと
私の体がいくつあっても足りません
笑い

コードまで有難う御座います
試してみて　使えるところは使わせていただきます
（真由美） 2017/01/05(木) 15:13
----
'登録ボタン押下
Private Sub CommandButton1_Click()
    mEFlag = True
    With Me.TextBox1
        Application.Range(.Tag).Value = .Text
        .Text = ""
        .Tag = ""
    End With
    With Me.TextBox2
        Application.Range(.Tag).Value = .Text
        .Text = ""
        .Tag = ""
    End With
    With Me.ComboBox1
        Application.Range(.Tag).Value = .Text
        .Text = ""
        .Tag = ""
        .ListIndex = -1
    End With
    With Application.Range(Me.ComboBox1.RowSource).CurrentRegion
        With Intersect(.Cells, .Offset(1))
            Me.ComboBox1.RowSource = .Address(, , , True)
            .Sort key1:=.Cells(2), Order1:=xlAscending, Header:=xlNo
        End With
    End With
    mEFlag = False
End Sub

＞私の体がいくつあっても足りません 
あぁ、調べながらだとそうなりますね。
まぁ、僕も調べながら書いてるのですが、
だいたいの構造っていうか、なんとなくある程度は覚えてないと、
調べるのがつらいかもですね。

コンボボックスに値をセットする方法も3つあるので、
RowSource
List
Add
みたいなのがあったよな〜。。。
というのは何回かやっぱ作ってみないと覚えないのかなぁとは思います。
RowSouceでセットすると、シート上の変更がそのまま反映されるようですね＾＾
並べ替えしたいときは便利そうです＾＾

アドレスをTagプロパティに記録しておいて利用してますが、
コンボボックスのListIndexがいつでも利用可能ですので、
無駄なことをしてます＾＾；

（まっつわん） 2017/01/05(木) 15:37
----
まっつわん様

有難う御座います
＞RowSouceでセットすると、シート上の変更がそのまま反映されるようですね＾＾

って事は　毎回セットしなくてもいい

下記は不要ってことになりますね　毎回セットしてましたから　　　
チャレンジしてみます
  Dim r As Range
    Set r = Worksheets("LIST").Range("D2:D1000")
    ComboBox1.List = r.Value
    ComboBox2.List = r.Value
    ComboBox3.List = r.Offset(0, -3).Value
    ComboBox5.List = r.Offset(0, 2).Value
     ComboBox6.List = r.Offset(1, 4).Value
     ComboBox7.List = r.Offset(0, 4).Value
      ComboBox7.ListIndex = 0

本当に勉強になります
有難う御座います
（真由美） 2017/01/05(木) 15:55
----
 コード拝見。

 いくつか気になる点

 ・最後の、各ComboBoxにリストを再セットしているところですが、ComboBox7 が H2 からの H列、ComboBox6 が H3 からのH列。
　つまり、ComboBox6には H2 がリストにはセットされないのですが、これでいいのでしょうか？
　ちょっと不自然な感じがします。

 ・上記も含めて、シートのレイアウトというか実態が見えないのですが、報告書シートには、最終行の次の同じ１行にセット。
　一方、Listシートには、A:C列、D列、F列　それぞれ、別ものとして、それぞれの列の最終セルの次にセットした上で
　重複を削除していますね。
　まぁ、そういった性格のシートなんでしょうね。

 ・各ComboBox へのリストセットは Initialize でもあると思いますけど、アップされたコードの最後のものと
　共通サブルーティン化することもできますかね。

 と、まぁ見えないところもありますけど、コード記述の一例として、報告書シートと Listシートに転記する部分のみ。
 (17:00 ちょこっと変更）

    With Sheets("報告書")
        .Range("B3") = Format(TextBox3.Text, "≪YYYY年　M月分≫")
        With Cells(Rows.Count, "I").End(xlUp).Offset(1).EntireRow
            .Range("A1:J1").Value = Array(TextBox8.Text, ComboBox1.Text, ComboBox2.Text, TextBox1.Text, TextBox7.Text, _
                                          ComboBox3.Text, ComboBox6.Text, ComboBox7.Text, TextBox5.Text, ComboBox5.Text)
        End With
    End With
    
    With Worksheets("LIST")
        .Cells(Rows.Count, "F").End(xlUp).Offset(1).Value = ComboBox5.Text
        .Cells(Rows.Count, "B").End(xlUp).Offset(1).EntireRow.Range("A1:C1").Value = Array(ComboBox3.Text, TextBox7.Text, TextBox6.Text)
        With .Cells(Rows.Count, "D").End(xlUp)
            .Offset(1).Value = ComboBox1.Text
            .Offset(2).Value = ComboBox2.Text
        End With
        .Range("F1", .Range("F" & Rows.Count).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlYes
        .Range("B1", .Range("B" & Rows.Count).End(xlUp)).EntireRow.Columns("A:C").RemoveDuplicates Columns:=Array(2), Header:=xlYes
        .Range("D1", .Range("D" & Rows.Count).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlYes
    End With

（β） 2017/01/05(木) 16:06
----
β様
本当に有難う御座います

＞・最後の、各ComboBoxにリストを再セットしているところですが、ComboBox7 が H2 からの H列、ComboBox6 が H3 からのH列。
　つまり、ComboBox6には H2 がリストにはセットされないのですが、これでいいのでしょうか？
　ちょっと不自然な感じがします。

気がつきませんでした　(;ﾟдﾟ)ｧ....

　
＞・上記も含めて、シートのレイアウトというか実態が見えないのですが、報告書シートには、最終行の次の同じ１行にセット。
　一方、Listシートには、A:C列、D列、F列　それぞれ、別ものとして、それぞれの列の最終セルの次にセットした上で
　重複を削除していますね。
　まぁ、そういった性格のシートなんでしょうね。

リストの下のほうにあっても　直接入力してしまう方がいらっしゃるので
とりあえず　保険的にです

＞ ・各ComboBox へのリストセットは Initialize でもあると思いますけど、アップされたコードの最後のものと
　共通サブルーティン化することもできますかね。

一行入力した後　新規書き込みの場合　一度開きなおさないと　リストに反映されない為
2行目以降にも同じものを使う可能性がある為
毎回リストを読み込ませる　って感じの素人的発想です

こんな短いコードまで　有難う御座います

あくまでも　　このファイルは　テンプレートとして使用してまして
他の名前でで保存して終了みたいな感じです

Private Sub CommandButton2_Click()
 On Error Resume Next
 If Sheets("報告書").Range("B6") = "" Then
   MsgBox "データ未入力です", 16
  Exit Sub

 
 Else
 Dim Target
    Sheets("報告書").Select
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    
     Range("D6").Select
    Selection.Copy
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Range("i1").NumberFormatLocal = "yyyymmdd"
    ActiveSheet.Name = Range("i1").Text
 
 
 Dim TargetPath As String
Dim myFname As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False

TargetPath = ThisWorkbook.Path
ActiveWorkbook.SaveAs TargetPath & "\" & _
   Range("i1").Text & Range("B6").Text & Range("C6").Text
  
    With Application
        .DisplayAlerts = False
        Sheets("LIST").Delete
        Sheets("報告書").Delete
        .DisplayAlerts = True
    End With
   ActiveWorkbook.Save
  ActiveWorkbook.Close
  End If
End Sub







（真由美） 2017/01/05(木) 17:22
----
＞あくまでも　　このファイルは　テンプレートとして使用してまして 
＞他の名前でで保存して終了みたいな感じです 
添削してほしいってことなのかな？

Private Sub CommandButton2_Click()
    Dim wb As Workbook
    Dim sName As String

    If Sheets("報告書").Range("B6").Value = "" Then
        MsgBox "データ未入力です", vbCritical
        Exit Sub
    Else
        Sheets("報告書").Copy
        Set wb = Workbooks(Workbooks.Count)
        With wb.Sheets(1)
            sName = Format(.Range("D6").Value, "yyyymmdd")
            .Range("I1").Value = sName
            .Name = sName
            wb.Close TargetPath & "\" & sName _
                     & .Range("B6").Text & .Range("C6").Text
            Set wb = Nothing
        End With
    End If
End Sub
（まっつわん） 2017/01/05(木) 19:49
----
まっつわん様
おはよう御座います

たぶん突っ込みどころ満載かなって思って

あげてみました
本当に今回勉強になりました

有難う御座います
今後ともども宜しくお願いいたします
（真由美） 2017/01/06(金) 08:59