내일도 화이팅

입출고 Undo, Redo 구현 - 코드제공, 파일제공(복붙용) 본문

엑셀/엑셀 매크로 사무자동화 코드

입출고 Undo, Redo 구현 - 코드제공, 파일제공(복붙용)

내일도화이팅 2023. 7. 30. 01:12

※ 코드와 파일은 상업적 이용, 개인적 이용이 모두 가능합니다. 다만, 블로그나 게시판 업로드 목적이라면 출처를 꼭 남겨주세요.

 

재고관리프로그램-Ver.5.xlsm
0.13MB

Undo와 Redo 시연영상 1

 

Undo와 Redo 시연영상 2

안녕하세요. 오랜만입니다.
 
매크로 사용의 단점이 뒤로가기랑 앞으로 가기가 안된다는 점인데요.
 
그러면 데이터를 잘못입력했을 때 상당히 곤란하겠죠?
 
원본 데이터는 날라가고 재고는 바꼈고 매우 곤란해집니다.
 
그래서 Undo(뒤로가기)와 Redo(앞으로가기)를 구현했습니다.
 
코드가 조금 많기도하고 견적서 입력코드와 제거 코드도 약간의 변경이 있습니다.
1. 입고견적서입력 코드

Sub 입고견적서입력()
    Dim data As Integer
    Dim Transcation As Integer
    Dim overlap As Boolean
    
    overlap = 중복확인("입고")
    
    If (Not overlap) Then
        Call save_past("입고", "input", Worksheets("입고견적서").Cells(3, "G").Value)
        Worksheets("Redo 입고데이터").Range("A:Y").Delete xlToLeft
        
        '입고시트 몇번째 셀부터 데이터를 넣을 수 있는지 확인
        data = 3
        Do While (Not IsEmpty(Cells(data, "B").Value))
            data = data + 1
        Loop
        
        '입고견적서의 데이터가 몇개인지 확인
        Transcation = 3
        Do While (Not IsEmpty(Worksheets("입고견적서").Cells(Transcation, 2).Value))
            Transcation = Transcation + 1
        Loop
        Range("C" & Trim(Str(data) & ":F" & Trim(Str(data + Transcation - 4)))).Value = Worksheets("입고견적서").Range("B3:E" & Trim(Str(Transcation - 1))).Value
        Range("B" & Trim(Str(data) & ":B" & Trim(Str(data + Transcation - 4)))).Value = Worksheets("입고견적서").Cells(3, 7).Value
        
        
        ' 재고갱신
        Call 입고재고갱신
    End If
End Sub

2. 출고견적서입력 코드

Sub 출고견적서입력()
    Dim data As Integer
    Dim Transcation As Integer
    Dim overlap As Boolean
    
    overlap = 중복확인("출고")
    
    If (Not overlap) Then
        Call save_past("출고", "input", Worksheets("출고견적서").Cells(3, "G").Value)
        Worksheets("Redo 출고데이터").Range("A:Y").Delete xlToft
        
        '출고시트 몇번째 셀부터 데이터를 넣을 수 있는지 확인
        data = 3
        Do While (Not IsEmpty(Cells(data, "B").Value))
            data = data + 1
        Loop
        
        '출고견적서의 데이터가 몇개인지 확인
        Transcation = 3
        Do While (Not IsEmpty(Worksheets("출고견적서").Cells(Transcation, 2).Value))
            Transcation = Transcation + 1
        Loop
        Range("C" & Trim(Str(data) & ":F" & Trim(Str(data + Transcation - 4)))).Value = Worksheets("출고견적서").Range("B3:E" & Trim(Str(Transcation - 1))).Value
        Range("B" & Trim(Str(data) & ":B" & Trim(Str(data + Transcation - 4)))).Value = Worksheets("출고견적서").Cells(3, 7).Value
        
        
        ' 재고갱신
        Call 출고재고갱신
    
    End If
End Sub

3. 입고데이터제거

Sub 입고제거()
    Dim i As Integer
    Dim j As Integer

    Dim delete_number As String
    Dim success_delete As Boolean
    
    success_delete = False
    delete_number = Application.InputBox("삭제하실 거래번호를 입력하세요", "거래삭제", , , , , , 2)
    
    i = 3
    Do While (Not IsEmpty(Cells(i, 2).Value))
        If (Cells(i, 2).Value Like delete_number) Then
            If (Not success_delete) Then
                Call save_past("입고", "delete", delete_number)
                Worksheets("Redo 입고데이터").Range("A:Y").Delete xlToLeft
            End If
            success_delete = True
            j = 3
            Do While (Not IsEmpty(Worksheets("재고관리").Cells(j, 2)))
                If (Cells(i, 4).Value Like Worksheets("재고관리").Cells(j, 2).Value) Then
                    Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value - Worksheets("입고").Cells(i, 6).Value
                    Exit Do
                End If
                j = j + 1
            Loop
            Range("B" & Trim(Str(i)) & ":F" & Trim(Str(i))).Select
            Selection.Delete Shift:=xlUp
            i = 3
        Else
            i = i + 1
        End If
    Loop
    If (Not success_delete) Then
        msg = MsgBox("이미 삭제되었거나 존재하지않는 번호입니다.", vbYesOnly, "삭제실패")
    Else
        Range("B3:F500").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
            With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("G2").Select
    End If
    
End Sub

4. 출고데이터제거

Sub 출고제거()
    Dim i As Integer
    Dim j As Integer

    Dim delete_number As String
    Dim success_delete As Boolean
    
    success_delete = False
    delete_number = Application.InputBox("삭제하실 거래번호를 입력하세요", "거래삭제", , , , , , 2)
    
    i = 3
    Do While (Not IsEmpty(Cells(i, 2).Value))
        If (Cells(i, 2).Value Like delete_number) Then
            If (Not success_delete) Then
                Call save_past("출고", "delete", delete_number)
                Worksheets("Redo 출고데이터").Range("A:Y").Delete xlToLeft
            End If
            success_delete = True
            j = 3
            Do While (Not IsEmpty(Worksheets("재고관리").Cells(j, 2)))
                If (Cells(i, 4).Value Like Worksheets("재고관리").Cells(j, 2).Value) Then
                    Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + Worksheets("출고").Cells(i, 6).Value
                    Exit Do
                End If
                j = j + 1
            Loop
            Range("B" & Trim(Str(i)) & ":F" & Trim(Str(i))).Select
            Selection.Delete Shift:=xlUp
            i = 3
        Else
            i = i + 1
        End If
    Loop
    If (Not success_delete) Then
        msg = MsgBox("이미 삭제되었거나 존재하지않는 번호입니다.", vbYesOnly, "삭제실패")
    Else
        Range("B3:F500").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
            With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("G2").Select
    End If
    
End Sub

5. 데이터 추가/삭제 시 기록 저장 코드(입출고 공통)

Sub save_past(kind As String, reverse_order As String, number As String)
    Worksheets("Undo " & kind & "데이터").Range("A:E").Delete xlToLeft
    Worksheets(kind).Range("B:F").Copy Worksheets("Undo " & kind & "데이터").Range("U:Y")
    Worksheets("Undo " & kind & "데이터").Cells(1, "U").Value = number
    Worksheets("Undo " & kind & "데이터").Cells(1, "V").Value = reverse_order
End Sub

6. Undo 입고

Sub undo_입고()
    If (IsEmpty(Worksheets("Undo 입고데이터").Cells(2, "U").Value)) Then
        msg = MsgBox("Undo하실 데이터가 없습니다.", vbYesOnly, "Undo 실패")
    Else
        Call save_future("입고")
        Call undo("입고")
    End If
End Sub

7. Undo 출고

Sub undo_출고()
    If (IsEmpty(Worksheets("Undo 출고데이터").Cells(2, "U").Value)) Then
        msg = MsgBox("Undo하실 데이터가 없습니다.", vbYesOnly, "Undo 실패")
    Else
        Call save_future("출고")
        Call undo("출고")
    End If
End Sub

8. Undo 실행(입출고 공통)

Sub undo(kind As String)
    Dim in_product As Boolean
    
    in_product = False
    If (kind Like "입고") Then
        in_product = True
    End If
    
    '재고 갱신
    If (Worksheets("Undo " & kind & "데이터").Cells(1, "V") Like "input") Then
        Dim i As Integer
        Dim j As Integer
        
        i = 3
        j = 3
        Do While (Not IsEmpty(Cells(i, 2).Value))
            If (Cells(i, 2).Value Like Worksheets("Undo " & kind & "데이터").Cells(1, "U").Value) Then
                j = 3
                Do While (Not IsEmpty(Worksheets("재고관리").Cells(j, 2)))
                    If (Cells(i, 4).Value Like Worksheets("재고관리").Cells(j, 2).Value) Then
                        If (in_product) Then
                            Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value - Worksheets("입고").Cells(i, 6).Value
                        Else
                            Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + Worksheets("출고").Cells(i, 6).Value
                        End If
                        Exit Do
                    End If
                    j = j + 1
                Loop
            End If
            i = i + 1
        Loop
    Else
        Dim count_inven As Integer
        count_inven = 3
            
        Do While (Not IsEmpty(Worksheets("재고관리").Cells(count_inven, 2).Value))
            count_inven = count_inven + 1
        Loop
            
        If (in_product) Then
            For i = 3 To 100
                For j = 3 To count_inven - 1
                    If (Worksheets("Undo 입고데이터").Cells(1, "U").Value Like Worksheets("Undo 입고데이터").Cells(i, "U").Value _
                    And Worksheets("재고관리").Cells(j, 2).Value Like Worksheets("Undo 입고데이터").Cells(i, "W").Value) Then
                        Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + Worksheets("Undo 입고데이터").Cells(i, "Y").Value
                        Exit For
                    End If
                Next j
            Next i
        Else
            For i = 3 To 100
                For j = 3 To count_inven - 1
                    If (Worksheets("Undo 출고데이터").Cells(1, "U").Value Like Worksheets("Undo 출고데이터").Cells(i, "U").Value _
                    And Worksheets("재고관리").Cells(j, 2).Value Like Worksheets("Undo 출고데이터").Cells(i, "W").Value) Then
                        Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value - Worksheets("Undo 출고데이터").Cells(i, "Y").Value
                        Exit For
                    End If
                Next j
            Next i
        End If
    End If
    '데이터 갱신
    Worksheets("Undo " & kind & "데이터").Range("U:Y").Cut Worksheets(kind).Range("B:F")
    Range("B1:F1").Value = ""
    Worksheets("Undo " & kind & "데이터").Range("A:E").Insert
End Sub

9. Undo 실행 시 기존 데이터 저장(입출고 공통)

Sub save_future(kind As String)
    Worksheets("Redo " & kind & "데이터").Range("A:E").Delete xlToLeft
    Worksheets(kind).Range("B:F").Copy Worksheets("Redo " & kind & "데이터").Range("U:Y")
    Worksheets("Redo " & kind & "데이터").Range("U1:V1").Value = Worksheets("Undo " & kind & "데이터").Range("U1:V1").Value
End Sub

10. Redo 입고

Sub redo_입고()
    If (IsEmpty(Worksheets("Redo 입고데이터").Cells(2, "U").Value)) Then
        msg = MsgBox("Redo하실 데이터가 없습니다.", vbYesOnly, "Redo 실패")
    Else
        Call save_past("입고", Worksheets("Redo 입고데이터").Cells(1, "V"), Worksheets("Redo 입고데이터").Cells(1, "U"))
        Call redo("입고")
    End If
End Sub

11. Redo 출고

Sub redo_출고()
    If (IsEmpty(Worksheets("Redo 출고데이터").Cells(2, "U").Value)) Then
        msg = MsgBox("Redo하실 데이터가 없습니다.", vbYesOnly, "Redo 실패")
    Else
        Call save_past("출고", Worksheets("Redo 출고데이터").Cells(1, "V"), Worksheets("Redo 출고데이터").Cells(1, "U"))
        Call redo("출고")
    End If
End Sub

12. Redo 실행(입출고 공통)

Sub redo(kind As String)
    Dim in_product As Boolean
    
    in_product = False
    If (kind Like "입고") Then
        in_product = True
    End If
    
    '재고 갱신
    If (Worksheets("Redo " & kind & "데이터").Cells(1, "V") Like "delete") Then
        Dim i As Integer
        Dim j As Integer
        
        i = 3
        Do While (Not IsEmpty(Cells(i, 2).Value))
            If (Cells(i, 2).Value Like Worksheets("Redo " & kind & "데이터").Cells(1, "U").Value) Then
                j = 3
                Do While (Not IsEmpty(Worksheets("재고관리").Cells(j, 2)))
                    If (Cells(i, 4).Value Like Worksheets("재고관리").Cells(j, 2).Value) Then
                        If (in_product) Then
                            Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value - Worksheets("입고").Cells(i, 6).Value
                        Else
                            Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + Worksheets("출고").Cells(i, 6).Value
                        End If
                        Exit Do
                    End If
                    j = j + 1
                Loop
            End If
            i = i + 1
        Loop
    Else
        Dim count_inven As Integer
        count_inven = 3
            
        Do While (Not IsEmpty(Worksheets("재고관리").Cells(count_inven, 2).Value))
            count_inven = count_inven + 1
        Loop
            
        If (in_product) Then
            For i = 3 To 100
                For j = 3 To count_inven - 1
                    If (Worksheets("Redo 입고데이터").Cells(1, "U").Value Like Worksheets("Redo 입고데이터").Cells(i, "U").Value _
                    And Worksheets("재고관리").Cells(j, 2).Value Like Worksheets("Redo 입고데이터").Cells(i, "W").Value) Then
                        Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + Worksheets("Redo 입고데이터").Cells(i, "Y").Value
                        Exit For
                    End If
                Next j
            Next i
        Else
            For i = 3 To 100
                For j = 3 To count_inven - 1
                    If (Worksheets("Redo 출고데이터").Cells(1, "U").Value Like Worksheets("Redo 출고데이터").Cells(i, "U").Value _
                    And Worksheets("재고관리").Cells(j, 2).Value Like Worksheets("Redo 출고데이터").Cells(i, "W").Value) Then
                        Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value - Worksheets("Redo 출고데이터").Cells(i, "Y").Value
                        Exit For
                    End If
                Next j
            Next i
        End If
    End If
    '데이터 갱신
    Worksheets("Redo " & kind & "데이터").Range("U:Y").Cut Worksheets(kind).Range("B:F")
    Range("B1:F1").Value = ""
    Worksheets("Redo " & kind & "데이터").Range("A:E").Insert
End Sub

많고 복잡하죠? 걱정하지마세요. 제가 아래의 링크에 위 코드들을 이해하기쉽게 하나하나 설명해드릴게요. ㅎㅎ
 
또한 Undo, Redo 코드설명 포스팅이 끝나면, 위의 복잡하고 읽기 어려운 코드들을 가독성 좋은 클린코드로 만드는 것도 보여드릴게요 ㅎㅎ
 
도움이 되셨다면 댓글과 좋아요 부탁드립니다.
 
피드백과 질문 모두 환영입니다.
 

입출고 Undo, Redo 구현 - 코드분석 링크

https://ksm30546.tistory.com/m/20

 

입출고 Undo, Redo 구현 - 코드분석

안녕하세요. 내일도 화이팅입니다. 어제 업로드한 Undo와 Redo 코드 글은 다들 보셨나요? 오늘은 그 코드들을 한줄한줄 분석해보려합니다. 총 8개의 모듈이 추가되었고, 4개의 모듈이 변경되었으며

ksm30546.tistory.com

 

'엑셀 > 엑셀 매크로 사무자동화 코드' 카테고리의 다른 글

입출고관리 데이터 코드 단순화 + 코드 캡슐화 - 코드제공, 파일제공  (0) 2023.08.04
입출고 삭제 - 코드제공, 파일제공  (0) 2023.08.01
입출고 시 중복 업로드 방지-코드제공, 파일제공(복붙용)  (0) 2023.07.22
입고, 출고 시 재고 자동 업데이트-코드제공,파일제공(복붙용)  (0) 2023.07.17
출고 데이터 추가하기-코드제공, 파일제공(복붙용)  (0) 2023.07.17
'엑셀/엑셀 매크로 사무자동화 코드' Related Articles more