오빠두엑셀 `2026 무료 챌린지` 오픈! 완주하고 수료증 받아가세요! 5년 연속 IT분야 베스트셀러! 「 진짜쓰는 실무엑셀 」로 2026년 공부 끝내기 엑셀이 막히셨나요? Q&A 게시판에서 바로 해결하세요.
메뉴
미해결 M365윈도우11

오빠두 선생님의 DB 함수 응용 방법을 알려주세요.

황
황금감자
10월 19일 조회 1,192
이 부분에 대하여 질문드립니다...

Product 를 눌러서 제품을 추가,삭제,수정하거나 또는 제품을 검색하고 클릭하여 "C10"부터 자료가 붙여넣기

되고 그 뒤에 다시 반복작업을 할 경우, "C10"에 입력이 됬으니 그 아래 "C11"행에 아래로 누적되어 입력되게

자료를 만들려고 하는데

__________________________________________________________

Dim WS As WorksheetSet WS = ThisWorkbook.Worksheets("고객명")Insert_Record WS, "값1", "값2", "값3", ...

____________________________________________________________

이 부분을 사용하면 될까요?

어떻게 입력하면 좋을까요? 예를들어 카테고리 1번, 카레고리 2번은 제외한 나머지만 붙여넣기를 한다면요.

사진과 수식 첨부 드립니다.

 

 

 

 

Private Sub P_B_C1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
frmCategory1.Show
End Sub
Private Sub P_B_C2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
frmCategory2.Show
End Sub
Private Sub P_B_C3_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
frmCategory3.Show
End Sub
Private Sub P_B_C4_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
frmCategory4.Show
End Sub
Private Sub P_B_UOM_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
frmUOM.Show
End Sub

Private Sub P_B_Close_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Unload Me
End Sub
Private Sub P_B_Close_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then Unload Me
End Sub

Private Sub P_B_Delete_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
P_Delete
End Sub
Private Sub P_B_Delete_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then P_B_Delete
End Sub

Private Sub P_B_Modify_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
P_Modify
End Sub
Private Sub P_B_Modify_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then P_B_Modify
End Sub

Private Sub P_B_Register_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
P_Register
End Sub
Private Sub P_B_Register_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then P_Register
End Sub

Private Sub P_B_Reset_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
P_Reset
End Sub
Private Sub P_B_Reset_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then P_Reset
End Sub


Private Sub txt_P_Search_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 27 Then Unload Me
End Sub


'유저폼에 추가한 버튼에 개수만큼 아래 명령문을 유저폼에 추가한 뒤, btnXXX 를 버튼 이름으로 변경합니다.
Private Sub P_B_Delete_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_Delete
End Sub
Private Sub P_B_Delete_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_Delete
End Sub

Private Sub P_B_Reset_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_Reset
End Sub
Private Sub P_B_Reset_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_Reset
End Sub

Private Sub P_B_Register_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_Register
End Sub
Private Sub P_B_Register_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_Register
End Sub

Private Sub P_B_Modify_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_Modify
End Sub
Private Sub P_B_Modify_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_Modify
End Sub

Private Sub P_B_Close_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_Close
End Sub
Private Sub P_B_Close_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_Close
End Sub

Private Sub P_B_Select_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_Select
End Sub
Private Sub P_B_Select_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_Select
End Sub

Private Sub P_B_C1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_C1
End Sub
Private Sub P_B_C1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OutHover_Css Me.P_B_C1
End Sub

Private Sub P_B_C2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_C2
End Sub
Private Sub P_B_C2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_C2
End Sub

Private Sub P_B_C3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_C3
End Sub
Private Sub P_B_C3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_C3
End Sub

Private Sub P_B_C4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_C4
End Sub
Private Sub P_B_C4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_C4
End Sub

Private Sub P_B_UOM_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.P_B_UOM
End Sub
Private Sub P_B_UOM_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.P_B_UOM
End Sub

Private Sub btnXXX_Enter()
OnHover_Css Me.btnXXX
End Sub


'아래 코드를 유저폼에 추가한 뒤, "btnXXX, btnYYY"를 버튼이름을 쉼표로 구분한 값으로 변경합니다.
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim ctl As Control
Dim btnList As String: btnList = "P_B_Delete, P_B_Reset, P_B_Register, P_B_Modify, P_B_Close, P_B_Select, P_B_C1, P_B_C2, P_B_C3, P_B_C4, P_B_UOM" ' 버튼 이름을 쉼표로 구분하여 입력하세요.
Dim vLists As Variant: Dim vList As Variant
If InStr(1, btnList, ",") > 0 Then vLists = Split(btnList, ",") Else vLists = Array(btnList)
For Each ctl In Me.Controls
 For Each vList In vLists
 If InStr(1, ctl.Name, Trim(vList)) > 0 Then OutHover_Css ctl
 Next
Next
End Sub
'커서 이동시 버튼 색깔을 변경하는 보조명령문을 유저폼에 추가합니다.
Private Sub OnHover_Css(lbl As Control): With lbl: .BackColor = RGB(211, 240, 224): .BorderColor = RGB(134, 191, 160): End With: End Sub
Private Sub OutHover_Css(lbl As Control): With lbl: .BackColor = &H8000000E: .BorderColor = -2147483638: End With: End Sub

Sub P_Reset()
Clear_Ctrls Me, "txt*", "txt_P_NameID,P_Search,P_ListBox"
End Sub

Sub P_Modify()
Dim DB As Variant
    DB = Get_DB(ShtProduct)

    Update_Record ShtProduct, Me.txtID.Value, Me.txt_P_Name01.Value, Me.txt_P_ComboBox02.Value, _
    Me.txt_P_ComboBox03.Value, Me.txt_P_ComboBox04.Value, Me.txt_P_ComboBox05.Value, _
    txt_P_Name06.Value, txt_P_Name07.Value, txt_P_Name08.Value, txt_P_Name09.Value, _
    txt_P_ComboBox10.Value, txt_P_Name11.Value

    Update_lstMain
    
    Select_ListItm Me.P_ListBox, Me.txtID.Value
End Sub


Sub P_Register()
Dim DB As Variant
Dim blnUnique As Boolean  'True/False
    DB = Get_DB(ShtProduct)
    blnUnique = IsUnique(DB, Me.txt_P_Name01.Value, 2)
    If blnUnique = False Then MsgBox "The same code exists. Please check again.", vbExclamation: Exit Sub
  
    Insert_Record ShtProduct, Me.txt_P_Name01.Value, Me.txt_P_ComboBox02.Value, _
    Me.txt_P_ComboBox03.Value, Me.txt_P_ComboBox04.Value, Me.txt_P_ComboBox05.Value, _
    txt_P_Name06.Value, txt_P_Name07.Value, txt_P_Name08.Value, txt_P_Name09.Value, _
    txt_P_ComboBox10.Value, txt_P_Name11.Value

    Update_lstMain
    P_Reset
    
    MsgBox "The product has been registered.", vbInformation
End Sub



Private Sub P_ListBox_Click()
Click_P_ListBox
End Sub
Private Sub UserForm_Initialize()
Update_lstMain
Update_UOM
Update_Category1
Update_Category2
Update_Category3
Update_Category4
End Sub
Sub Click_P_ListBox()
Dim vArr As Variant
    vArr = Get_ListItm(Me.P_ListBox)
    
    Me.txtID.Value = vArr(0)
    Me.txt_P_Name01.Value = vArr(1)
    Select_CboItm Me.txt_P_ComboBox02, vArr(2)
    Select_CboItm Me.txt_P_ComboBox03, vArr(3)
    Select_CboItm Me.txt_P_ComboBox04, vArr(4)
    Select_CboItm Me.txt_P_ComboBox05, vArr(5)
    Me.txt_P_Name06.Value = vArr(6)
    Me.txt_P_Name07.Value = vArr(7)
    Me.txt_P_Name08.Value = vArr(8)
    Me.txt_P_Name09.Value = vArr(9)
    Select_CboItm Me.txt_P_ComboBox10, vArr(10)
    Me.txt_P_Name11.Value = vArr(11)
End Sub

Sub Update_lstMain()
Dim DB As Variant

    DB = Get_DB(ShtProduct)
    DB = Connect_DB(DB, 2, ShtP_Ca1, "Category1")
    DB = Connect_DB(DB, 3, ShtP_Ca2, "Category2")
    DB = Connect_DB(DB, 4, ShtP_Ca3, "Category3")
    DB = Connect_DB(DB, 5, ShtP_Ca4, "Category4")

    Update_List Me.P_ListBox, DB, "0;80;0;0;0;0;100;170;170;50;50;50;100;60;60;60"
End Sub
Sub Update_UOM()
Dim DB As Variant
    DB = Get_DB(ShtUOM, True)
    
    Update_Cbo Me.txt_P_ComboBox10, DB
End Sub

Sub Update_Category1()
Dim DB As Variant
    DB = Get_DB(ShtP_Ca1)
    
    Update_Cbo Me.txt_P_ComboBox02, DB, 2
End Sub
Sub Update_Category2()
Dim DB As Variant
    DB = Get_DB(ShtP_Ca2)
    
    Update_Cbo Me.txt_P_ComboBox03, DB, 2
End Sub
Sub Update_Category3()
Dim DB As Variant
    DB = Get_DB(ShtP_Ca3)
    
    Update_Cbo Me.txt_P_ComboBox04, DB, 2
End Sub
Sub Update_Category4()
Dim DB As Variant
    DB = Get_DB(ShtP_Ca4)
    
    Update_Cbo Me.txt_P_ComboBox05, DB, 2
End Sub

 

 

 
#VBA·매크로 #폼·컨트롤 #DB 함수

댓글 0

질문답변 게시판의 최근 글

스크랩 완료