protect sheet ที่บาง cell มี code VBA ด้วย

[ANUSARA]

ได้ทำ input sheet ให้ผู้ใช้ใส่ข้อมูล จึงต้องการ protect บาง cell ที่มีสูตรไว้ไม่ให้แก้ไขได้
และเหลือบาง cell สำหรับใส่ หรือ เลือกข้อมูล
การ protect sheet ที่มีบางส่วนเป็น Combo box (แบบ Active X) ที่มี vba
เลือกส่วนที่จะให้ใส่ data คือตั้งแต่ G5:G9 , G14:G16 และ G20
ช่องที่มี Active X และ VBA คือช่องที่ G7
ในเซลล์ G5ก็เป็นActive Xเหมือนกันแต่ไม่มีcode VBA พอ protect แล้วไม่มีปัญหาค่ะ
แต่สำหรับ Cell G7 พอ protect แล้วเลือกรายการใน list ได้ แต่พอเลือก Custom
แล้วไม่ขึ้น Msg Box มาให้ใส่ค่าค่ะ มันขึ้น Error ที่หน้า Code VBA ค่ะ (ตามรูป)

วิธี Protect ที่ทำคือ
เลือกส่วนพื้นที่ที่ต้องการให้ใส่ค่า >Format Cell > Protection > เอากากบาทช่อง Lock ออก
แล้วมา Protect Sheet >เลือกเครื่องหมายถูกที่ Select locked cells, select unlocked cells, Edited objects >ใส่ password ค่ะ

ผลออกมาคือ ขึ้น error ที่ code vba ค่ะ
ไฟล์ที่แนบยังไม่ได้ protect ค่ะ

[snasui]

ลองส่งไฟล์ตัวอย่างมาดูครับ

[ANUSARA]

ไฟล์แนบมาใต้รูปแล้วค่ะ
เดี๋ยวลองแนบไปใหม่นะคะ
ขอบคุณค่ะ

[snasui]

ไฟล์แนบมาใต้รูปแล้วค่ะ
เดี๋ยวลองแนบไปใหม่นะคะ
ขอบคุณค่ะ

ขออภัยด้วยครับ มัวแต่มองภาพไม่เห็นไฟล์

ก่อนที่จะจัด Format ให้ทำการปลด Lock เสียก่อนครับ หลังจาก จัด Format แล้วก็ให้ใส่ Password ไว้ตามเดิม

การดำเนินการใด ๆ ด้วยกล่องโต้ตอบที่เขียนด้วย VBA เพื่อให้ดำเนินการกับ Worksheet ให้เขียน Code เพื่อปลด Lock ก่อนเสมอ หลังจากทำเสร็จแล้วให้ Lock ไว้ตามเดิม

ถ้ายังมีการ Lock อยู่แล้วไปเขียน Code ให้มีการเปลี่ยนแปลงเซลล์ที่ทำการ Lock ก็จะทำให้เกิด Error ตามที่เห็น

ดู Code สำหรับการปลด Lock และ Lock ใหม่ได้ที่นี่ครับ http://topicstock-tech.pantip.com/tech/ ... 61910.html

ถ้ายังไม่ได้ช่วยแจ้ง Password เพื่อปลด Lock ด้วยครับ

[ANUSARA]

Dim Sht As Worksheet
Sub ProtectAllSheets()
For Each Sht In ThisWorkbook.Worksheets
Sht.Protect Password:="secret"
Next
End Sub
Sub UnProtectAllSheets()
For Each Sht In ThisWorkbook.Worksheets
Sht.Unprotect Password:="secret"
Next
End Sub

ลอง copy code นี้ไปวางบน Module แล้วค่ะ Cell G7 ที่เป็น Combo box มี VBA ยัง error อยู่คะ
Password คือ ANUSARA

ขอบคุณค่ะ

[snasui]

เอาไปวางทั้งหมดไม่ได้ครับ

ที่ส่งให้มันเป็นตัวอย่างเท่านั้น

ถ้า Password คือ Secret ก็ให้ปลดด้วย Password นี้ก่อน

ถ้า Password คือ ANUSARA ก็ให้ปลดด้วย Password นี้ก่อน

การปลด ก็เช่น

ActiveSheet.Unprotect Password:="ANUSARA"

ข้างบนนี้วางไว้ก่อน Code ที่ต้องการเปลี่ยนแปลงใน Sheet ที่กำลังทำงาน

การ Protect ใหม่ก็เช่น

ActiveSheet.Protect Password:="ANUSARA"

ข้างบนนี้วางไว้หลังจากมีการเปลียนแปลงเรียบร้อยแล้ว

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

Code: Select all

Private Sub ComboBox1_Change()
    ActiveSheet.Unprotect Password:="ANUSARA"
    If Range("xSizeEx") = "Custom" Then
        xSizeEx = InputBox(Prompt:="Please Enter Custom Size Please.", Title:="PLEASE ENTER CUSTOM SIZE", Default:="")
          Range("xSizeEx") = xSizeEx
            
         Range("xSizeEx").NumberFormat = "General "" (Custom)"""
    Else
     Range("xSizeEx") = Range("xSizeEx")
     Range("xSizeEx").NumberFormat = "General"
   End If
   ActiveSheet.Protect Password:="ANUSARA"
End Sub

[ANUSARA]

ได้แล้วค่ะ ขอบคุณนะคะ

ถ้ามี หลาย Combo box ต้องทำแบบนี้ทุกอันเลยหรือไม่

[snasui]

ได้แล้วค่ะ ขอบคุณนะคะ

ถ้ามี หลาย Combo box ต้องทำแบบนี้ทุกอันเลยหรือไม่

ก็คงต้องทำเช่นนั้นครับ ถ้าหากว่าเป็นการแก้ไขเซลล์ที่เราทำการ Protect เอาไว้ ถ้าแก้ไขเซลล์อื่นที่ไม่ได้ทำการ Protect ก็ไม่ต้องไปปลดการ Protect แล้วทำการ Protect ใหม่แต่อย่างใดครับ

[ANUSARA]

รบกวนถามค่ะ

ลอง protect sheet โดยใส่ code ตามด้านล่าง และที่ได้แนบไฟล์มา แต่พอ protect ไปแล้ว กล่อง Msg Box ให้ใส่ค่า Custom ไม่แสดงค่ะ และcode ที่เคยกำหนดให้แสดง
กล่อง Custom ก็ไม่แสดง แล้วที่กำหนดให้เมื่อเลือก Please click & Select data และ Not Calculate แล้วไม่ให้ไปแสดงค่าในหน้า Sheet2 ก็ไม่ได้ค่ะ
ต้องปรับ Code อย่างไรคะ

Private Sub ComboBox1_Change()

ActiveSheet.Unprotect Password:="ANUSARA"

If Range("xSsx1") = "Custom" Then
xSsx1 = InputBox(Prompt:="Enter Custom Value Please.", Title:="PLEASE ENTER CUSTOM VALUE", Default:="")
Range("xSsx1") = xSsx1

If Not IsNumeric(xSsx1) Then
MsgBox "Please enter numeric."
ComboBox1.Text = "<--Please click & Select data-->"
Exit Sub
End If

Range("xSsx1").NumberFormat = "General "" (Custom)"""
Else

Range("xSsx1").NumberFormat = "General"
End If

If ComboBox1.Text <> "<--Please click & Select data-->" Then
Worksheets("Sheet2").Range("xREx1") = ComboBox1.Text
Else
Worksheets("Sheet2").Range("xREx1") = ""
End If

ActiveSheet.Protect Password:="ANUSARA"

End Sub

[snasui]

เนื่องจากผูกเซลล์ B2 ไว้กับ ComboBox และเมื่อเลือก ComboBox แล้วค่าใน B2 จะเปลี่ยนตาม นั่นหมายถึง B2 ต้องอนุญาตให้แก้ไขได้

ให้เปลี่ยนการกำหนดค่าเซลล์ B2 ใน Sheet1 ใหม่ตามด้านล่าง

1. เลือกเซลล์ B2
2. เข้าเมนู Home > Format > Format Cells
3. ที่แถบ Protection ปลดเครื่องหมายตรง Locked

จากนั้นลอง Run Code ดูใหม่ครับ

[ANUSARA]

ขอบคุณค่ะสำหรับคำตอบที่แนะนำ

ปัญหาที่ :
1. หากเป็นการ protect sheet แบบไฟล์ที่แนบด้านล้่าง จะต้องแก้ไขอย่างไรคะ

cell F15:F19 , C25, C27 เป็น cell ที่ให้เติมข้อมูล จึงไม่ได้ทำการ lock cell
ส่วน C11, E8 เป็นแบบ data validation ก็ไม่ได้ lock
ส่วน E21, C29 เป็น Combo Box ที่มี custom ให้ใส่ค่าด้วย จึงไม่ได้ lock cell ตามที่เคยแนะนำ

แส้วจึง ใส่ คำสั่ง protect sheet

แต่ผลคือเกิด error ที่
If Not IsText(xsourceex) Then
MsgBox "Please enter text."
ComboBox2.Text = ""
Exit Sub
End If
ของ combo box 2 ค่ะ

และ custom ของ combo box 2,1 ก็ไม่ขึ้นค่ะ

ต้องปรับ code อย่างไรคะ

2. หาก cell ที่มีเฉพาะ combo box ที่ให้เลือกข้อมูลเพียงอย่างเดียงไม่มี custom ต้องปลด lock cell ด้วยใช่ไหมคะ
3. เมื่อ protect sheet แล้ว cell F15: F19 จากที่เคยกำหนดว่าให้เปลี่ยนค่าเป็น 0 เมื่อกลับไปเลือก E8 เป็น Method 1
แต่ค่ายังคงเดิมค่ะ

รบกวนด้วยนะคะ
ขอบคุณค่ะ

[snasui]

ตอนทำการ Protect เพิ่มการอนุญาตให้ทำการ Format Cell ได้ด้วย โดยเพิ่ม Code เป็นตามด้านล่างครับ

Code: Select all

ActiveSheet.Protect Password:="ANUSARA", AllowFormattingCells:=True

นอกจากนี้ผมเปลี่ยน Code ส่วนของ ComboBox2_Change เป็นตามด้านล่างคิดว่าน่าจะตรงกับกล่องรับข้อมูลมากกว่าที่เีขียนมา

Code: Select all

Private Sub ComboBox2_Change()

    ActiveSheet.Unprotect Password:="ANUSARA"
    
     If Range("xSourceEx") = "Custom" Then
            xsourceex = InputBox(Prompt:="Enter Custom Quality Please.", Title:="PLEASE ENTER CUSTOM QUALITY", Default:="")
            Range("xSourceEx") = xsourceex
            
            If Not IsNumeric(xsourceex) Then
                MsgBox "Please enter numeric"
                ComboBox2.Text = ""
                Exit Sub
            End If
            Range("xSourceEx").NumberFormat = "General "" (Custom)"""
    Else
    
'        Range("xSourceEx") = Range("xSourceEx")
        Range("xSourceEx").NumberFormat = "General"
    End If
    
    If Not IsNumeric(ComboBox2.Text) Then
        Worksheets("Sheet2").Range("D5") = ComboBox2.Text
    Else
        Worksheets("Sheet2").Range("D5") = ""
    End If
    
    ActiveSheet.Protect Password:="ANUSARA", AllowFormattingCells:=True
    
End Sub

[ANUSARA]

ลอง copy code ไปวางcombobox2 แล้วค่ะ

เมื่อ protrct sheet แล้ว พอกดเลือก custom ที่ combobox 2 แล้วมันขึ้นerror ที่หน้าVBA
Run Time error '1004':
Application-defined or object-defined error

แล้วพอกด OK ก็ขึ้นว่า
Run Time error '1004':
Unable to set the Number Format property of the Range Class
กด Debug แล้วขึ้นแถบเหลืองที่บรรทัด
Range("xSourceEx").NumberFormat = "General""(Custom)"""
พอกกด run ไป มันก็กลับมาขึ้นกล่องให้ใ่ส่ค่า custom ได้ค่ะ แต่พอใส่ค่า กด OK แล้วมันก้กลับมา Error เหมือนเดิมค่ะ

ต้องแก้อย่างไรดีคะ
ขอบคุณค่ะ

[snasui]

ที่ Private Sub Worksheet_Change(ByVal Target As Range) ก็ต้องเพิ่มการอนุญาตให้กำหนด Format เมื่อทำการ Protect ด้วยเช่นกัน ไฟล์ที่ส่งมายังไม่ได้เพิ่มเป็นตามด้านล่าง ผมทดลองเพิ่มและลอง Run ดูไม่พบว่าเกิด Error ครับ

Code: Select all

ActiveSheet.Protect Password:="ANUSARA", AllowFormattingCells:=True

[ANUSARA]

ขอบคุณมากค่ะ สามารถ protectได้ค่ะ

แต่ combobox ที่มี code แบบที่กำหนดให้แสดงค่า และไม่แสดงค่า ดังเช่น code ด้านล่าง

If ComboBox1.Text <> "<--Please click & Select data-->" _
And ComboBox1.Text <> "Not Calculate" Then

Worksheets("Sheet2").Range("xResult1") = ComboBox1.Text

Else
Worksheets("Sheet2").Range("xResult1") = ""
End If

เมื่อ protect แล้ว จะขึ้น debug แสดงแถบเหลืองที่
Worksheets("Sheet2").Range("xResult1") = ""

หรือบางทีเลือก data แล้วไม่ไปแสดงที่หน้า Sheet2 ด้วยค่ะ ต้องคลิกเลือกไปมาหลายรอบกว่าจะแสดงค่า
ต้องแก้ไขตรงจุดนี้อย่างไรคะ

เมื่อเราเขียน code vba protect ไปแล้ว พอมา protect sheet ที่ tab sheet หน้า excel แล้วต้อง click เครื่องหมายถูก ที่เครื่องมือใดบ้าง
ที่เลือกไว้คือ Select locked cell , Select unlocked cell, Edit objects ค่ะ ต้องเลือกอันไหนอีกไหมคะ

ขอบคุณค่ะ

[snasui]

สาเหตุเดิม ๆ ครับ

Sheet2 เซลล์ B2 เมื่อต้องการให้เปลี่ยนแปลงได้ก็ต้องปลด Lock เซลล์ด้วยครับ หากไม่ได้ปลด Lock เมื่อทำการ Protect แล้วก็ไม่สามารถเปลี่ยนค่าในเซลล์นั้น ๆ ได้ โปรแกรมก็จะฟ้อง Error

ส่วนในตอนทำการ Protect จะอนุญาตให้ทำอะไรได้บ้างก็แล้วแต่ความจำเป็นของงาน เช่นแทรกบรรทัด แทรกแถว จัด Format เซลล์ ฯลฯ