ปัญหา Lock Cells ไม่เป็นไปตามเงื่อนไข

[modtanoi]

วิธีการที่ลองเดาๆ ดูมาหลายวันและยังทำไม่ได้ ปัญหาน่าจะมาจาก .Locked ไม่เข้าใจเงื่อนไข ดังนี้
1) ต้องการให้เซลล์ด้านล่างล็อคเมื่อเมื่อเซลล์เป้าหมายไม่ใช่ค่าที่ต้องการหรือค่าที่ต้องการให้ล็อค(กำหนดเงื่อนไขถูกต้อง) --> ผลที่ได้ .Validation ทำได้ตามเงื่อนไข(เป็นจริง)แต่ .Locked เซลล์ด้านล่างไม่สามารถทำได้ตามเงื่อนไข เพราะเซลล์ด้านล่างล็อคหมดเลย
2) ลองปรับแก้เงื่อนไขสุดท้ายให้ใช้เครื่องหมายเท่ากับ(=) 1 เงื่อนไข --> ผลที่ได้เซลล์ด้านล่างล็อค/ไม่ล็อคทำได้ตามเงื่อนไข แต่ถ้าทำให้เซลล์เป้าหมายว่าง หรือค่าอื่นที่ไม่ได้กำหนดเงื่อนไข เซลล์ด้านล่างจะเป็นค่าเดิม(ล็อค/ไม่ล็อค) ก่อนเปลี่ยนแปลง
3) ลองปรับแก้เงื่อนไขสุดท้ายให้ใช้เครื่องหมายเท่ากับ(=) หลายเงื่อนไข(เชื่อมด้วย or) --> ผลที่ได้เหมือนข้อ 1)
4) ลองปรับแก้เงื่อนไขสุดท้ายให้ใช้เครื่องหมายเท่ากับ(=) หลายเงื่อนไข(เชื่อมด้วย and) --> ผลที่ได้ผิดพลาดทั้ง .Validation และ .Locked
5) ลองปรับแก้เงื่อนไข เหมือนข้อ 1) แต่เอาเครื่องหมายคำพูดออกไป --> ผลที่ได้เหมือนข้อ 2)
6) สร้างเงื่อนไข เหมือนข้อ 1) (ทุกอย่าง) และเพิ่ม Sub เข้ามาช่วย(ใช้เงื่อนไขเหมือนข้อ 3)) --> ผลที่ได้เหมือนข้อ 2)
7) สร้างเงื่อนไข เหมือนข้อ 1) แต่เงื่อนไขสุดท้าย ประการเงื่อนไขเพิ่ม
(If Cells(3, Cell1.Column).MergeArea.Locked = False Then Cells(3, Cell1.Column).MergeArea.Locked = True) --> ผลที่ได้เหมือนข้อ 1)
8) ตัดเงื่อนไขสุดท้ายออก กำหนดให้เซลล์ด้านล็อคหมด --> ผลที่ได้เซลล์ด้านล่างเป็นไปตามเงื่อนไข
ดังนั้นปัญหาน่าจะมาจาก .Locked ไม่เข้าใจเงื่อนไข
รบกวนอาจารย์ช่วยแนะนำวิธีแก้ไขให้ด้วยนะครับ
ขอบคุณครับ

หมายเหตุ: ไฟล์ที่แนบเป็นข้อ 5 ครับ

[snasui]

ให้อธิบายมาว่าต้องการจะทำอะไร โดยช่วยขยายความข้อ 1 ให้ชัดเจน

เซลลเป้าหมายไม่ใช่ค่าที่ต้องการคือค่าอะไร ค่าที่ต้องการไม่ให้ล็อคคืออะไร

ค่าทดสอบคืออะไร ถ้าถูกต้องจะต้องแสดงผลอย่างไร

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

[modtanoi]

"เซลล์เป้าหมาย" คือเซล์แถวที่ 2
"เซลล์ด้านล้าง" คือเซลล์ที่ต้องการให้เปลี่ยนแปลงและล็อค/ไม่ล็อค
ค่าที่ต้องการไม่ให้เซลล์ด้านล่างล็อค คือ "เน่า" --> สามารถเลือกผลที่เน่าได้ว่ามีกี่ผล
จากคำสั่งที่เขียนต้องการให้ค่าวน loop ตามเงื่อนไขไปเลื่อยๆ
- กำหนดให้เป้าหมายเป็นแถวที่ 2 ในช่วง Range("C2:N2")
- เงื่อนไขแรก ถ้าเลือกรายการ "เน่า" เซลล์ด้านล่างจะต้องไม่ล็อค(สามารถเลือกผลที่เน่าเสียได้)
- เงื่อนไขที่สอง ถ้าเลือกรายการ "50%" คือ กึ่งเน่ากึ่งเสีย เซลล์ด้านล่างจะต้องถูกล็อคและแสดงสัญลักษณ์ขีด(-)
- เงื่อนไขสุดท้าย ถ้าไม่ใช่ทั้งเงื่อนไขแรกและเงื่อนไขที่สอง เซลล์ด้านล่างจะต้องถูกล็อคและไม่ไม่มีค่าใดๆ เลย เช่น เลือกรายการ ขีด(-) หรือไม่เน่า หรือลบให้เป็นช่องว่าง เซลล์ด้านล่างจะต้องถูกล็อคและว่างป่าว(เลือกรายการไม่ได้และไม่มีสัญลักษณ์ขีด(-)) ครับ

อธิบายข้อที่
1. เขียนเงื่อนไขดังข้างบน พอรันแล้วเซลล์ด้านล่างล็อคหมดเลย
2. ลองเปลี่ยนเงื่อนไขสุดท้ายให้เท่ากับเงื่อนไขเดียว(จากเดิมใช้เป็นไม่เท่ากับ) เช่น เงื่อนไขสุดท้าย ถ้าไม่เน่า เซลล์ด้านล่างจะต้องล็อค พอรันแล้วทุกค่าที่กำหนดแสดงออกมาถูกต้องตามเงื่อนไข ยกเว้นค่าที่ไม่ได้กำหนด เช่น ขีด(-) และช่องว่าง เซลล์ด้านล่างตรงนั้นจะไม่เปลี่ยนแปลงไปจากเดิม
3. ลองเปลี่ยนเงื่อนไขสุดท้ายเป็น ถ้าไม่เน่า หรือ ขีด หรือ ช่องว่าง ค่าใดค่าหนึ่งเป็นจริง เซลล์ด้านล่างจะต้องล็อค พอรันแล้วทุกค่าล็อคหมดเลย
4. ลองเปลี่ยนเงื่อนไขสุดท้ายเป็น ถ้าไม่เน่า และ ขีด และ ช่องว่าง ทุกค่าเป็นจริง เซลล์ด้านล่างจะต้องล็อค พอรันแล้วผิดพลาดทั้ง .Validation และ .Locked (ข้อนี้รู้ว่าประกาศเงื่อนไขไม่ถูกต้องแต่ก็อยากรู้เลยลองทำดู)
5. เขียนเงื่อนไขดังข้างบน ลองตัดเครื่องหมายคำพูดจาก ElseIf Cell1 <> "เน่า" And Cell1 <> 0.5 Then เป็น ElseIf Cell1 <> เน่า And Cell1 <> 0.5 Then พอรันแล้วทุกค่าที่กำหนดแสดงออกมาถูกต้องตามเงื่อนไข แต่ถ้าลบเซลล์แถวที่สองให้เป็นช่องว่าง เซลล์ด้านล่างจะไม่เปลี่ยนแปลง(จะยังคงค่าเซลล์เดิม เช่น ก่อนหน้าเป็น "เน่า" พอลบให้เป็นช่องว่างแล้ว เซลล์ด้านล่ายยังสามารถเลือกรายการได้อยู่และเซลล์ไม่ถูกล็อค ที่ถูกต้องคือเซลล์ด้านล่างจะต้องไม่มีค่าอะไรเลยและเซลล์จะต้องถูกล็อค)
6. เขียนเงื่อนไขดังข้างบน เพิ่ม Sub LockDataList() ลงใน Module เงื่อนไขสุดท้ายไม่ใส่เคื่องหมายคำพูด(ElseIf Cell1 <> เน่า And Cell1 <> 0.5 Then) แล้วเอามารันใน Private Sub Worksheet_Changee (เงื่อนไขสุดท้ายไม่ใส่เคื่องหมายคำพูด(ElseIf Cell1 <> "เน่า" And Cell1 <> 0.5 Then) ต่อจากการแสดงค่า List ตามเงื่อนไข พอรันแล้วทุกค่าที่กำหนดแสดงออกมาถูกต้องตามเงื่อนไข แต่ถ้าลบเซลล์แถวที่สองให้เป็นช่องว่าง เซลล์ด้านล่างจะไม่เปลี่ยนแปลง เหมือนดังข้อ 5)
7. ประกาศเงื่อนไขเพิ่มเติม
เงื่อนไขแรก: เขียนเงื่อนไขดังข้างบน
เงื่อนไขที่สอง: ถ้าเซลล์ด้านล่างถูกไม่ล็อคจะต้องถูกล็อค
พอรันแล้วเซลล์ด้านล่างล็อคหมดเลย
8. ตัดเงื่อนไขสุดท้ายออกพื่อทดสอบว่าเรากำหนดเงื่อนไขและให้แสดงผลถูกรึเปล่า เหลือแค่
- เงื่อนไขแรก ถ้าเลือกรายการ "เน่า" เซลล์ด้านล่างจะต้องไม่ล็อค(สามารถเลือกผลที่เน่าเสียได้)
- เงื่อนไขที่สอง ถ้าเลือกรายการ "50%" คือ กึ่งเน่ากึ่งเสีย เซลล์ด้านล่างจะต้องถูกล็อคและแสดงสัญลักษณ์ขีด(-)
พอรันแล้วค่า .Validation และ .Locked แสดงออกมาถูกต้องตามที่กำหนดดังเงื่อนไข

[snasui]

- เงื่อนไขแรก ถ้าเลือกรายการ "เน่า" เซลล์ด้านล่างจะต้องไม่ล็อค(สามารถเลือกผลที่เน่าเสียได้)
- เงื่อนไขที่สอง ถ้าเลือกรายการ "50%" คือ กึ่งเน่ากึ่งเสีย เซลล์ด้านล่างจะต้องถูกล็อคและแสดงสัญลักษณ์ขีด(-)
- เงื่อนไขสุดท้าย ถ้าไม่ใช่ทั้งเงื่อนไขแรกและเงื่อนไขที่สอง เซลล์ด้านล่างจะต้องถูกล็อคและไม่ไม่มีค่าใดๆ เลย เช่น เลือกรายการ ขีด(-) หรือไม่เน่า หรือลบให้เป็นช่องว่าง เซลล์ด้านล่างจะต้องถูกล็อคและว่างป่าว(เลือกรายการไม่ได้และไม่มีสัญลักษณ์ขีด(-)) ครับ

ผมทดสอบคลิกเลือกค่าในเซลล์ C2 แล้วพบว่าค่าใน C3 เป็นไปตามเงื่อนไขด้านบนแล้วครับ

ลองอธิบายมาว่าที่ทดสอบมาเองนั้นหากเลือกค่าต่าง ๆ ใน C2 แล้วเซลล์ C3 แสดงผลไม่เป็นไปตามเงื่อนไขด้านบนอย่างไรครับ

[modtanoi]

เซลล์ K2 รายการคือ "เน่า" เซลล์ด้านล่างจะต้องแสดง Validation และไม่ล็อค --> ถูกต้อง
แต่ถ้าลบเซลล์ K2 เป็นช่องว่าง เซลล์ด้านล่างจะไม่เปลี่ยนแปลง(จะยังมีรายการให้เลือกและเซลล์ไม่ล็อค)
ที่ถูกต้อง เซลล์ด้านล่างจะต้องไม่มีค่าใดๆ เลย และเซลล์จะต้องถูกล็อคครับ

[snasui]

ลองปรับ Code เป็นตามด้านล่างครับ

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target.Range("a1"), Range("C2:N2")) Is Nothing Then
        If Target.Range("a1") = "เน่า" Then
            With Target.Offset(1, 0).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="-,1 ผล,2 ผล,3 ผล,4 ผล,5 ผล"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Target.Offset(1, 0).MergeArea.Locked = False
        ElseIf Target.Range("a1") = 0.5 Then
            Target.Offset(1, 0).Validation.Delete
            If Target.Offset(1, 0) <> "-" Then
                Target.Offset(1, 0) = "-"
            End If
            Target.Offset(1, 0).MergeArea.Locked = True
        ElseIf Target.Range("a1") <> "เน่า" And Target.Range("a1") <> 0.5 Then
            Target.Offset(1, 0).Validation.Delete
            If Target.Offset(1, 0) <> "" Then
                Target.Offset(1, 0) = ""
            End If
            Target.Offset(1, 0).MergeArea.Locked = True
        End If
    End If
    Application.EnableEvents = True
End Sub

[modtanoi]

ทำได้เฉพาะเซลล์ที่เลือกครับ อยากให้รันวนรอบได้หมดทุกตัว
แต่ก่อนเคยกำหนดให้เงื่อนไขเซลล์ที่เลือกเป็น Target เหมือนกันครับ
พอทำแลวเซลล์ด้านล่างจะเปลี่ยนได้เฉพาะเซลล์ที่เราเลือกเหมือนที่อาจารย์แนะนำ
ก็เลยเปลี่ยนมาใช้ค่า Cell1 แทน และกำหนดคำสั่ง For Each Cell1 In Range("C2:N2").Cells เข้าไป
เพื่อที่จะให้มีการรันวนรอบทุกครั้งที่มีการเปลี่ยนแปลรายการไปเรื่อยๆ ครับ

[snasui]

จะวนรอบหรือไม่วนรอบไม่ใช่ปัญหาครับ หากต้องการวนรอบก็ปรับใช้กับ For each...Next เช่นเดิม

Code ทีใช้กับ Event เช่นที่ยกตัวอย่างมา ผมไม่เห็นความจำเป็นที่ต้องวนรอบ จึงปรับ Code มาใหม่เพื่อให้เกิดความกระชับ เนื่องจากหลักการคือเกิดการเปลี่ยนแปลงในเซลล์ด้านบนก่อนแล้วค่อยพิจารณาเซลล์ด้านล่าง หากเซลล์ด้านบนไม่เกิดการเปลี่ยนแปลงก็ไม่จำเป็นที่จะต้องไปวนรอบเพื่อตรวจสอบแต่อย่างใด

[modtanoi]

แต่ก่อนใช้ For each...Next
ตัวแปรที่กำหนดให้ล็อคเซลล์ใช้ Target ก็ใช้ได้ครับ
Range(Cells(Target.Row + 1, Target.Column), Cells(Target.Row + 1, Target.Column + 1)).Locked = True
ลืมไปว่าไม่ต้องใช้ For each...Next ก็ได้(มั่วอยู่หลายวัน)
ตอนนี้สงสัยเรื่องการประกาศช่วงให้แต่ละเซลล์
ทำไมต้องเป็น Target.Range("a1") เมื่อเซลล์เป้าหมายคือ C2
ก่อนหน้านี้เคยทำเป็นแบบเซลล์เดียวทำได้ง่ายดี
พอมาเซลล์ประสานก็เลยงง สงสัยประกาศตัวแปรผิดก็เลยทำไม่ได้
ก็เลยมาใช้ For each...Next ประกาศช่วงแล้วเราเข้าใจ
ดังนั้นก็เลยสังสัยว่า Range("a1") คืออะไรครับ
ลองเลื่อนเซลล์ไปก็ยังใช้ได้ แต่ถ้าเปลี่ยนเป็น Range("c1") กับใช้ไม่ได้
ขอบคุณครับ

[snasui]

กรณีเป็น Merge cell เช่น C1:D1 และเราต้องการทำงานกับ C1 (เซลล์แรกของเซลล์ที่ Merge กัน) เทคนิคของการเข้าถึงเซลล์แรกของเซลล์ที่ Merge กันสามารถใช้ .Range("a1") เข้ามาช่วย เพื่อลดการเกิด Error ครับ

[modtanoi]

ขอบคุณมากครับ