Code VBA ที่กำหนดให้ผู้ใช้ใส่ข้อมูลบน Excel

ANUSARA · #1

ต้องการ Code VBA ที่กำหนดให้ผู้ใช้ใส่ข้อมูลบน Excel ที่ต้องการให้ผู้ใช้เลือกวิธีแบบที่ 1 แล้วจะใส่ค่าได้เฉพาะช่องของแบบที่ 1
และถ้าเลือกวิธีแบบที่ 2 จะใส่ค่าได้เฉพาะในช่องของแบบที่ 2 จะไปเลือกหรือใสข้อมูลในแบบที่ 1 ไม่ได้

แนบไฟล์ มาดูด้วยนะคะ
1. ต้องการให้ใส่ข้อมูลตามลำดับตั้งแต่ Name, Date
2. แล้วพอถึงข้อ 1 Method ให้เลือกว่าจะเอา Method 1 หรือ Method 2
3. ถ้าเลือก Method 1 จะสามารถคลิกเลือกเฉพาะที่ข้อ 1.1 เพื่อเลือกข้อมูลจาก method โดยคลิกที่ 1.2 Method 2 ไม่ได้ รวมถึง คลิกที่ 1.2.1 และ 1.2.2 ไม่ได้ด้วย หรือ ถ้าเลือก Method 2 จะคลิกที่ Method 1 ไม่ได้จะใส่ข้อมูลได้เฉพาะใน 1.2.1 , 1.2.2
4. ส่วนในข้อ 2-4 สามารถใส่ข้อมูลได้แม้จะเลือก Method 1 หรือ Method 2

ANUSARA · #2

ต้องการ Code VBA ที่กำหนดให้ผู้ใช้ใส่ข้อมูลบน Excel ที่ต้องการให้ผู้ใช้เลือกวิธีแบบที่ 1 แล้วจะใส่ค่าได้เฉพาะช่องของแบบที่ 1
และถ้าเลือกวิธีแบบที่ 2 จะใส่ค่าได้เฉพาะในช่องของแบบที่ 2 จะไปเลือกหรือใสข้อมูลในแบบที่ 1 ไม่ได้

แนบไฟล์ มาดูด้วยนะคะ
1. ต้องการให้ใส่ข้อมูลตามลำดับตั้งแต่ Name, Date
2. แล้วพอถึงข้อ 1 Method ให้เลือกว่าจะเอา Method 1 หรือ Method 2
3. ถ้าเลือก Method 1 จะสามารถคลิกเลือกเฉพาะที่ข้อ 1.1 เพื่อเลือกข้อมูลจาก method โดยคลิกที่ 1.2 Method 2 ไม่ได้ รวมถึง คลิกที่ 1.2.1 และ 1.2.2 ไม่ได้ด้วย หรือ ถ้าเลือก Method 2 จะคลิกที่ Method 1 ไม่ได้จะใส่ข้อมูลได้เฉพาะใน 1.2.1 , 1.2.2
4. ส่วนในข้อ 2-4 สามารถใส่ข้อมูลได้แม้จะเลือก Method 1 หรือ Method 2

snasui · #3

ลองดูการทำ Validation ด้วยการ Custom เพื่อใช้ Function ตรวจสอบเงื่อนไขตามไฟล์แนบครับ

ANUSARA · #4

ถ้าต้องการให้ช่องนี้มีรายการให้เลือกตามสีของช่อง C5 ค่ะ
และกำหนดเหมือนเดิมคือจะคลิกเลือกรายการได้ต้องเลือก Method 1 จะต้องเพิ่มสูตรอย่างไรคะ
=OFFSET($O$6,1,MATCH($C$5,$P$6:$R$6,0),5,1)
สูตรนี้ได้ลองทำเพื่อให้มันมีรายการเลือกเพื่อเชื่อมจากการเลือกสีด้านบน ค่ะ
ต้องรวมนี้กับสูตร =IF(E8="Method 1",$M$10:$M$15) อย่างไรคะ

snasui · #5

ANUSARA wrote:ถ้าต้องการให้ช่องนี้มีรายการให้เลือกตามสีของช่อง C5 ค่ะ
และกำหนดเหมือนเดิมคือจะคลิกเลือกรายการได้ต้องเลือก Method 1 จะต้องเพิ่มสูตรอย่างไรคะ
=OFFSET($O$6,1,MATCH($C$5,$P$6:$R$6,0),5,1)
สูตรนี้ได้ลองทำเพื่อให้มันมีรายการเลือกเพื่อเชื่อมจากการเลือกสีด้านบน ค่ะ
ต้องรวมนี้กับสูตร =IF(E8="Method 1",$M$10:$M$15) อย่างไรคะ

ที่ Data > Validation เปลี่ยนสูตรเป็นด้านล่างแทนสูตรเดิมครับ

=IF(E8="Method 1",OFFSET($O$6,0,MATCH($C$5,$P$5:$R$5,0),4))

ANUSARA · #6

ปัญหาคื่อ ถ้าเกิดผู้ใช้เลือก Method 1 แล้ว คลิกเลือกข้อมูลค้างไว้ แต่เปลี่ยนมาเลือก Method 2 ซึ่งเป็นแบบใส่ข้อมูล ดังนั้นข้อมูลในแบบ Method 1 ที่ถูกเลือกจากlist จะค้างอยู่
จึงอยากให้ถ้าเลือก Method 2 แล้วข้อมูลในMetod 1 จะแสดงตัวเลือกแรกคือ **Please select data**
และถ้าเลือก Method 1 ช่องที่ใส่ data ของ Method 2 อยากให้แสดงค่า 0
ต้องใส่สูตรเพิ่มอย่างไรคะ

รบกวนอีกครั้งค่ะ
ขอบคุณค่ะ

snasui · #7

ANUSARA wrote:ปัญหาคื่อ ถ้าเกิดผู้ใช้เลือก Method 1 แล้ว คลิกเลือกข้อมูลค้างไว้ แต่เปลี่ยนมาเลือก Method 2 ซึ่งเป็นแบบใส่ข้อมูล ดังนั้นข้อมูลในแบบ Method 1 ที่ถูกเลือกจากlist จะค้างอยู่
จึงอยากให้ถ้าเลือก Method 2 แล้วข้อมูลในMetod 1 จะแสดงตัวเลือกแรกคือ **Please select data**
และถ้าเลือก Method 1 ช่องที่ใส่ data ของ Method 2 อยากให้แสดงค่า 0
ต้องใส่สูตรเพิ่มอย่างไรคะ

รบกวนอีกครั้งค่ะ
ขอบคุณค่ะ

กรณีต้องการเช่นนี้ต้องใช้ VBA เข้ามาช่วยครับ เมื่อถามถึง VBA ผมถือว่าคุณ ANUSARA มีความคุ้นเคยกับ VBA อยู่แล้ว การวาง Code คงไม่มีปัญหา

ลองนำ Code ด้านล่างนี้ไปวางที่ Sheet1 ครับ

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$E$8" Then
        If Target = "Method 1" Then
            Range("C11") = "**Please select data**"
            Range("F15:H19") = 0
        Else
            Range("C11") = "**Please select data**"
            Range("F15:H19").ClearContents
        End If
    End If
    Application.EnableEvents = True
End Sub

จากนั้นลองเปลี่ยน E8 แล้วสังเกตดูผล

ANUSARA · #8

ขอบคุณมากค่ะ ลองเอา code ไปวางแล้ว สามารถทำได้แล้วค่ะ
ขอสอบถามเพิ่มเติมนะคะ
ช่อง cell F15:F19 เป็นช่องที่เราให้ผู้ใช้เติมค่าที่เป็นตัวเลข
แต่เราได้กำหนดไปแล้วว่า ให้ใส่ค่าได้เมื่อเลือก เลือก Method 2
โดยกำหนดไว้ที่ Data validation > Allow:Custom> Formula: $E$8 = "Method2"
แต่ถ้าเราอยากจะกำหนดให้ช่องที่ใส่ข้อมูลนี้รับแต่ค่าที่เป็นตัวเลขอย่างเดียว
จะใส่ตัวอักษรไม่ได้ เนื่องจากจะไม่สามารถคำนวณได้ค่ะ
เราต้องตั้งค่าเพิ่มอย่างไรบ้างคะ

ถ้าจะเลือก Data validation > Allow: whole number > Data: greater than > Minimun :0
เพื่อให้ใส่ค่าที่มากกว่า 0 ได้ไหมคะ และจะเอาไปเพิ่มกับคำสั่งเดิมอย่างไรคะ

แนบไฟล์ด้านล่างค่ะ

snasui · #9

ตัวอย่างการกำหนดให้กรอกได้เฉพาะตัวเลขและ E8 เป็น Method 2

ลองตามนี้ครับ

๑. คลุม F15:H19
๒. เข้าเมนู Data > Validation
๓. เปลี่ยนสูตรเดิมเป็นตามด้านล่าง

=AND($E$8="Method 2",ISNUMBER(F15))

ANUSARA · #10

ขอบคุณมากค่ะ

ANUSARA · #11

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$E$8" Then
If Target = "Method 1" Then
Range("C11") = "**Please select data**"
Range("F15:H19") = 0
Else
Range("C11") = "**Please select data**"
Range("F15:H19").ClearContents
End If
End If
Application.EnableEvents = True
End Sub

จาก ตรงบรรทัด Range("F15:H19") = 0 คือเซลล์มีหลายคอลัมน์ จะต้องไม่ merge cell ใช่ไหมคะ
แต่ถ้าในแต่ละแถวมี cell ที่มีหลาย column และถูก merge กันอยู่ จะต้องเขียน code อย่างไรคะ

ANUSARA · #12

ต้องการกำหนด ให้เลือก Combo Box2 ได้เมื่อเลือก Method 2 และถ้าเลือก Method อื่นแล้ว Combo Box นี้จะถูกเลือกไม่ได้
และแสดงตัวเลือกเป็น Please click & select data ค้างไว้ค่ะ

และอยากกำหนดให้ เวลาใส่ค่า Custom จะต้องใส่ได้แต่ค่าตัวเลข จะต้องแก้ไขตรงไหนบ้างคะ
ขอบคุณค่ะ

snasui · #13

ANUSARA wrote:1. แต่ถ้าในแต่ละแถวมี cell ที่มีหลาย column และถูก merge กันอยู่ จะต้องเขียน code อย่างไรคะ
2. ต้องการกำหนด ให้เลือก Combo Box2 ได้เมื่อเลือก Method 2 และถ้าเลือก Method อื่นแล้ว Combo Box นี้จะถูกเลือกไม่ได้
และแสดงตัวเลือกเป็น Please click & select data ค้างไว้ค่ะ

ลองดู Code ด้านล่างครับ

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Application.EnableEvents = False
    If Target.Address = "$E$8" Then
        If Target = "Method 1" Then
            Range("C11") = "**Please select data**"
            With ComboBox2
                .Text = "Please Click & Select Data"
                .ListFillRange = ""
                .ShowDropButtonWhen = fmShowDropButtonWhenNever
            End With
            For Each r In Range("F15:F19")
                If r.MergeCells Then
                    r = 0
                Else
                    r = 0
                End If
            Next r
        Else
            Range("C11") = "**Please select data**"
            With ComboBox2
                .ListFillRange = "M24:M28"
                .ShowDropButtonWhen = fmShowDropButtonWhenAlways
            End With
            For Each r In Range("F15:F19")
                If r.MergeCells Then
                    r = ""
                    Else
                    r.ClearContents
                End If
            Next r
        End If
    End If
  Application.EnableEvents = True
End Sub

และอยากกำหนดให้ เวลาใส่ค่า Custom จะต้องใส่ได้แต่ค่าตัวเลข จะต้องแก้ไขตรงไหนบ้างคะ
ขอบคุณค่ะ

ลองดูตัวอย่างการปรับ Code ด้านล่างครับ

Code: Select all

Private Sub ComboBox2_Change()
     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
End Sub

ANUSARA · #14

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Application.EnableEvents = False
If Target.Address = "$E$8" Then
If Target = "Method 1" Then
Range("C11") = "**Please select data**"
With ComboBox2
.Text = "Please Click & Select Data"
.ListFillRange = ""
.ShowDropButtonWhen = fmShowDropButtonWhenNever
End With
For Each r In Range("F15:F19")
If r.MergeCells Then
r = 0
Else
r = 0
End If
Next r
Else
Range("C11") = "**Please select data**"
With ComboBox2
.ListFillRange = "M24:M28"
.ShowDropButtonWhen = fmShowDropButtonWhenAlways
End With
For Each r In Range("F15:F19")
If r.MergeCells Then
r = ""
Else
r.ClearContents
End If
Next r
End If
End If
Application.EnableEvents = True
End Sub

code นี้ได้ตามที่ต้องการค่ะ
แต่พอกด ตัวเลือก Method 1แล้ว combo box จะหายไปค่ะ แต่มันยังพิมพ์อะไรต่างๆ หรือลบคำว่า Please click & select data ออกไปได้ค่ะ
จะมีคำสั่ง lock ไว้ไม่ให้แก้ไขอะไรเพิ่มเติมได้มีไหมคะ
ลองใช้ Data vlidation แล้วกำหนดให้เซลล์นั้นขึ้นกับ Method 2 เหมือนกัยที่ทำกับ Cell ปกติดูแต่ก็ยังไม่ได้ค่ะ

รบกวนอีกครั้งนะคะ
Code อื่นที่แนะนำมาใช้ได้ตามที่ต้องการค่ะ
ขอบคุณค่ะ

snasui · #15

การ Disable Combo box ลองดู Code ตามด้านล่างครับ

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Application.EnableEvents = False
    If Target.Address = "$E$8" Then
        If Target = "Method 1" Then
            Range("C11") = "**Please select data**"
            With ComboBox2
                .Text = "Please Click & Select Data"
                .Enabled = False
            End With
            For Each r In Range("F15:F19")
                If r.MergeCells Then
                    r = 0
                Else
                    r = 0
                End If
            Next r
        Else
            Range("C11") = "**Please select data**"
            With ComboBox2
                .Enabled = True
                .ListFillRange = "M24:M28"
            End With
            For Each r In Range("F15:F19")
                If r.MergeCells Then
                    r = ""
                    Else
                    r.ClearContents
                End If
            Next r
        End If
    End If
  Application.EnableEvents = True
End Sub

ANUSARA · #16

1 ลองเอา code มาวางแล้วค่ะ อย่างที่ต้องการคือเมื่อเลือก Method 1 แล้วcombobox 2 จะแก้ไขไม่ได้ค่ะ แต่เมื่อคลิกกลับมาเป็น Method 2 แล้ว combobox ไม่กลับมาให้เลือกรายการเหมือนเดิมได้ค่ะ

2 ถ้าเราให้ แสดงค่าจาก Combo box2 ที่ผู้ใช้เลือกไปแสดงผลใน Sheet2
แต่อยากให้แสดงค่าตามที่เลือกค่ะ เช่น 10, 20 , 50 หรืออื่นๆตามที่ผู้ใช้ใส่ค่ามา
แต่ถ้า Combo box นี้ไม่ถูกเลือก หรือถูกเลือกเป็น Please click & select data แล้ว
cell D5 ที่ Sheet2 ก็ให้แสดงค่าในช่องนั้นเป็นช่องว่างค่ะ

ขอบคุณค่ะ

snasui · #17

คุณ ANUSARA ลองปรับ Code เองมาให้ดูด้วยครับว่าได้พยายามปรับด้วยตัวเองแล้ว

ถ้าคุณเคยใช้ VBA ย่อมจะต้องปรับ Code มาได้บ้างครับ เพราะการเปลี่ยนแปลง Code ย่อมเกิดขึ้นได้ตลอดเวลาถ้าความต้องการเปลี่ยนแปลงไป

ANUSARA · #18

ลองแก้เป็นแบบนี้ สามารถแก้ไขได้แล้วค่ะ
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Application.EnableEvents = False
If Target.Address = "$E$8" Then
If Target = "Method 1" Then
Range("C11") = "**Please select data**"
Range("F15:F19") = 0
With ComboBox2
.Text = "Please Click & Select Data"
.ListFillRange = ""
.ShowDropButtonWhen = fmShowDropButtonWhenNever
.Enabled = False
End With

Else
Range("C11") = "**Please select data**"
Range("F15:F19").ClearContents
With ComboBox2
.Enabled = True
.ListFillRange = "M24:M28"
.ShowDropButtonWhen = fmShowDropButtonWhenAlways
End With

End If
End If
Application.EnableEvents = True
End Sub

แต่ปัญหาอันนี้ยังแก้ไม่ได้เลยค่ะ
# ถ้าเราให้ แสดงค่าจาก Combo box2 ที่ผู้ใช้เลือกไปแสดงผลใน Sheet2
แต่อยากให้แสดงค่าตามที่เลือกค่ะ เช่น 10, 20 , 50 หรืออื่นๆตามที่ผู้ใช้ใส่ค่ามา
แต่ถ้า Combo box นี้ไม่ถูกเลือก หรือถูกเลือกเป็น Please click & select data แล้ว
cell D5 ที่ Sheet2 ก็ให้แสดงค่าในช่องนั้นเป็นช่องว่างค่ะ

snasui · #19

จาก

ANUSARA wrote:1 ลองเอา code มาวางแล้วค่ะ อย่างที่ต้องการคือเมื่อเลือก Method 1 แล้วcombobox 2 จะแก้ไขไม่ได้ค่ะ แต่เมื่อคลิกกลับมาเป็น Method 2 แล้ว combobox ไม่กลับมาให้เลือกรายการเหมือนเดิมได้ค่ะ
...ขอบคุณค่ะ

ผมทดสอบแล้ว Code ที่ผมปรับไปให้ครั้งล่าสุดสามารถใช้ได้ไม่มีปัญหาครับ ทีุ่คุณปรับมาให้นั้นผมยังไม่ถือว่าปรับครับ อย่าเพิ่งเสียกำลังใจ

สำหรับ Code ที่ต้องการให้ปรับมาก็คือตรงนี้ครับ

ANUSARA wrote:....2 ถ้าเราให้ แสดงค่าจาก Combo box2 ที่ผู้ใช้เลือกไปแสดงผลใน Sheet2
แต่อยากให้แสดงค่าตามที่เลือกค่ะ เช่น 10, 20 , 50 หรืออื่นๆตามที่ผู้ใช้ใส่ค่ามา
แต่ถ้า Combo box นี้ไม่ถูกเลือก หรือถูกเลือกเป็น Please click & select data แล้ว
cell D5 ที่ Sheet2 ก็ให้แสดงค่าในช่องนั้นเป็นช่องว่างค่ะ

ตัวอย่าง Code เพื่อบันทึกค่าใน Combobox2 ไปยัง D5 ของ Sheet2 ตามด้านล่างครับ

Code: Select all

Private Sub ComboBox2_Change()
     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 IsNumeric(ComboBox2.Text) Then
        Worksheets("Sheet2").Range("D5") = ComboBox2.Text
    Else
        Worksheets("Sheet2").Range("D5") = ""
    End If
End Sub

ANUSARA · #20

1. ถ้าเปลี่ยนข้อมูลที่เลือกเป็นตัวหนังสือทั้งหมดไม่ใช่ตัวเลขแล้วต้องปรับ code อย่างไรคะ

เราให้ แสดงค่าจาก Combo box2 ที่ผู้ใช้เลือกไปแสดงผลใน Sheet2
แต่อยากให้แสดงค่าตามที่เลือกค่ะ เช่น closure,label, bottom หรืออื่นๆตามที่ผู้ใช้ใส่ค่ามา
แต่ถ้า Combo box นี้ไม่ถูกเลือก หรือถูกเลือกเป็น Please click & select data แล้ว
cell D5 ที่ Sheet2 ก็ให้แสดงค่าในช่องนั้นเป็นช่องว่างค่ะ

2. ตรง Msg ที่เคยขึ้นมาตอนพิมพ์ตัวอักษรแล้ว ขึ้นมาเตือนให้ใส่แต่ตัวเลข
แต่จะเปลี่ยนเป็นขึ้นมาเตือนให้ใส่เฉพาะตัวอักษร ไม่ให้ใส่ตัวเลข

ทั้ง 2 อย่างนี้ต้องปรับ code อย่างไรคะ
ลองเปลี่ยนจาก Numeric เป็น text ยังไม่ได้ค่ะ

ขอบคุณค่ะ

Snasui Excel Forum

File Anatomy Add-in: ตรวจสอบโครงสร้างไฟล์ Excel อย่างมืออาชีพ