[發問] With .ComboBox2為何沒反應呢???

本帖最後由 sillykin 於 2017-4-5 22:55 編輯

Dim ColNr As Integer
Dim Arr() As String

Private Sub CommandButton4_Click()
Dim MaxColNr As Integer
With Sheet4
      MaxColNr = .Cells(50, .Columns.Count).End(xlShiftToLeft).Column
End With
If ColNr = MaxColNr Then

      MsgBox "這已經是最後的紀錄了！"
Else
      ColNr = ColNr + 1
      With UserForm12
         .TextBox2 = Sheet4.Cells(50, ColNr)
         .TextBox3 = Sheet4.Cells(51, ColNr)
         .TextBox4 = Sheet4.Cells(52, ColNr)
         .TextBox5 = Sheet4.Cells(53, ColNr)
         .TextBox6 = Sheet4.Cells(54, ColNr)
         .TextBox7 = Sheet4.Cells(55, ColNr)
         .ComboBox1.ListIndex = TextBox4

         With .ComboBox2
            For i = 0 To .ListCount - 1
                  If Left(Arr(i), 1) = Sheet4.Cells(53, ColNr) Then
                     .ListIndex = i
                     Exit For
                  End If
            Next i
         End With

         With .ComboBox3
            For i = 0 To .ListCount - 1
                  If Left(Arr(i), 2) = Sheet4.Cells(54, ColNr) Then
                     .ListIndex = i
                     Exit For
                  End If
            Next i
         End With
      End With
End If
End Sub

Private Sub CommandButton5_Click()
If ColNr = 2 Then

      MsgBox "這已經是第一個記錄!"
Else
      ColNr = ColNr - 1
      With UserForm12

         .TextBox2 = Sheet4.Cells(50, ColNr)
         .TextBox3 = Sheet4.Cells(51, ColNr)
         .TextBox4 = Sheet4.Cells(52, ColNr)
         .TextBox5 = Sheet4.Cells(53, ColNr)
         .TextBox6 = Sheet4.Cells(54, ColNr)
         .TextBox7 = Sheet4.Cells(55, ColNr)
         .ComboBox1.ListIndex = TextBox4


         With .ComboBox2
            For i = 0 To .ListCount - 1
                  If Left(Arr(i), 1) = Sheet4.Cells(53, ColNr) Then
                     .ListIndex = i
                     Exit For
                  End If
            Next i
         End With

         With .ComboBox3
            For i = 0 To .ListCount - 1
                  If Left(Arr(i), 2) = Sheet4.Cells(54, ColNr) Then
                     .ListIndex = i
                     Exit For
                  End If
            Next i
         End With
      End With
End If
End Sub

'-----------------------------------------------------------------------------------------

Private Sub UserForm_Initialize()

ColNr = 2
With UserForm12

         .TextBox2 = Sheet4.Cells(50, ColNr)
         .TextBox3 = Sheet4.Cells(51, ColNr)
         .TextBox4 = Sheet4.Cells(52, ColNr)
         .TextBox5 = Sheet4.Cells(53, ColNr)
         .TextBox6 = Sheet4.Cells(54, ColNr)
         .TextBox7 = Sheet4.Cells(55, ColNr)
End With
   ComboBox1.AddItem "0"
   ComboBox1.AddItem "1"





   With ComboBox2
   .AddItem "C"
   .AddItem "E"
   .AddItem "G"
   .AddItem "L"
   .AddItem "N "
   .AddItem "S"

   ReDim Arr(.ListCount - 1)
      For i = 0 To .ListCount - 1
         Arr(i) = .List(i)
      Next i
      For i = 0 To .ListCount - 1
         If Left(Arr(i), 1) = Sheet4.Cells(53, ColNr) Then
            .ListIndex = i
            Exit For
         End If
      Next i
End With




   With ComboBox3
   .AddItem "X0"
   .AddItem "XA"
   .AddItem "XB"
   .AddItem "XC"
   .AddItem "XD"
   .AddItem "XE"
   .AddItem "XF "
   .AddItem "XG"
   .AddItem "XH"
   .AddItem "XI:"
   .AddItem "XJ"
   .AddItem "XK"
   .AddItem "XL"




      ReDim Arr(.ListCount - 1)
      For i = 0 To .ListCount - 1
         Arr(i) = .List(i)
      Next i
      For i = 0 To .ListCount - 1
         If Left(Arr(i), 2) = Sheet4.Cells(54, ColNr) Then
            .ListIndex = i
            Exit For
         End If
      Next i
End With

End Sub