snasui.com ยินดีต้อนรับ ยินดีต้อนรับสู่กระดานถามตอบ Excel and VBA และอื่น ๆ ที่เป็นมิตรกับทุกท่าน มีไฟล์แนบมหาศาล ช่วยให้ท่านค้นหาและติดตามศึกษาได้โดยง่าย สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ ระบุ Version ของ Excel
Private Sub btsearch_Click()
On Error Resume Next
Dim found As Boolean
Dim r As Range
For Each r In Sheet2.Columns(A).SpecialCells(xlCellTypeConstants)
Sheet2.Activate
Next r
If found Then
If Err.Number = 91 Then
TextBox1.RowSource = "combobox1"
End If
Exit Sub
Else
MsgBox "ไม่มีข้อมูล"
TextBox1.Value = ""
Combobox1.Value = ""
End If
End Sub
โค้ดอยู่ที่ Userform2 ค่ะ รบกวนดูโค้ดค้นหาให้หน่อยค่ะ
เช่น ถ้าเราต้องการค้นหาคำว่า section ให้เลือกข้อมูล section ใน combobox1 แล้วกดปุ่มค้นหาให้แสดงข้อมูลคอลลัมน์ section ที่อยู่ในชีท DATA ทั้งหมดค่ะ ถ้าเราต้องการ Add ข้อมูลเพิ่มลงไปใน ชีท DATA
เช่น ต้องการบันทึกข้อมูลไซส์เสื้อ ให้เลือกข้อมูลเสื้อใน combobox1 แล้วใส่ข้อมูลลงใน textbox1 คือ XXXL แล้วกดปุ่ม ADD ข้อมูลก็จะถูกบันทึกลงในชีท DATA คอลลัมน์ F (เสื้อ)
You do not have the required permissions to view the files attached to this post.
Private Sub ComboBox1_Change()
If ComboBox1.Value = "Section" Then
ListBox1.RowSource = "DATA!B2:B20"
Else
ListBox1.Value = ""
TextBox1.Value = ""
End If
If ComboBox1.Value = "Uniform_Type" Then
ListBox1.RowSource = "DATA!C2:C20"
Else
ListBox1.Value = ""
TextBox1.Value = ""
End If
If ComboBox1.Value = "Size_Shirth" Then
ListBox1.RowSource = "DATA!G2:G20"
Else
ListBox1.Value = ""
TextBox1.Value = ""
End If
If ComboBox1.Value = "Size_Trousere" Then
ListBox1.RowSource = "DATA!H2:H60"
Else
ListBox1.Value = ""
TextBox1.Value = ""
End If
End Sub
Private Sub ClearData()
TextBox1.Text = vbNullString
TextBox1.SetFocus
End Sub
Private Sub btsearch_Click()
On Error Resume Next
Dim found As Boolean
Dim r As Range
For Each r In Sheet2.Columns(A).SpecialCells(xlCellTypeConstants)
Sheet2.Activate
Next r
If found Then
If Err.Number = 91 Then
TextBox1.RowSource = "combobox1"
End If
Exit Sub
Else
MsgBox "äÁèÁÕ¢éÍÁÙÅ"
TextBox1.Value = ""
Combobox1.Value = ""
End If
End Sub
Private Sub CommandButton1_Click()
Dim CurrentRow As Long
Sheet2.Activate
If TextBox1 <> vbNullString Then
CurrentRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(CurrentRow, 1).Value = TextBox1.Text
Call ClearData
Else
MsgBox ("¡Ãسһé͹¢éÍÁÙÅ")
End If
End Sub
Private Sub CommandButton2_Click()
UserForm2.Hide
End Sub
Private Sub UserForm_Initialize()
Call ClearData
Combobox1.AddItem "Section"
Combobox1.AddItem "Uniform_Type"
Combobox1.AddItem "Size_Shirth"
Combobox1.AddItem "Size_Trousere"
Combobox1.AddItem "Group"
Combobox1.AddItem "Position"
Combobox1.AddItem "DEPT"
Combobox1.AddItem "Uniform_No_F"
Combobox1.AddItem "Uniform_No_M"
End Sub
You do not have the required permissions to view the files attached to this post.
Private Sub UserForm_Initialize()
Dim r As Range, rTarget As Range
Call ClearData
With Sheets("data")
Set rTarget = .Range(.Range("a1"), .Cells(1, .Columns.Count).End(xlToLeft)) _
.SpecialCells(xlCellTypeVisible)
Set rTarget = rTarget.SpecialCells(xlCellTypeConstants)
End With
For Each r In rTarget
Combobox1.AddItem r.Value
Next r
End Sub
Private Sub btsearch_Click()
Dim txt As String, r As Range, rList As Range
txt = Combobox1.Value
With Sheets("data")
Set r = .Rows(1).Find(txt)
Set rList = .Range(r.Offset(1, 0), .Cells(.Rows.Count, r.Column).End(xlUp)) _
.SpecialCells(xlCellTypeConstants)
End With
ListBox1.Clear
For Each r In rList
ListBox1.AddItem r.Value
Next r
'If Err.Number = 91 Then
' ListBox1.RowSource = "Combobox1.Value"
' MsgBox "ไม่มีข้อมูล"
'End If
End Sub
'ADD
Private Sub CommandButton1_Click()
Str_text = Combobox1.Text & " " & TextBox1.Text
With ListBox1
.AddItem Str_text
End With
Dim strFind As String
Dim oRng As Range
Dim fRng As Range
Dim i As Long
strFind = Combobox1.Value ' string to find
Set oRng = Worksheets("DATA").Rows(1) ' column to search
Set fRng = oRng.Cells(oRng.Cells.Count)
For i = 1 To Application.CountIf(oRng, strFind & "*")
Set fRng = oRng.Cells.Find(What:=strFind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
After:=fRng, _
MatchCase:=False)
If Not fRng Is Nothing Then
With ListBox1
.AddItem fRng.Offset(1, 0).Value
MsgBox ("ºÑ¹·Ö¡¢éÍÁÙÅàÃÕºÃéÍÂ")
Call ClearData
End With
Else
MsgBox ("¡Ãسһé͹¢éÍÁÙÅ")
End If
Next i
End Sub
You do not have the required permissions to view the files attached to this post.
'ADD
Private Sub CommandButton1_Click()
Str_text = ComboBox1.Text & " " & TextBox1.Text
With ListBox1
.AddItem Str_text
End With
Dim strFind As String
Dim oRng As Range
Dim fRng As Range
Dim i As Long
strFind = ComboBox1.Value ' string to find
Set oRng = Worksheets("DATA").Rows(1) ' column to search
Set fRng = oRng.Cells(oRng.Cells.Count)
For i = 1 To Application.CountIf(oRng, strFind & "*")
Set fRng = oRng.Cells.Find(What:=strFind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
After:=fRng, _
MatchCase:=False)
If Not fRng Is Nothing Then
With ListBox1
.AddItem fRng.Offset(1, 0).Value
MsgBox ("ºÑ¹·Ö¡¢éÍÁÙÅàÃÕºÃéÍÂ")
Call ClearData
End With
Else
MsgBox ("¡Ãسһé͹¢éÍÁÙÅ")
End If
Next i
End Sub
'ADD
Private Sub CommandButton1_Click()
Str_text = ComboBox1.Text & " " & TextBox1.Text
With ListBox1
.AddItem Str_text
End With
Dim strFind As String
Dim oRng As Range
Dim fRng As Range
Dim i As Long
strFind = ComboBox1.Value ' string to find
Set oRng = Worksheets("DATA").Rows(1) ' column to search
Set fRng = oRng.Cells(oRng.Cells.Count)
For i = 1 To Application.CountIf(oRng, strFind & "*")
Set fRng = oRng.Cells.Find(What:=strFind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
After:=fRng, _
MatchCase:=False)
If Not fRng Is Nothing Then
With ListBox1
.AddItem fRng.Offset(1, 0).Value
MsgBox ("ºÑ¹·Ö¡¢éÍÁÙÅàÃÕºÃéÍÂ")
Call ClearData
End With
Else
MsgBox ("¡Ãسһé͹¢éÍÁÙÅ")
End If
Next i
End Sub
'ADD
Private Sub CommandButton1_Click()
' Str_text = Combobox1.Text & " " & TextBox1.Text
' With ListBox1
' .AddItem Str_text
' End With
Dim strFind As String
Dim oRng As Range
Dim fRng As Range
Dim i As Long
strFind = Combobox1.Value ' string to find
Set oRng = Worksheets("DATA").Rows(1) ' column to search
Set fRng = oRng.Cells(oRng.Cells.Count)
For i = 1 To Application.CountIf(oRng, strFind & "*")
Set fRng = oRng.Cells.Find(What:=strFind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
After:=fRng, _
MatchCase:=False)
If Not fRng Is Nothing Then
fRng.End(xlDown).Offset(1, 0).Value = Me.TextBox1.Text
With ListBox1
.AddItem Me.TextBox1.Text 'fRng.Offset(1, 0).Value
MsgBox ("บันทึกข้อมูลเรียบร้อย")
Call ClearData
End With
Else
MsgBox ("กรุณาป้อนข้อมูล")
End If
Next i
End Sub
Private Sub CommandButton3_Click()
Dim lng As Long
Answer = MsgBox("Are you sure you want to delete data from database?", 4 + 48, "Delete database")
If Answer = 6 Then
lng = Application.Match(ListBox1.Value, Worksheets("DATA").Range("E:E"), 0)
Worksheets("DATA").Rows(lng).Delete
Unload Me
ElseIf Answer = 7 Then
End If
End Sub
Dim lng As Long
Dim i As Long, j As Long
With Sheets("DATA")
If Application.CountIf(.Rows(1), Me.Combobox1.Text) = 0 Then
Exit Sub
Else
j = Application.Match(Me.Combobox1.Text, .Rows(1), 0)
End If
End With
i = Me.ListBox1.ListIndex
Answer = MsgBox("Are you sure you want to delete data from database?", 4 + 48, "Delete database")
If Answer = 6 Then
If i <> -1 Then
lng = Application.Match(Me.ListBox1.List(i), Sheets("DATA").Columns(j), 0)
Worksheets("DATA").Cells(lng, j).Delete
Me.ListBox1.RemoveItem Me.ListBox1.ListIndex
Unload Me
End If
End If
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
โปรดทราบ 
