Page 1 of 1

Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Tue Jan 28, 2020 2:59 pm
by nongmay_za
รบกวนอาจารย์ช่วยดูโค๊ดให้หน่อยค่ะว่าผิดตรงไหน คือค้นหาแล้วแก้ไขข้อมูลแต่เวลาที่เรากดอัพเดทข้อมูลจะไปบันทึกที่แถวข้างบน ซึ่งจริงๆต้องการให้บันทึกที่แถวเดิมค่ะ
ตัวอย่างโค๊ด

Private Sub CommandButton3_Click()
Dim irow As Long
Dim ws As Worksheet
On Error Resume Next
If Err.Number = 13 Then
MsgBox "ไม่พบข้อมูล"
GoTo iNet
End If
If TextBox1.Text = "" Then
MsgBox "กรุณากรอกข้อมูล"
GoTo iNet
End If
Set ws = Worksheets("ซื้อหน้าบ้าน")
If Application.CountIf(ws.Range("b:b"), TextBox1.Value) > 0 Then
irow = Application.Match(CDbl(TextBox1.Value), ws.Range("b:b"), 0)
msgRepns = MsgBox("ต้องการแก้ไขข้อมูลคลิก Yes หากต้องการยกเลิกคลิก NO", vbYesNo)
Else
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If
Test.xlsm

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Tue Jan 28, 2020 7:53 pm
by puriwutpokin
ควรไว้โค้ดให้เป็นโค้ดตามนี้ด้วยครับ
ปรับตามนี้ครับ

Code: Select all

Private Sub CommandButton3_Click()
Dim irow As Long
    Dim ws As Worksheet
    On Error Resume Next
    If Err.Number = 13 Then
MsgBox "ไม่พบข้อมูล"
GoTo iNet
    End If
    If TextBox1.Text = "" Then
MsgBox "กรุณากรอกข้อมูล"
GoTo iNet
 End If
            Set ws = Worksheets("ซื้อหน้าบ้าน")
             If Application.CountIf(ws.Range("a:a"), TextBox63.Value) > 0 Then
        irow = Application.Match(CDbl(TextBox1.Value), ws.Range("b:b"), 0) + 4
        msgRepns = MsgBox("ต้องการแก้ไขข้อมูลคลิก Yes หากต้องการยกเลิกคลิก NO", vbYesNo)
Else
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
    
        ws.Cells(irow, 2) = TextBox1.Value
ws.Cells(irow, 1).Value = Me.ComboBox20.Value
ws.Cells(irow, 2).Value = Me.TextBox1.Value
ws.Cells(irow, 3).Value = Me.ComboBox2.Value
ws.Cells(irow, 4).Value = Me.TextBox60.Value
ws.Cells(irow, 5).Value = Me.TextBox61.Value
ws.Cells(irow, 6).Value = Me.TextBox58.Value
ws.Cells(irow, 7).Value = Me.ComboBox3.Value
ws.Cells(irow, 8).Value = Me.TextBox2.Value
ws.Cells(irow, 9).Value = Me.TextBox3.Value
ws.Cells(irow, 10).Value = Me.TextBox4.Value
ws.Cells(irow, 11).Value = Me.ComboBox4.Value
ws.Cells(irow, 12).Value = Me.TextBox5.Value
ws.Cells(irow, 13).Value = Me.TextBox6.Value
ws.Cells(irow, 14).Value = Me.TextBox7.Value
ws.Cells(irow, 15).Value = Me.ComboBox5.Value
ws.Cells(irow, 16).Value = Me.TextBox8.Value
ws.Cells(irow, 17).Value = Me.TextBox9.Value
ws.Cells(irow, 18).Value = Me.TextBox10.Value
ws.Cells(irow, 19).Value = Me.ComboBox6.Value
ws.Cells(irow, 20).Value = Me.TextBox11.Value
ws.Cells(irow, 21).Value = Me.TextBox12.Value
ws.Cells(irow, 22).Value = Me.TextBox13.Value
ws.Cells(irow, 23).Value = Me.ComboBox7.Value
ws.Cells(irow, 24).Value = Me.TextBox14.Value
ws.Cells(irow, 25).Value = Me.TextBox15.Value
ws.Cells(irow, 26).Value = Me.TextBox16.Value
ws.Cells(irow, 27).Value = Me.ComboBox8.Value
ws.Cells(irow, 28).Value = Me.TextBox17.Value
ws.Cells(irow, 29).Value = Me.TextBox18.Value
ws.Cells(irow, 30).Value = Me.TextBox19.Value
ws.Cells(irow, 31).Value = Me.ComboBox9.Value
ws.Cells(irow, 32).Value = Me.TextBox20.Value
ws.Cells(irow, 33).Value = Me.TextBox21.Value
ws.Cells(irow, 34).Value = Me.TextBox22.Value
ws.Cells(irow, 35).Value = Me.ComboBox10.Value
ws.Cells(irow, 36).Value = Me.TextBox23.Value
ws.Cells(irow, 37).Value = Me.TextBox24.Value
ws.Cells(irow, 38).Value = Me.TextBox25.Value
ws.Cells(irow, 39).Value = Me.ComboBox11.Value
ws.Cells(irow, 40).Value = Me.TextBox26.Value
ws.Cells(irow, 41).Value = Me.TextBox27.Value
ws.Cells(irow, 42).Value = Me.TextBox28.Value
ws.Cells(irow, 43).Value = Me.ComboBox12.Value
ws.Cells(irow, 44).Value = Me.TextBox29.Value
ws.Cells(irow, 45).Value = Me.TextBox30.Value
ws.Cells(irow, 46).Value = Me.TextBox31.Value
ws.Cells(irow, 47).Value = Me.ComboBox13.Value
ws.Cells(irow, 48).Value = Me.TextBox32.Value
ws.Cells(irow, 49).Value = Me.TextBox33.Value
ws.Cells(irow, 50).Value = Me.TextBox34.Value
ws.Cells(irow, 51).Value = Me.ComboBox14.Value
ws.Cells(irow, 52).Value = Me.TextBox35.Value
ws.Cells(irow, 53).Value = Me.TextBox36.Value
ws.Cells(irow, 54).Value = Me.TextBox37.Value
ws.Cells(irow, 55).Value = Me.ComboBox15.Value
ws.Cells(irow, 56).Value = Me.TextBox38.Value
ws.Cells(irow, 57).Value = Me.TextBox39.Value
ws.Cells(irow, 58).Value = Me.TextBox40.Value
ws.Cells(irow, 59).Value = Me.ComboBox16.Value
ws.Cells(irow, 60).Value = Me.TextBox41.Value
ws.Cells(irow, 61).Value = Me.TextBox42.Value
ws.Cells(irow, 62).Value = Me.TextBox43.Value
ws.Cells(irow, 63).Value = Me.ComboBox17.Value
ws.Cells(irow, 64).Value = Me.TextBox44.Value
ws.Cells(irow, 65).Value = Me.TextBox46.Value
ws.Cells(irow, 66).Value = Me.TextBox47.Value
ws.Cells(irow, 67).Value = Me.ComboBox18.Value
ws.Cells(irow, 68).Value = Me.TextBox48.Value
ws.Cells(irow, 69).Value = Me.TextBox50.Value
ws.Cells(irow, 70).Value = Me.TextBox51.Value
ws.Cells(irow, 71).Value = Me.ComboBox19.Value
ws.Cells(irow, 72).Value = Me.TextBox52.Value
ws.Cells(irow, 73).Value = Me.TextBox54.Value
ws.Cells(irow, 74).Value = Me.TextBox55.Value
ws.Cells(irow, 75).Value = Me.TextBox62.Value

ComboBox20.Text = " "
TextBox1.Text = " "
ComboBox2.Text = " "
TextBox60.Text = " "
TextBox61.Text = " "
TextBox58.Text = " "
ComboBox3.Text = " "
TextBox2.Text = " "
TextBox3.Text = " "
TextBox4.Text = " "
ComboBox4.Text = " "
TextBox5.Text = " "
TextBox6.Text = " "
TextBox7.Text = " "
ComboBox5.Text = " "
TextBox8.Text = " "
TextBox9.Text = " "
TextBox10.Text = " "
ComboBox6.Text = " "
TextBox11.Text = " "
TextBox12.Text = " "
TextBox13.Text = " "
ComboBox7.Text = " "
TextBox14.Text = " "
TextBox15.Text = " "
TextBox16.Text = " "
ComboBox8.Text = " "
TextBox17.Text = " "
TextBox18.Text = " "
TextBox19.Text = " "
ComboBox9.Text = " "
TextBox20.Text = " "
TextBox21.Text = " "
TextBox22.Text = " "
ComboBox10.Text = " "
TextBox23.Text = " "
TextBox24.Text = " "
TextBox25.Text = " "
ComboBox11.Text = " "
TextBox26.Text = " "
TextBox27.Text = " "
TextBox28.Text = " "
ComboBox12.Text = " "
TextBox29.Text = " "
TextBox30.Text = " "
TextBox31.Text = " "
ComboBox13.Text = " "
TextBox32.Text = " "
TextBox33.Text = " "
TextBox34.Text = " "
ComboBox14.Text = " "
TextBox35.Text = " "
TextBox36.Text = " "
TextBox37.Text = " "
ComboBox15.Text = " "
TextBox38.Text = " "
TextBox39.Text = " "
TextBox40.Text = " "
ComboBox16.Text = " "
TextBox41.Text = " "
TextBox42.Text = " "
TextBox43.Text = " "
ComboBox17.Text = " "
TextBox44.Text = " "
TextBox46.Text = " "
TextBox47.Text = " "
ComboBox18.Text = " "
TextBox48.Text = " "
TextBox50.Text = " "
TextBox51.Text = " "
ComboBox19.Text = " "
TextBox52.Text = " "
TextBox54.Text = " "
TextBox55.Text = " "
TextBox62.Text = " "

        MsgBox "ข้อมูลอัพเดทแล้ว"
        ActiveWorkbook.Save
iNet:
End Sub

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Wed Jan 29, 2020 10:09 am
by nongmay_za
อาจารย์ค่ะลองไปปรับแบบที่อาจารย์บอกแล้วแต่ยังเป็นเหมือนเดิมเลยค่ะ คือเวลาที่กดอัพเดทข้อมูลก็จะไปบันทึกที่แถวอื่นค่ะ รบกวนอาจารย์อีกรอบน่ะค่ะ
ขอบคุณค่ะ

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Wed Jan 29, 2020 8:18 pm
by snasui
:D กรุณายกตัวอย่างค่าที่กรอก ปุ่มที่คลิก และบรรทัดที่แสดงผลลัพธ์ หากถูกต้องจะแสดงผลลัพธ์ที่บรรทัดไหน จะได้เข้าใจตรงกันครับ

ควรโพสต์ Code ให้แสดงเป็น Code พร้อมทั้งแจ้งตำแหน่งว่า Code อยู่ตรงไหน อ่านกฎการใช้บอร์ดด้านบนประกอบครับ :roll:

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Thu Jan 30, 2020 11:00 am
by nongmay_za
ตัวอย่างค่ะ 1
ค้นหาเลขที่บิล 10013 กดค้นหาซื้อหน้าบ้าน ข้อมูลขึ้นมา เราทำการแก้ไขจำนวนเงิน เป็น 1313 แล้วกดอัพเดทซื้อหน้าบ้าน
ซึ่งต้องการให้บันทึกแถวที่30 แต่พอกดอัพเดทแล้วไปบันทึกแถวที่34ค่ะ

ตัวอย่างค่ะ 2
ค้นหาเลขที่บิล 10049 กดค้นหาซื้อหน้าบ้าน ข้อมูลขึ้นมา เราทำการแก้ไขจำนวนเงิน เป็น 4949 แล้วกดอัพเดทซื้อหน้าบ้าน
ซึ่งต้องการให้บันทึกแถวที่52 แต่พอกดอัพเดทแล้วไปบันทึกแถวที่54ค่ะ

โค๊ดอยู่ตรง UseForm บันทึกรายการซื้อขาย

ตัวอย่างโค๊ด ไม่รู้ว่าผิดตรงไหนค่ะ

Code: Select all

Private Sub CommandButton3_Click()
Dim irow As Long
    Dim ws As Worksheet
    On Error Resume Next
    If Err.Number = 13 Then
MsgBox "ไม่พบข้อมูล"
GoTo iNet
    End If
    If TextBox1.Text = "" Then
MsgBox "กรุณากรอกข้อมูล"
GoTo iNet
 End If
            Set ws = Worksheets("ซื้อหน้าบ้าน")
             If Application.CountIf(ws.Range("a:a"), TextBox63.Value) > 0 Then
        irow = Application.Match(CDbl(TextBox1.Value), ws.Range("b:b"), 0) + 4
        msgRepns = MsgBox("ต้องการแก้ไขข้อมูลคลิก Yes หากต้องการยกเลิกคลิก NO", vbYesNo)
Else
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Thu Jan 30, 2020 8:49 pm
by puriwutpokin
ลองดูครับ

Code: Select all

'Other code...
    Dim irow As Long
    Dim ws As Worksheet
    On Error Resume Next
    If Err.Number = 13 Then
MsgBox "ไม่พบข้อมูล"
GoTo iNet
    End If
    If TextBox63.Text = "" Then
MsgBox "กรุณากรอกข้อมูล"
GoTo iNet
 End If
            Set ws = Worksheets("ซื้อหน้าบ้าน")
            Set sRange = ws.Range("a4:a" & ws.Range("a" & ws.Rows.Count).End(xlUp).Row)
             If Application.CountIf(sRange, TextBox63.Value) > 0 Then
        irow = Application.Match(CDbl(TextBox63.Value), sRange, 0) + 3
        msgRepns = MsgBox("ต้องการแก้ไขข้อมูลคลิก Yes หากต้องการยกเลิกคลิก NO", vbYesNo)
Else
        irow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
'Other code...

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Fri Jan 31, 2020 9:45 am
by nokkakolsong
ได้แล้วค่ะ ขอบคุณมากน่ะค่ะ

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Tue May 05, 2020 1:48 pm
by nongmay_za
รบกวนช่วยดูโค็ดให้หน่อยค่ะ
กดอัพเดทข้อมูลแต่ข้อมูลที่ได้ไปบันทึกแถวสุดท้ายค่ะ
อยู่ใน Useform : ค้นหาอัพเดท ค่ะ

ขอบคุณค่ะ

Code: Select all

Private Sub CommandButton2_Click()
Dim irow As Long
    Dim ws As Worksheet
    On Error Resume Next
    If Err.Number = 13 Then
MsgBox "ไม่พบข้อมูล"
GoTo iNet
    End If
    If TextBox63.Text = "" Then
MsgBox "กรุณากรอกข้อมูล"
GoTo iNet
 End If
            Set ws = Worksheets("บันทึกรายการซื้อขาย")
            Set sRange = ws.Range("A8:a" & ws.Range("a" & ws.Rows.Count).End(xlUp).Row)
             If Application.CountIf(sRange, TextBox63.Value) > 0 Then
        irow = Application.Match(CDbl(TextBox63.Value), sRange, 0) + 3
        msgRepns = MsgBox("ต้องการแก้ไขข้อมูลคลิก Yes หากต้องการยกเลิกคลิก NO", vbYesNo)
Else
        irow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If

        ws.Cells(irow, 3) = TextBox1.Value
ws.Cells(irow, 1).Value = Me.ComboBox21.Value
ws.Cells(irow, 2).Value = Me.ComboBox20.Value
ws.Cells(irow, 3).Value = Me.TextBox1.Value
ws.Cells(irow, 4).Value = Me.ComboBox2.Value
ws.Cells(irow, 5).Value = Me.TextBox60.Value
ws.Cells(irow, 6).Value = Me.TextBox61.Value
ws.Cells(irow, 7).Value = Me.ComboBox3.Value
ws.Cells(irow, 8).Value = Me.TextBox2.Value
ws.Cells(irow, 9).Value = Me.TextBox3.Value
ws.Cells(irow, 10).Value = Me.TextBox4.Value
ws.Cells(irow, 11).Value = Me.ComboBox4.Value
ws.Cells(irow, 12).Value = Me.TextBox5.Value
ws.Cells(irow, 13).Value = Me.TextBox6.Value
ws.Cells(irow, 14).Value = Me.TextBox7.Value
ws.Cells(irow, 15).Value = Me.ComboBox5.Value
ws.Cells(irow, 16).Value = Me.TextBox8.Value
ws.Cells(irow, 17).Value = Me.TextBox9.Value
ws.Cells(irow, 18).Value = Me.TextBox10.Value
ws.Cells(irow, 19).Value = Me.ComboBox6.Value
ws.Cells(irow, 20).Value = Me.TextBox11.Value
ws.Cells(irow, 21).Value = Me.TextBox12.Value
ws.Cells(irow, 22).Value = Me.TextBox13.Value
ws.Cells(irow, 23).Value = Me.ComboBox7.Value
ws.Cells(irow, 24).Value = Me.TextBox14.Value
ws.Cells(irow, 25).Value = Me.TextBox15.Value
ws.Cells(irow, 26).Value = Me.TextBox16.Value
ws.Cells(irow, 27).Value = Me.ComboBox8.Value
ws.Cells(irow, 28).Value = Me.TextBox17.Value
ws.Cells(irow, 29).Value = Me.TextBox18.Value
ws.Cells(irow, 30).Value = Me.TextBox19.Value
ws.Cells(irow, 31).Value = Me.ComboBox9.Value
ws.Cells(irow, 32).Value = Me.TextBox20.Value
ws.Cells(irow, 33).Value = Me.TextBox21.Value
ws.Cells(irow, 34).Value = Me.TextBox22.Value
ws.Cells(irow, 35).Value = Me.ComboBox10.Value
ws.Cells(irow, 36).Value = Me.TextBox23.Value
ws.Cells(irow, 37).Value = Me.TextBox24.Value
ws.Cells(irow, 38).Value = Me.TextBox25.Value
ws.Cells(irow, 39).Value = Me.ComboBox11.Value
ws.Cells(irow, 40).Value = Me.TextBox26.Value
ws.Cells(irow, 41).Value = Me.TextBox27.Value
ws.Cells(irow, 42).Value = Me.TextBox28.Value
ws.Cells(irow, 43).Value = Me.ComboBox12.Value
ws.Cells(irow, 44).Value = Me.TextBox29.Value
ws.Cells(irow, 45).Value = Me.TextBox30.Value
ws.Cells(irow, 46).Value = Me.TextBox31.Value
ws.Cells(irow, 47).Value = Me.ComboBox13.Value
ws.Cells(irow, 48).Value = Me.TextBox32.Value
ws.Cells(irow, 49).Value = Me.TextBox33.Value
ws.Cells(irow, 50).Value = Me.TextBox34.Value
ws.Cells(irow, 51).Value = Me.ComboBox14.Value
ws.Cells(irow, 52).Value = Me.TextBox35.Value
ws.Cells(irow, 53).Value = Me.TextBox36.Value
ws.Cells(irow, 54).Value = Me.TextBox37.Value
ws.Cells(irow, 55).Value = Me.ComboBox15.Value
ws.Cells(irow, 56).Value = Me.TextBox38.Value
ws.Cells(irow, 57).Value = Me.TextBox39.Value
ws.Cells(irow, 58).Value = Me.TextBox40.Value
ws.Cells(irow, 59).Value = Me.ComboBox16.Value
ws.Cells(irow, 60).Value = Me.TextBox41.Value
ws.Cells(irow, 61).Value = Me.TextBox42.Value
ws.Cells(irow, 62).Value = Me.TextBox43.Value
ws.Cells(irow, 75).Value = Me.TextBox62.Value
ws.Cells(irow, 76).Value = Me.TextBox78.Value
ws.Cells(irow, 77).Value = Me.TextBox64.Value
ws.Cells(irow, 78).Value = Me.TextBox74.Value
ws.Cells(irow, 79).Value = Me.TextBox75.Value
ws.Cells(irow, 80).Value = Me.TextBox65.Value
ws.Cells(irow, 81).Value = Me.TextBox66.Value


ComboBox20.Text = " "
TextBox1.Text = " "
ComboBox2.Text = " "
TextBox60.Text = " "
TextBox61.Text = " "
TextBox58.Text = " "
ComboBox3.Text = " "
TextBox2.Text = " "
TextBox3.Text = " "
TextBox4.Text = " "
ComboBox4.Text = " "
TextBox5.Text = " "
TextBox6.Text = " "
TextBox7.Text = " "
ComboBox5.Text = " "
TextBox8.Text = " "
TextBox9.Text = " "
TextBox10.Text = " "
ComboBox6.Text = " "
TextBox11.Text = " "
TextBox12.Text = " "
TextBox13.Text = " "
ComboBox7.Text = " "
TextBox14.Text = " "
TextBox15.Text = " "
TextBox16.Text = " "
ComboBox8.Text = " "
TextBox17.Text = " "
TextBox18.Text = " "
TextBox19.Text = " "
ComboBox9.Text = " "
TextBox20.Text = " "
TextBox21.Text = " "
TextBox22.Text = " "
ComboBox10.Text = " "
TextBox23.Text = " "
TextBox24.Text = " "
TextBox25.Text = " "
ComboBox11.Text = " "
TextBox26.Text = " "
TextBox27.Text = " "
TextBox28.Text = " "
ComboBox12.Text = " "
TextBox29.Text = " "
TextBox30.Text = " "
TextBox31.Text = " "
ComboBox13.Text = " "
TextBox32.Text = " "
TextBox33.Text = " "
TextBox34.Text = " "
ComboBox14.Text = " "
TextBox35.Text = " "
TextBox36.Text = " "
TextBox37.Text = " "
ComboBox15.Text = " "
TextBox38.Text = " "
TextBox39.Text = " "
TextBox40.Text = " "
ComboBox16.Text = " "
TextBox41.Text = " "
TextBox42.Text = " "
TextBox43.Text = " "
ComboBox17.Text = " "
TextBox44.Text = " "
TextBox46.Text = " "
TextBox47.Text = " "
ComboBox18.Text = " "
TextBox48.Text = " "
TextBox50.Text = " "
TextBox51.Text = " "
ComboBox19.Text = " "
TextBox52.Text = " "
TextBox54.Text = " "
TextBox55.Text = " "
TextBox62.Text = " "
TextBox64.Text = " "
TextBox65.Text = " "
TextBox66.Text = " "
TextBox67.Text = " "
TextBox68.Text = " "
TextBox69.Text = " "
TextBox74.Text = " "
TextBox75.Text = " "
TextBox76.Text = " "
TextBox77.Text = " "

        MsgBox "ข้อมูลอัพเดทแล้ว"
        ActiveWorkbook.Save
iNet:
End Sub

Re: Useform แก้ไขแล้วไม่บันทึกลงแถวเดิม

Posted: Tue May 05, 2020 4:09 pm
by nongmay_za
อัพเดทค่ะ ตอนนี้ทำได้แล้วค่ะ
ขอบคุณค่ะ