Snasui Excel Forum

Code: Select all

Private Sub ComboBox01_Change()

On Error Resume Next
Sheets("list_other").Select
Range("B2").Select
Do While True
    If ActiveCell.Value = ComboBox01.Text Then
       TextBox01.Text = ActiveCell.Offset(0, 1).Value
       TextBox10.Text = ActiveCell.Offset(0, 2).Value
       ComboBox09.Text = "ES05B0003014"
       'TextBox08.Text = Sheets("Log").Range("A1")
       AddP.Enabled = True
       Exit Sub
    End If
            ActiveCell.Offset(1, 0).Select
Loop

Code: Select all

Private Sub ComboBox01_Change()
    Dim i As Integer
    With Sheets("cal")
        i = Application.CountIf(.Range("b3", .Range("b" & _
            .Rows.Count).End(xlUp)), ComboBox01.Text)
        If i > 0 Then
            MsgBox "<Your message>"
        End If
    End With
'Other code

Code: Select all

Private Sub ComboBox01_Change()
On Error Resume Next

Dim i As Integer
With Sheets("cal")        '  ตรวจสอบค่าซ้ำกันใน ComboBox01
    i = Application.CountIf(.Range("b3", .Range("b" & _
        .Rows.Count).End(xlUp)), ComboBox01.Text)
    If i > 0 Then
        MsgBox "< มีชื่ออยู่ใน List แล้ว >"
        ComboBox01.Text = ""
    End If
End With

'Other code
Sheets("list_other").Select
Range("B2").Select
Do While True
    If ActiveCell.Value = ComboBox01.Text Then
       TextBox01.Text = ActiveCell.Offset(0, 1).Value
       TextBox10.Text = ActiveCell.Offset(0, 2).Value
       ComboBox09.Text = "ES05B0003014"
       AddP.Enabled = True
       Exit Sub
    End If
            ActiveCell.Offset(1, 0).Select
Loop

End Sub

Code: Select all

Dim cncl As Boolean

Code: Select all

Private Sub ComboBox01_Change()
On Error Resume Next
If cncl Then
    cncl = False
    Exit Sub
End If
Dim i As Integer
With Sheets("cal")        '  ตรวจสอบค่าซ้ำกันใน ComboBox01
    i = Application.CountIf(.Range("b3", .Range("b" & _
        .Rows.Count).End(xlUp)), ComboBox01.Text)
    If i > 0 Then
        MsgBox "< มีชื่ออยู่ใน List แล้ว >"
        cncl = True
        ComboBox01.Text = ""
        Exit Sub
    End If
End With
'Other code]

Code: Select all

Private Sub AddP_Click()
'Other code
                ActiveCell.Offset(0, 23).Value = TextBox34.Text
                cncl = True
                ComboBox01.Text = ""

                TextBox21.Text = ""
'Other code