엑셀 폼 조건부서식 유효성검사 이름정의

 

엑셀폼조건부서식유효성검사.xls

0.05MB

비밀번호 a

Function validateForm() As Boolean

validateForm = True

    

    If Range("F6") = "" Then

        MsgBox "item ID를 입력해주세요.", vbOKOnly + vbInformation, "Item ID"

        Range("F6").Interior.Color = vbRed

        Range("F6").Activate

        validateForm = False

        

        ElseIf Range("F8") = "" Then

        MsgBox "item name을 입력해주세요.", vbOKOnly + vbInformation, "Item Name"

        Range("F8").Interior.Color = vbRed

        Range("F8").Activate

        validateForm = False

        

    ElseIf Range("F10") = "" Then

        MsgBox "Price를 입력해주세요.", vbOKOnly + vbInformation, "Price"

        Range("F10").Interior.Color = vbRed

        Range("F10").Activate

        validateForm = False

        

    ElseIf Range("F12") = "" Then

        MsgBox "Quantity를 입력해주세요.", vbOKOnly + vbInformation, "Quantity"

        Range("F12").Interior.Color = vbRed

        Range("F12").Activate

        validateForm = False

        

    End If

End Function

 

Function reset()

Range("F6, F8, F10, F12").ClearContents

Range("F6, F8, F10, F12").Interior.ColorIndex = 15

End Function

 

Sub transferData()

 

ActiveSheet.Unprotect "a"

 

If validateForm = True Then

 

Application.ScreenUpdating = False

 

Dim nextblankRow As Long, lastrow As Long

 

lastrow = Sheets("완성").Range("A" & Rows.Count).End(xlUp).Row

 

nextblankRow = lastrow + 1

 

With ThisWorkbook.Sheets("완성")

    

        .Range("A" & nextblankRow).Value = nextblankRow - 1

        .Range("B" & nextblankRow).Value = Range("F6").Value

        .Range("C" & nextblankRow).Value = Range("F8").Value

        .Range("D" & nextblankRow).Value = Range("F10").Value

        .Range("E" & nextblankRow).Value = Range("F12").Value

        

    End With

    

    Sheets("완성").Columns("A:E").AutoFit

    

    Call reset

    Else

 

    Exit Sub

 

End If

 

ActiveSheet.Protect "a"

 

Application.ScreenUpdating = True

End Sub

 

Sub resetForm()

ActiveSheet.Unprotect "a"

Dim i As Integer

i = MsgBox("전체 폼 입력을 취소하시겠습니까?", vbQuestion + vbYesNo, "Reset Form")

    If i = vbYes Then

        Call reset

    End If

ActiveSheet.Protect "m22525830"

End Sub

==============================

Private Sub Workbook_Open()

Sheets("아이템").Visible = xlVeryHidden

End Sub

https://www.youtube.com/watch?v=VF4a9YDpOjU