제조 필수 도구! 엑셀로 간편하게 만드는 BOM 소요량 계산기-업무 효율 상승

2025.01.19 - [엑셀] - 제조에서 꼭 필요한 엑셀 소요량 계산기 만들기

제조에서 꼭 필요한 엑셀 소요량 계산기 만들기

 

위 글은 단순 엑셀로 제조회사에서 필요한 bom, 소요량 계산기를 만들었다. 

오늘은 bom 소요량을 계산한 데이터를 저장 하고, 리스트를 선택 할때 버튼 외에 더블클릭으로 제품을 선택 할 수 있는 기능을 추가 하였다. 

상업적인 사용은 불가이다. 신고 할 수 있음.

 

 

 

아래는 추가한 기능들에 대한 내용을 작성 하고자 한다. 

 

엑셀 VBA: ListBox 더블 클릭 시 텍스트박스에 값 입력하기

---

기능 개요

ListBox에 표시된 항목을 더블 클릭하면 해당 항목이 자동으로 TextBox(txtProductName)에 입력되는 기능을 구현합니다. 이 기능을 통해 버튼 클릭 없이도 빠르게 값을 입력할 수 있습니다.

---

구현 순서

1. ListBox를 디자인에 추가

1. 엑셀의 VBA 편집기(Alt + F11)를 열고 UserForm을 편집합니다.
2. UserForm에 ListBox와 TextBox를 추가합니다.
3. ListBox 이름: lstProducts (기본값을 사용하거나 원하는 이름으로 설정)
4. TextBox 이름: txtProductName (기본값을 사용하거나 원하는 이름으로 설정)

2. ListBox에 항목 추가하기

ListBox에 데이터를 불러오기 위해 버튼 클릭 이벤트(btnLoadProducts_Click)를 구현합니다.

 

설명:

• Sheet "BOM"에 입력된 데이터를 lstProducts에 추가합니다.
• 항목을 더블 클릭하면 TextBox에 입력됩니다.

Private Sub btnLoadProducts_Click()
    Dim wsBOM As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' BOM 시트 설정
    Set wsBOM = ThisWorkbook.Sheets("BOM")
   
    ' ListBox 초기화
    Me.lstProducts.Clear

    ' BOM 데이터의 마지막 행 찾기
    lastRow = wsBOM.Cells(wsBOM.Rows.Count, 1).End(xlUp).Row

    ' 제품 목록 추가
    For i = 2 To lastRow
        If Not IsEmpty(wsBOM.Cells(i, 1).Value) Then
            Me.lstProducts.AddItem wsBOM.Cells(i, 1).Value
        End If
    Next i
End Sub

---

3. ListBox 더블 클릭 이벤트 구현

ListBox에서 항목을 더블 클릭하면 해당 값이 txtProductName에 입력되도록 DblClick 이벤트를 작성합니다.

Private Sub lstProducts_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' ListBox 항목 더블 클릭 시 txtProductName에 입력
    If Me.lstProducts.ListIndex = -1 Then
        MsgBox "제품을 선택하세요.", vbExclamation
        Exit Sub
    End If

    ' 선택된 항목을 txtProductName에 입력
    Me.txtProductName.Value = Me.lstProducts.Value
End Sub

 

설명:

• 사용자가 ListBox 항목을 더블 클릭하면 선택된 항목이 txtProductName에 입력됩니다.
• 아무것도 선택하지 않고 더블 클릭하면 경고 메시지가 표시됩니다.

 

전체 동작 프로세스

1. 데이터 로드:
   • btnLoadProducts_Click 버튼을 클릭하여 ListBox에 데이터를 불러옵니다.
2. ListBox 항목 더블 클릭:
   • ListBox 항목을 더블 클릭하면 해당 항목이 txtProductName에 입력됩니다.

---

사용자 경험 향상

• 기존에는 btnSelectProduct_Click 버튼을 눌러야 값을 입력했지만, 이제 더블 클릭만으로 값 입력이 가능합니다.
• 버튼을 눌러야 하는 번거로움을 제거하여 사용자 편의성이 향상됩니다.

---

엑셀 VBA: History 시트에 데이터 저장 기능 구현하기

기능 개요

• 목적: UserForm에서 입력된 데이터를 History 시트에 저장하여 추적 및 기록 관리.
• 저장 내용:
  • No (고유 번호)
  • 날짜 및 시간
  • 제품 이름
  • 수량
  • 공정
  • 필요 수량

구현 순서

1. History 시트 준비

• History 시트에 아래와 같은 열 이름을 추가:
  • A열: No
  • B열: 날짜 및 시간
  • C열: 제품 이름
  • D열: 수량
  • E열: 공정
  • F열: 필요 수량

2. VBA 코드 작성

아래 코드는 btnSaveResults_Click 버튼 클릭 시 UserForm에서 데이터를 History 시트에 저장하는 기능을 구현합니다.

 

Private Sub btnSaveResults_Click()
    Dim wsResult As Worksheet
    Dim wsHistory As Worksheet
    Dim lastRowHistory As Long
    Dim currentTime As String
    Dim recordNumber As Long
    Dim resultRow As Long

    ' 시트 설정
    Set wsResult = ThisWorkbook.Sheets("Result")
    On Error Resume Next
    Set wsHistory = ThisWorkbook.Sheets("History")
    If wsHistory Is Nothing Then
        ' History 시트가 없으면 생성
        Set wsHistory = ThisWorkbook.Sheets.Add
        wsHistory.Name = "History"
        wsHistory.Cells(1, 1).Value = "No"
        wsHistory.Cells(1, 2).Value = "날짜 및 시간"
        wsHistory.Cells(1, 3).Value = "제품 이름"
        wsHistory.Cells(1, 4).Value = "수량"
        wsHistory.Cells(1, 5).Value = "공정"
        wsHistory.Cells(1, 6).Value = "필요 수량"
    End If
    On Error GoTo 0

    ' 현재 시간 가져오기
    currentTime = Now()

    ' History 시트에서 마지막 행 찾기 (A열 기준으로 마지막 번호 확인)
    lastRowHistory = wsHistory.Cells(wsHistory.Rows.Count, 1).End(xlUp).Row

    ' 고유 번호 계산
    If lastRowHistory <= 1 Then
        ' 첫 번째 저장인 경우
        recordNumber = 1
    Else
        ' 기존 번호의 마지막 값 + 1
        recordNumber = wsHistory.Cells(lastRowHistory, 1).Value + 1
    End If

    ' 기록 저장: No, 날짜 및 시간, 제품 이름, 수량
    lastRowHistory = lastRowHistory + 1 ' 새 데이터는 항상 다음 행부터 저장
    wsHistory.Cells(lastRowHistory, 1).Value = recordNumber ' No
    wsHistory.Cells(lastRowHistory, 2).Value = currentTime ' 날짜 및 시간
    wsHistory.Cells(lastRowHistory, 3).Value = Me.txtProductName.Value ' 제품 이름
    wsHistory.Cells(lastRowHistory, 4).Value = Me.txtQuantity.Value ' 수량

    ' Result 시트의 공정 및 필요 수량 저장
    resultRow = 5 ' Result 시트에서 공정 시작 행
    Do While wsResult.Cells(resultRow, 1).Value <> ""
        ' 공정과 필요 수량 저장
        wsHistory.Cells(lastRowHistory, 5).Value = wsResult.Cells(resultRow, 1).Value ' 공정
        wsHistory.Cells(lastRowHistory, 6).Value = wsResult.Cells(resultRow, 2).Value ' 필요 수량

        ' 다음 행으로 이동
        resultRow = resultRow + 1
        lastRowHistory = lastRowHistory + 1
    Loop

    MsgBox "결과가 History 시트에 저장되었습니다.", vbInformation
End Sub
    

코드 설명

1. History 시트 준비:
   • History 시트가 없는 경우 자동으로 생성하고, 열 이름을 추가합니다:
      

2. 데이터 저장 위치 계산:

• A열(No)의 마지막 값을 기준으로 다음 저장 위치를 계산:
   

 

3. 고유 번호(No) 계산:

• No는 이전 데이터의 마지막 번호 + 1로 계산: 
•  

4. 공정 데이터 저장:

• Sheet "Result"의 공정(A열)과 필요 수량(B열)을 반복문으로 읽어와 History 시트에 기록:

 

5. 메시지 출력:

• 저장 완료 후 사용자에게 알림 메시지를 표시

 

---

테스트 절차

1. UserForm 실행:
   • UserForm에서 제품 이름과 수량을 입력합니다.
2. 결과 확인:
   • "계산 실행" 버튼을 눌러 Sheet "Result"에 결과값을 확인합니다.
3. 데이터 저장:
   • "저장" 버튼을 눌러 결과값을 History 시트에 저장합니다.
4. History 시트 확인:
   • History 시트에 저장된 데이터가 No, 날짜 및 시간, 제품 이름, 수량, 공정, 필요 수량과 함께 올바르게 기록되었는지 확인합니다.

확장 가능성

• 특정 조건(날짜, 제품 이름 등)으로 데이터를 필터링하는 기능 추가.
• 저장 데이터를 주기적으로 백업하거나 다른 파일로 내보내는 기능 구현.

---

소요량 계산기 2501018.zip

0.03MB