userform show edit and delete

[Totem]

เรียนอาจารย์และเพื่อนสมาชิก

ติดปัญหาการสร้าง userform ที่ แสดงรายการข้อมูล การแก้ไขและลบรายการ การบันทึกรายการ ใน userform1 และ userform2 จึ่งขออธิบายดังนี้

1.ใน Useform1

1.1 ต้องการกำหนดให้(ที่ระบายสีไว้)
การทำงานคือแสดงรายการ column G , H , I ได้แล้ว แต่ต้องการกำหนดช่วงข้อมูล
ในลักษณะ

Code: Select all

 LastRow = Sheets("edit and delete").Range("G" & Rows.Count).End(xlUp).Row

ครับ ช่วยปรับเป็นตัวอย่าง

Code: Select all

ListBox1.RowSource = "G2:I2000"

ด้วยครับ

Code: Select all

Private Sub CommandButton1_Click() 'Show Data

    ListBox1.ColumnCount = 3
    ListBox1.RowSource = "G2:I2000"

End Sub

หากแก้ไขในข้อ 1.1 ได้แล้ว ต้องการ ListBox1.Clear CommandButton5_Click()
จะปรับ ListBox1.RowSource = "" อย่างไรครับ

1.2 ComboBox1.List "ไม่มี"

Code: Select all

Private Sub ComboBox1_Change() 

    For i = 2 To Application.WorksheetFunction.CountA(Sheets("edit and delete").Range("G:G"))
        If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then
        Me.ListBox1.AddItem Cells(i, 1).Value
        Me.ListBox1.AddItem Cells(i, 2).Value
        Me.ListBox1.AddItem Cells(i, 3).Value
    End If
Next i

End Sub

เป็น ComboBox1.List เลือกข้อความว่า "ไม่มี"ได้แล้ว แสดงรายการใน Listbox
แต่พอ Loop แล้ว กลับไม่แสดงรายการที่เลือกข้อความว่า "ไม่มี"
เช่น
ชื่อ แผนก สถานะ
BBB2 bbb2 ไม่มี

หาไม่เจอสถานะว่า ไม่มี ครับ

Code: Select all

If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then

ต้องการให้แสดงรายการ ชื่อ แผนก สถานะ ที่มีสถานะว่า "ไม่มี" ครับ


2.ใน Useform2
เมื่อทำการกดปุ่ม edit selected แล้ว จะไปเปิด Useform2 เพื่อทำการ ดังนี้
2.1 save ต้องการให้ทุกครั้งที่ save ให้ข้อมูลต่อลงมาที่บรรทัดสุดท้าย
แต่ code กลับ ไป edit selected ที่ row ที่ 3 จาก column G , H ไป column B , C

Code: Select all

            Cells(i, 2) = TextBox1.Text
            Cells(i, 3) = TextBox2.Text

ช่วยปรับเป็นตัวอย่างด้วยครับ

Code: Select all

Private Sub CommandButton1_Click()  'save
    Dim i As Integer

    For i = 2 To Sheets("edit and delete").Range("B" & Rows.Count).End(xlUp).Row
        If Cells(i, 7) = Label4.Caption Then
            Cells(i, 2) = TextBox1.Text
            Cells(i, 3) = TextBox2.Text
        End If
    Next i

    MsgBox "Saved!", vbInformation
    'Unload Me
End Sub

2.2 DeleteSelected ทุกครั้งที่เลือก Delete ข้อมูลใน Listbox
ลบได้ตามต้องการแต่กลับไปกระทบกับ Cell อื่น
ไม่ไปกระทบกับช่อง Cell อื่น
เนื่องจากใน cloumn B และ C มีการอ้างอิงจาก cell อื่น (ข้อมูลที่ใช้จริงมีสูตรอยู่) เมื่อลบไปแล้วทำให้
สูตรที่อ้างอิงไว้ขึ้น #Ref
ที่ต้องการคือ ลบรายการนั้นแล้วไม่ทำให้สูตรอ้างอิงหายไปครับ ช่วยปรับเป็นตัวอย่างให้ด้วยครับ

Code: Select all

                Cells(i, 2).Value = ""
                Selection.delete
                Cells(i, 3).Value = ""
                Selection.delete

Code: Select all

Private Sub DeleteSelected_Click() 

    Dim i As Integer
    
    If MsgBox("Are you sure you want to delete the row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
        For i = 1 To Sheets("edit and delete").Range("B" & Rows.Count).End(xlUp).Row
            If Cells(i, 2) = ListBox1.List(ListBox1.ListIndex) Then
                Cells(i, 2).Select
                Cells(i, 2).Value = ""
                Cells(i, 2).Select
                Cells(i, 2).Value = ""
                Selection.delete
                Cells(i, 3).Value = ""
                Selection.delete
            End If
        Next i
    End If
End Sub

ขอบคุณครับ

edit and delete.xlsm

[snasui]

ถามตอบให้ผ่านไปทีละข้อครับ

ข้อแรก ไม่พบว่ามีการเขียน LastRow = Sheets("edit and delete").Range("G" & Rows.Count).End(xlUp).Row

ในคำสั่ง CommandButton1_Click() ให้เขียนขึ้นมาก่อนแล้วลองปรับกับ ListBox1.RowSource = "G2:I2000" มาให้ดูว่ามีแนวคิดที่จะ LastRow มาใช้กับ Statement อย่างไรครับ

[Totem]

ถามตอบให้ผ่านไปทีละข้อครับ

ข้อแรก ไม่พบว่ามีการเขียน LastRow = Sheets("edit and delete").Range("G" & Rows.Count).End(xlUp).Row

ในคำสั่ง CommandButton1_Click() ให้เขียนขึ้นมาก่อนแล้วลองปรับกับ ListBox1.RowSource = "G2:I2000" มาให้ดูว่ามีแนวคิดที่จะ LastRow มาใช้กับ Statement อย่างไรครับ

เรียนอาจารย์ครับ
ปัญหาที่เขียนแล้ว ทำได้แต่กำหนดให้แสดงตำแหน่งแถวสุดท้ายได้อย่างเดียว ไม่ได้เริ่มจาก G2:I2 ไปจนสุดแถวที่ 10 หรือแถวใดๆถึงแถวสุดท้ายครับ

ตรงนี้ครับ

Code: Select all

ListBox1.RowSource = Sheets("edit and delete").Range(Cells(lastlow, 7), Cells(lastlow, 9)).Address

Code: Select all

Private Sub CommandButton1_Click()
Dim lastlow As Long

        lastlow = Sheets("edit and delete").Range("G" & Rows.Count).End(xlUp).Row

        ListBox1.ColumnCount = 3
        ListBox1.RowSource = Sheets("edit and delete").Range(Cells(lastlow, 7), Cells(lastlow, 9)).Address

End Sub 

ขอบคุณครับ

[snasui]

ควรแนบไฟล์ล่าสุดมาด้วยจะได้ดูต่อไปจากนั้นและควรเป็นเช่นนี้เสมอ ไม่เช่นนั้นผมต้องมาขอให้แนบไฟล์ล่าสุดอยู่ทุกครั้งแทนที่จะได้ตอบทันทีครับ

จากตัวอย่างที่โพสต์มา ปรับเป็นตามนี้ครับ ListBox1.RowSource = Sheets("edit and delete").Range(Cells(2, 7), Cells(lastlow, 9)).Address

[Totem]

ควรแนบไฟล์ล่าสุดมาด้วยจะได้ดูต่อไปจากนั้นและควรเป็นเช่นนี้เสมอ ไม่เช่นนั้นผมต้องมาขอให้แนบไฟล์ล่าสุดอยู่ทุกครั้งแทนที่จะได้ตอบทันทีครับ

จากตัวอย่างที่โพสต์มา ปรับเป็นตามนี้ครับ ListBox1.RowSource = Sheets("edit and delete").Range(Cells(2, 7), Cells(lastlow, 9)).Address

ครับ ข้อ 1 Useform1 1.1 ได้ตามต้องการครับ และ ListBox1.Clear CommandButton5_Click() ไม่ปัญหาใช้งานได้ครับ
ขอบคุณครบ

ขอไปต่อครับใน
1.2 ComboBox1.List "ไม่มี"
เมื่อเลือก คำว่า "ไม่มี" ใน Listbox1 จะต้องแสดงรายการเฉพาะแถวที่มี สถานะ ไม่มี เท่านั้น แต่จาก

Code: Select all

Private Sub ComboBox1_Change() 

    For i = 2 To Application.WorksheetFunction.CountA(Sheets("edit and delete").Range("G:G"))
        If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then
        Me.ListBox1.AddItem Cells(i, 1).Value
        Me.ListBox1.AddItem Cells(i, 2).Value
        Me.ListBox1.AddItem Cells(i, 3).Value
    End If
Next i

End Sub

ในบรรทัดที่

Code: Select all

If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then

กลับไม่แสดงรายการออกมาครับ ช่วยพิจารณาปรับให้ด้วยครับ
ขอบคุณครับ

edit and delete.xlsm

[snasui]

ตัวอย่าง Code ครับ

Code: Select all

Dim arr() As Variant, j As Integer, k As Integer
Me.ListBox1.RowSource = ""
Me.ListBox1.ColumnCount = 3
k = Application.CountIf(Sheets("edit and delete").Range("G:G"), Me.ComboBox1.Text)
ReDim arr(0 To k, 2)
For i = 2 To Application.WorksheetFunction.CountA(Sheets("edit and delete").Range("G:G"))
    If Sheets("edit and delete").Cells(i, "I").Value = Me.ComboBox1.Value Then
        arr(j, 0) = Sheets("edit and delete").Cells(i, "G").Value
        arr(j, 1) = Sheets("edit and delete").Cells(i, "H").Value
        arr(j, 2) = Sheets("edit and delete").Cells(i, "I").Value
        j = j + 1
    End If
Next i
Me.ListBox1.List() = arr

[Totem]

ตัวอย่าง Code ครับ

Code: Select all

Dim arr() As Variant, j As Integer, k As Integer
Me.ListBox1.RowSource = ""
Me.ListBox1.ColumnCount = 3
k = Application.CountIf(Sheets("edit and delete").Range("G:G"), Me.ComboBox1.Text)
ReDim arr(0 To k, 2)
For i = 2 To Application.WorksheetFunction.CountA(Sheets("edit and delete").Range("G:G"))
    If Sheets("edit and delete").Cells(i, "I").Value = Me.ComboBox1.Value Then
        arr(j, 0) = Sheets("edit and delete").Cells(i, "G").Value
        arr(j, 1) = Sheets("edit and delete").Cells(i, "H").Value
        arr(j, 2) = Sheets("edit and delete").Cells(i, "I").Value
        j = j + 1
    End If
Next i
Me.ListBox1.List() = arr

ได้ตามต้องการ ในส่วนของการแสดงข้อมูล ติดอยู่ต้องที่ เมื่อเลือกรายการว่า "ไม่มี" แล้ว แสดงรายการข้อมูลได้ แต่ ColumnHeads ไม่ขึ้น ชื่อ แผนก สถานะ ครับ
ผมลองปรับใน view object ไปกำหนด ColumnHeads เป็น True แล้วก็ไม่ขึ้น ColumnHeads
ปกติน่าจะขึ้นครับ กลับไม่ขึ้นเฉยเลยครับ
ขอบคุณครับ

edit and delete.xlsm

[snasui]

ถ้าด้วย Code ที่ผมเขียนให้นี้ไม่ขึ้น Header อยู่แล้วครับ

ทั้งนี้เพราะ Rowsource เอาค่ามาจาก Range ใน Worksheet ซึ่งถ้าใน Range นั้นมีการ Scope ถึงหัวคอลัมน์ด้วยก็จะถูกนำมาแสดง แต่ค่าตามวิธีนี้เป็นการเก็บรายการเข้า Array แล้วค่อยนำมาใช้ ใน Array กับใน Worksheet ไม่ได้เทียบเท่ากัน

การจะแสดงผลลัพธ์แบบมีหัวคอลัมน์ด้วยจะต้องเก็บค่าไปพักไว้อีกพื้นที่ของ Worksheet แล้วกำหนดตำแหน่งนั้นให้เป็น RowSource ของ ListBox ใหม่จึงจะแสดงหัวคอลัมน์ได้ครับ

[Totem]

อาจารย์มีตัวอย่างให้ดูไหมครับ จะได้ปรับใช้ครับ

[snasui]

ตัวอย่างในการนำ Array ไปกำหนดค่าในกับ Range คิดว่าพบได้จำนวนมากครับ

อย่างเช่นตัวอย่างนี้ wordpress/search-multiple-sheets/

ตัวอย่างอื่น ๆ ลองดูที่นี่ครับ Assign array to ranges

[Totem]

ข้อ2.Useform2
2.1 ต้องการให้ทุกครั้งที่ save ให้ข้อมูลต่อลงมาที่บรรทัดสุดท้าย
ทำได้แล้วครับ
แต่ ติดตรงที่เวลาสั่งปิด Useform2

Code: Select all

Private Sub CommandButton2_Click()
    UserForm2.Hide
    Unload UserForm2
End Sub

แล้วกลับมาที่ Useform1 จะขึ้น MsgBox "No Data or Click the info" ด้วย ซึ่งไม่ต้องการให้ขึ้นเฉพาะตอนปิด Useform2 ครับ
แต่ขึ้นแจ้งตอนแรกเมื่อเริ่มทำงานใน Useform1 ไม่เป็นไรครับเพราะตัองการแจ้งข้อความ

Code: Select all

Private Sub CommandButton4_Click() 'edit selected

On Error GoTo ErrorHandler:

    UserForm2.Label4 = ListBox1.List(ListBox1.ListIndex)
    UserForm2.TextBox1.Text = ListBox1.Column(0, ListBox1.ListIndex)
    UserForm2.TextBox2.Text = ListBox1.Column(1, ListBox1.ListIndex)
    UserForm2.Show

ErrorHandler:

    MsgBox "No Data or Click the info"
    
        Exit Sub
        
   Resume Next

End Sub

ขอบคุณครับ

[snasui]

ตราบใดที่ยังใช้ข้อมูลจาก UserForm2 อยู่จะต้องไม่ Unload ครับ

ให้ Mark Statement Unload ทิ้งไป

เมื่อใดที่เลิกใช้หรือจบงาน ค่อยไป Unload ครับ

[Totem]

ตัวอย่างในการนำ Array ไปกำหนดค่าในกับ Range คิดว่าพบได้จำนวนมากครับ

อย่างเช่นตัวอย่างนี้ wordpress/search-multiple-sheets/

ตัวอย่างอื่น ๆ ลองดูที่นี่ครับ Assign array to ranges

จะลองไปศึกษาดูครับ ขอบคุณครับ

[Totem]

ตราบใดที่ยังใช้ข้อมูลจาก UserForm2 อยู่จะต้องไม่ Unload ครับ

ให้ Mark Statement Unload ทิ้งไป

เมื่อใดที่เลิกใช้หรือจบงาน ค่อยไป Unload ครับ

ผมลอง

Code: Select all

 'Unload UserForm2 

Code: Select all

Private Sub CommandButton2_Click()
    UserForm2.Hide
    'Unload UserForm2
End Sub

ก็ยังขึ้นเหมือนเดิม ไม่ว่าทำถูกต้องหรือเปล่าครับ ยังไม่ได้ครับ

ขอบคุณครับ

[snasui]

ไฟล์ที่แนบมาล่าสุดผมต้องทดสอบอย่างไรใพ้พบปัญหานั้น ลองลำดับการทำงานเป็นขั้นเป็นตอนมาด้วยครับ

[Totem]

ลำดับขั้นตอนเป็นดังนี้ครับ
1.เมื่อเปิด userform1 ขึ้นมาแล้ว หากไม่เลือก data ใน listbox จะขึ้น MsgBox "No Data or Click the info" ให้ทราบเพื่อให้เลือกรายการ
2.หากทำการเลือกรายการ data ใน listbox แล้ว ทำการกดปุ่ม edit selected หน้าต่าง userform2 ก็จะเปิดขึ้นมาเพื่อที่จะแก้ไข บันทึก หรือลบ
3.จากนั้นเมื่อได้ทำการแก้ไข บันทึก หรือลบ อย่างใดอย่างหนึ่งเรียบร้อยแล้ว ก็จะทำการปิดหน้าต่าง userform2
4.โดยกดปุ่ม back to userform1

Code: Select all

UserForm2.Hide
    Unload UserForm2

ปัญหาอยู่ตรงนี้ครับ เมือทำการปิดหรือซ่อน UserForm2 ไม่ต้องการให้ MsgBox "No Data or Click the info" ขึ้นมาให้ทราบอีกครับ

[snasui]

กรณีการดัก Error หากไม่พบว่ามี Error ก็ให้ออกจาก Procedure แต่หาก Error ค่อยกระโดดไปทำงานใน Code ที่ใช้ดักจับ Error ต่อครับ

ตัวอย่างการปรับ Code

Code: Select all

Private Sub CommandButton4_Click() 'edit selected

    On Error GoTo ErrorHandler:

    UserForm2.Label4 = ListBox1.List(ListBox1.ListIndex)
    UserForm2.TextBox1.Text = ListBox1.Column(0, ListBox1.ListIndex)
    UserForm2.TextBox2.Text = ListBox1.Column(1, ListBox1.ListIndex)
    UserForm2.Show
    Exit Sub
ErrorHandler:

    MsgBox "No Data or Click the info"
    Resume Next

End Sub

[Totem]

กรณีการดัก Error หากไม่พบว่ามี Error ก็ให้ออกจาก Procedure แต่หาก Error ค่อยกระโดดไปทำงานใน Code ที่ใช้ดักจับ Error ต่อครับ

ตัวอย่างการปรับ Code

Code: Select all

Private Sub CommandButton4_Click() 'edit selected

    On Error GoTo ErrorHandler:

    UserForm2.Label4 = ListBox1.List(ListBox1.ListIndex)
    UserForm2.TextBox1.Text = ListBox1.Column(0, ListBox1.ListIndex)
    UserForm2.TextBox2.Text = ListBox1.Column(1, ListBox1.ListIndex)
    UserForm2.Show
    Exit Sub
ErrorHandler:

    MsgBox "No Data or Click the info"
    Resume Next

End Sub

ขอบคุณครับ ได้ตามต้องการครับ

[Totem]

ติดปัญหา ข้อ2.ใน Useform2
เมื่อทำการกดปุ่ม edit selected แล้ว จะไปเปิด Useform2 เพื่อทำการ ดังนี้
2.2 DeleteSelected ทุกครั้งที่เลือก Delete ข้อมูลใน Listbox
ลบได้ตามต้องการแต่กลับไปกระทบกับ Cell อื่น เพราะเป็นการเลื่อน cell ขึ้นไป แล้วไปกระทบกับช่อง Cell อื่น
เนื่องจากใน cloumn B และ C มีการอ้างอิงจาก cell อื่นที่มีสูตร (ข้อมูลที่ใช้จริงมีสูตรอยู่) เมื่อลบไปแล้วทำให้
สูตรที่อ้างอิงไว้ขึ้น #Ref
สิ่งที่ต้องการคือ ลบรายการใดๆที่เลือกแล้ว เมื่อลบรายการนั้นๆ ไม่ทำให้สูตรอ้างอิงขึ้น #Ref ครับ

Code: Select all

Private Sub DeleteSelected_Click() 

    Dim i As Integer
    
    If MsgBox("Are you sure you want to delete the row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
        For i = 1 To Sheets("edit and delete").Range("B" & Rows.Count).End(xlUp).Row
            If Cells(i, 2) = ListBox1.List(ListBox1.ListIndex) Then
                Cells(i, 2).Select
                Cells(i, 2).Value = ""
                Cells(i, 3).Select
                Cells(i, 3).Value = ""
                Cells(i, 2).Value = ""
                Selection.delete
                Cells(i, 3).Value = ""
                Selection.delete
            End If
        Next i
    End If
End Sub

ปรับแล้วยังไม่ได้ครับ

Code: Select all

          Cells(i, 2).Value = ""
          Selection.delete
          Cells(i, 3).Value = ""
          Selection.delete

ติดตรงนี้ครับ
ช่วยปรับเป็นตัวอย่างให้ด้วยครับ

ขอบคุณครับ

edit and delete.xlsm

[snasui]

เปลี่ยนจาก Selection.delete เป็น Selection.clearcontents จะได้ไม่ทำให้เซลล์ที่เกี่ยวข้องติด #REF ครับ