EXCEL + VBA for Data Search and Update

程式碼1



Private Sub ComboBox2_Change()

  Dim sh As Worksheet
  Set sh = ThisWorkbook.Sheets("Sheet1")
  Dim i As Integer

If Me.ComboBox.Value <> "" Then
    If ComboBox1.MatchFound = True Then
      i = Application.Match(VBA.CLng(Me.ComboBox1.Value), sh.Range("A:A"), 0)
      
      Me.TextBox.Value = sh.Range("B" & i).Value
      
      If sh.Range("C" & i).Value = "Man" Then Me.OptionButton1.Value = True
      If sh.Range("C" & i).Value = "Woman" Then Me.OptionButton2.Value = True
  
      If sh.Range("D" & i).Value = "X" Then Me.CheckBox1.Value = True: Me.CheckBox2.Value = False: Me.CheckBox3.Value = False
      If sh.Range("D" & i).Value = "Y" Then Me.CheckBox1.Value = False: Me.CheckBox2.Value = True: Me.CheckBox3.Value = False
      If sh.Range("D" & i).Value = "Z" Then Me.CheckBox1.Value = False: Me.CheckBox2.Value = False: Me.CheckBox3.Value = True
  
      Me.ComboBox2.Value = sh.Range("E" & i).Value
    
      Exit Sub
    End If
  
  If ComboBox1.MatchFound = False Then
    Me.TextBox.Value = "Wrong Data"
    Me.OptionButton1.Value = False
    Me.OptionButton2.Value = False

    Me.CheckBox1.Value = False
    Me.CheckBox2.Value = False
    Me.CheckBox3.Value = False

    Exit Sub
  End If
  
End If
End Sub

= = = = = = = = = = 

Private Sub CommandFix_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim n As Long
n = Application.Match(VBA.CLng(Me.ComboBox1.Value), sh.Range("A:A"), 0)
 
  If Me.object.Value = "" Then
     MsgBox "information"
     Exit Sub
  End If
  
  If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False Then
     MsgBox "choose"
     Exit Sub
  End If
  
  If Me.CheckBox1.Value = True/False And Me.CheckBox2.Value = True/False And Me.CheckBox3.Value = True/False Then
       MsgBox "information"
     Exit Sub
  End If
  
  sh.Range("A" & n).Value = Me.ComboBox1.Value
  sh.Range("B" & n).Value = Me.TextBox.Value

  If Me.OptionButton1.Value = True Then sh.Range("C" & n).Value = "Man"
  If Me.OptionButton2.Value = True Then sh.Range("C" & n).Value = "Woman"

  If Me.CheckBox1.Value = True Then sh.Range("D" & n).Value = "X"
  If Me.CheckBox2.Value = True Then sh.Range("D" & n).Value = "Y"
  If Me.CheckBox3.Value = True Then sh.Range("D" & n).Value = "X"

  sh.Range("E" & n).Value = Me.ComboBox2.Value

End Sub

= = = = = = = = = = 

Private Sub CommandClear_Click()
Me.object.Value = "" or False

End Sub

= = = = = = = = = = 

Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim i As Integer

  Me.ComboBox1.Clear
  Me.ComboBox1.AddItem ""

  For i = 3 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    Me.ComboBox1.AddItem sh.Range("A" & i).Value
  Next i

  With Me.ComboBox2
       .Clear
       .AddItem ""
  End With

End Sub