[¼ö°­±â°£] 2010. 11. 1 ~ 2011. 10. 31
½Åû¹®ÀÇ : À¥Å¸ÀÓ±³À°¼¾ÅÍ 02-3477-8300, ¿î¿µ°ü¸® : ¿ÀÇǽºÆ©ÅÍ 070-7098-2554

±³À°°úÁ¤ ³ªÀÇÇнÀ ¿ÀÇǽºÆÁ °Ë»ö ¹«·á°­ÁÂ
Home ·Î±×ÀΠȸ¿ø°¡ÀÔ °í°´¼¾ÅÍ »çÀÌÆ®¸Ê
¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º > ¹«·á°­ÁÂ
¿¢¼¿ 2000 µû¶óÇϱâ
Ȩ·¯´× ¿¢¼¿ 2000°­ÁÂ
À繫 ÇÔ¼ö °­ÁÂ
¿¢¼¿ 2002 »õ·Î¿î ±â´É
Ȳ±â¼º´ÔÀÇ VBA°­ÁÂ
°û½ÂÁÖ´ÔÀÇ VBAÀ̾߱â
Home > ¹«·á°­Á > ¿¢¼¿
¿¢¼¿, °û½ÂÁÖ´ÔÀÇ ¿ÀÆ©°ø±¸ÇÔ Á¦ÀÛÀ¸·Î ¹è¿öº¸´Â VBA À̾߱â, Excel

2. µµ±¸ ¸ðÀ½ ¸¸µé±â - ¥°

ÀÚ·á´Ù¿î·Îµå : ¿ÀÆ©°ø±¸ÇÔ002.xls

¾È³çÇϼ¼¿ä.
Áö³­ ¹ø¿¡´Â °­ÀÇ¿¡ ³Ê¹« ¸ôµÎÇÏ´Ù º¸´Ï ÀÌ·±Àú·± ¾ê±â¸¦ ¸øÇÑ °Í °°±º¿ä.

Á¶¸¸°£ MS¿ÀÇǽºXP ¹öÀüÀÌ Ãâ½ÃµÉ·Á´Â ¸ð¾çÀÔ´Ï´Ù. ¿©±âÀú±â¼­ XP¾ê±â¸¦ ÇÏ´õ±º¿ä. XPÀÇ »õ·Î¿î ±â´É¿¡ ´ëÇØ ¸¹ÀÌ µé¾îº¸¼Ì´ÂÁö¡¦ Á¦°¡ ±Ã±ÝÇÑ °ÍÀº ¿©·¯ºÐµéÀÌ XP·Î ¾÷±×·¹À̵å ÇÏ½Ç ÀÇÇâÀÌ ÀÖ´ÂÁö ÀÔ´Ï´Ù. ¹°·Ð Á¦°¡ XPÆÄ´Â »ç¶÷Àº ¾Æ´ÏÁö¸¸ ¿©·¯ºÐÀÇ »ý°¢ÀÌ ÀúÀÇ ºñ½ÁÇÑÁö ¾Ë°í ½Í±º¿ä. Á¦»ý°¢ÀÌ¿ä? ³ªÁß¿¡ °¡¸£ÃÄ µå¸®ÁÒ. ¸¸ÀÏ Á¦°¡ ±àÁ¤ÀûÀ¸·Î ¾Æ´Ï¸é ¹Ý´ë·Î ºÎÁ¤ÀûÀ¸·Î ¾ê±âÇÏ¸é »ý°¢ÀÌ ¹Ù²î½Ç ºÐµµ ÀÖÀ» Áöµµ ¸ð¸£´Ï±î¿ä? ¾Æ½¬¿î°Ô ÀÖ´Ù¸é ÇѱÛÆÇ ¿ÀÇǽº XP¹öÀüÀº À½¼ºÁö¿ø±â´ÉÀÌ ºüÁ® ÀÖ´Ù´õ±º¿ä. ¾ÆÁ÷ MS°¡ ÇÊ¿ä·Î ÇÏ´Â ±â¼úÀ» °¡Áø º¥´õ¸¦ ãÁö ¸øÇØ ºüÁ® ÀÖ´Ù´øµ¥, ÀÌ ±â´ÉÀÌ µÈ´Ù¸é Å°º¸µå µÎµå¸®Áö ¾Ê°í ¿ø°í¸¦ ¾µ ¼ö ÀÖÁö ¾ÊÀ» ±î ½Í±º¿ä.

XP¿Í °ü·ÃÇÏ¿© ÇÑ °¡Áö ¾ê±â¸¦ ÇÏÀÚ¸é ¸¶ÀÌÅ©·Î¼ÒÇÁÆ®(MS)¿ÀÇǽº XP¿¡¼­´Â ¿ÀÇǽº±æÀâÀÌ°¡ »ç¶óÁú Àü¸ÁÀÔ´Ï´Ù.
¾Æ¹« ¶§³ª ºÒ¾¦ Æ¢¾î³ª¿Í µµ¿ò¸»À» ÁÖ°Ú´Ù°í ÀÚûÇÏ´Â ±ÍÂúÀº Á¸ÀçÀÎ ¿ÀÇǽº ±æÀâÀ̸¦ ¿µ¾î·Î´Â 'Ŭ¸®ÇÇ'(Clippy)¶ó°í ÇÕ´Ï´Ù. »ç½Ç ¾ê°¡ ¸ð¾çÀÌ ¿ì½º¿ö¼­ "ÀÌ°Ô ¹¹¾ß?"ÇßÁö¸¸ Á¾À̸¦ ¹­¾î µÎ´Â Ŭ¸³ÀÔ´Ï´Ù. »ç½Ç ¾ê ¸»°íµµ ÀÌ»ÛÀÌ, µ¹°í·¡, ¾ÆÀν´Å¸ÀιڻçµîÀÌ ±æÀâÀÌ·Î ³ª¼¹Áö¸¸ ¾î´Â °Í Çϳª »ç¶û¹ÞÁö ¸øÇÏ°í ¿ì¸®ÀÇ ±â¾ï¼Ó¿¡ ±ÍÂú¾Æ¼­ ¾ó¸¥ ¼û°Ü¹ö¸®´Â Á¸ÀçÀÔ´Ï´Ù(ºÒ½ÖÇÏÁÒ?)
¿Ü±¹¾ð·ÐµéÀº "MSµµ ÀÌ·¯ÇÑ »çÁ¤À» ¾Æ´Â µí Ŭ¸®ÇǸ¦ ÇØ°íÇÑ´Ù"°í º¸µµÇϴµ¥ "ÇØ°í"¶ó´Â Ç¥ÇöÀÌ Á¦¹ý Àç¹ÌÀÖ½À´Ï´Ù. ±×·³ ¾Æ·¡¾Æ Çѱۿ¡¼­ ä¿ë½ÃÄÑ ÁÙ·Á³ª?

¿äÁò ±¹³»°Ç ¿Ü±¹ÀÌ°Ç ´åÄıâ¾÷(Àϸí ÂÀÄıâ¾÷)ÀÇ È­µÎ´Â ¼­ºñ½ºÀ¯·áÈ­ÀÔ´Ï´Ù. ¹«·á¿¡ ±æµé¿©Á® ´ë¸Ó¸®°¡ µÇ¾î¹ö¸° ³×ƼÁðµéÀÇ Áö°©À» ¾î¶»°Ô ¹ú¸± ¼ö ÀÖÀ»±î¿ä? °¡Àå À¯¸ÁÇÑ ÄÜÅÙÃ÷´Â °ÔÀÓ°ú ±³À°ºÐ¾ßÀÔ´Ï´Ù. Àú °°Àº »ç¶÷µµ ¿Â¶óÀα³À°¿¡ °ü½ÉÀÌ Àְŵç¿ä. Çпø°¡±â¿£ ³Ê¹« ½Ã°£ÀÌ ¾ø°í µ·ÀÌ ¸¹ÀÌ µé´Ùº¸´Ï Á¦´ë·ÎµÈ ¿Â¶óÀα³À°¿¡ ´«±æÀÌ °¡´õ±º¿ä. ÄÜÅÙÃ÷À¯·áÈ­¿¡ ´ëÇÑ ÀÌ·±Àú·± ¾ê±â¸¦ µè´Ùº¸´Ï °¡Àå °¡±î¿î ÂÀÄıâ¾÷ÀÎ ¿ÀÇǽºÆ©ÅÍ´Â ¾î¶²Áö ¸ð¸£°Ú±º¿ä, ¹¯°í´äÇϱ⸦ À¯·áÈ­ÇÏ¸é ´©°¡ °¨È÷ ¹°¾îº¼±î? ¾Æ´Ï¸é 1³â¿¡ ¸¸¿ø¾¿ ȸºñ¸¦ ¹ÞÀ¸¸é ¾î¶³±î? ¿ÀÆ©¸¦ º¸¸é ±â¾÷ÀÌ ¾Æ´Ï¶ó µ¿È£È¸ ¼º°ÝÀÌ Â£Àºµ¥ ¼ö¼ö·áº¸´Ù´Â ȸºñ¶ó´Â À̸§ÀÌ ¾î¿ï¸± °Í °°±º¿ä ±×·¯¸é ¼­¹öµµ Å­Á÷ÇÑ °Å »ç°í Á÷¿øµé ¿ù±Þµµ ´õ ÁÖ¾î ¼­ºñ½º¸¦ °³¼±ÇÒ ¼ö ÀÖÁö ¾ÊÀ»±î? 1³â¿¡ ¸¸¿øÀ¸·Î´Â ¾î¸²µµ ¾øÀ¸·Á³ª? ^.^;

ÀÚ ÀÌÁ¦ ¾ê±â´Â ±×¸¸ÇÏ°í °­ÀǸ¦ ÇÏÁÒ. °­ÀÇ¿¡ µ· ¾È¹ÞÀ¸´Ï±î ¶³Áö ¸¶¼¼¿ä( ^^ )
¿¢¼¿À» ºñ·ÔÇÏ¿© ´Ù¸¥ ¸¶ÀÌÅ©·Î¼ÒÇÁÆ® ¿ÀÇǽº ÀÀ¿ëÇÁ·Î±×·¥µéÀº ¸Þ´º¿Í µµ±¸¸ðÀ½ ÀÛ¼º¿¡ ´ëÇØ °°Àº ¹æ¹ýÀ» »ç¿ëÇÏ°í ÀÖ½À´Ï´Ù. Áï ¿ÀÇǽº ¾î´À Á¦Ç°¿¡¼­³ª CommandBars°³Ã¼¸¦ ÀÌ¿ëÇÏ¿© ¸Þ´º, µµ±¸¸ðÀ½, Æ˾÷¸Þ´º¸¦ ¸¸µé ¼ö ÀÖ½À´Ï´Ù. ¸Þ´º´Â Áö³­ ½Ã°£¿¡ ÀÌ¹Ì ´Ù·ç¾ú°í, Æ˾÷¸Þ´º´Â ´ÙÀ½ ½Ã°£¿¡ ´Ù·ê °ÍÀÔ´Ï´Ù. ¿À´ÃÀº µµ±¸¸ðÀ½¿¡ ´ëÇؼ­¸¸ ¾Ë¾Æº¸µµ·Ï ÇÏ°Ú½À´Ï´Ù.

º»°ÝÀûÀÎ Á¦ÀÛ¿¡ ¾Õ¼­ CommandBars°³Ã¼¸¦ °¡Áö°í ¿¢¼¿ÀÇ ¸Þ´ºÃ¼°è¸¦ ŽÇèÇغ¸µµ·Ï ÇÏ°Ú½À´Ï´Ù. »ç½Ç Áö³­ ½Ã°£¿¡ ¸Þ´º¸¦ ÀÛ¼ºÇϱâ Àü¿¡ ÀÌ°É ¸ÕÀú ÇØ¾ß Çߴµ¥, ¸¶À½ÀÌ ±ÞÇÏ´Ù º¸´Ï °æȲÀÌ ¾ø¾ú½À´Ï´Ù.
¾Æ·¡ÀÇ ÇÁ·Î½ÃÁ®´Â CommandBarsÄ÷º¼Ç°³Ã¼¸¦ Ž»öÇÏ°í ±× ÀÌÇÏÀÇ Control°³Ã¼¸¦ º¸¿©ÁÖ´Â °ÍÀÔ´Ï´Ù. Ãâ·ÂµÈ ³»¿ëÀ» º¸½Ã¸é ¸Þ´ºÀÇ À̸§°ú °¢Á¾ µµ±¸¸ðÀ½ÀÇ À̸§ µîÀÌ º¸ÀÏ °ÍÀÔ´Ï´Ù.

Sub NavigateCommandBars()
     Dim cmdbarNavigator As CommandBar
     Dim cmdctlControl As CommandBarControl

     For Each cmdbarNavigator In Application.CommandBars
        Debug.Print cmdbarNavigator.Name
        For Each cmdctlControl In cmdbarNavigator.Controls
           Debug.Print cmdctlControl.Caption
        Next
     Next
End Sub

¿©·¯ºÐÀÇ µµ±¸¸ðÀ½À» ±âÁ¸ÀÇ µµ±¸¸ðÀ½¿¡ Ãß°¡ÇÏ·Á¸é cmdbarNavigator.NameÀ» Âü°íÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î Ç¥Áصµ±¸¸ðÀ½ÀÇ À̸§Àº "Standard "ÀÔ´Ï´Ù. ¿ÀÆ©°ø±¸ÇÔ ¹öÆ°À» Ç¥Áصµ±¸¸ðÀ½¿¡ ¸¸µé·Á¸é ´ÙÀ½°ú °°½À´Ï´Ù.

'Ç¥ÁØ µµ±¸¸ðÀ½ÀÇ ÄÁÆ®·Ñ¼ö¸¦ µ¹·ÁÁØ´Ù.
Private Function FindNumOfBtn() As Byte
     FindNumOfBtn = CommandBars("Standard").Controls.Count
End Function

'Ç¥ÁØ µµ±¸¸ðÀ½¿¡ "OT-Tools"¸¦ Ãß°¡ÇÑ´Ù.
Public Sub CreateUserCmdBarBtn()
     Dim cmdbtnUser As CommandBarButton

     Set cmdbtnUser = CommandBars("Standard").Controls.Add(Type:=msoControlButton, Before:=FindNumOfBtn())

     With cmdbtnUser
        .Style = msoButtonCaption
        .FaceId = 17
        .Caption = "OT-Tools"
        .OnAction = "CommandBar"
        .BeginGroup = True
     End With

     Set cmdbtnUser = Nothing
End Sub
'Ç¥ÁØ µµ±¸¸ðÀ½¿¡¼­ "OT-Tools"¸¦ »èÁ¦ÇÑ´Ù.
Public Sub DeleteUserCmdBtn()
     Dim cmdbtnUser As CommandBarControl

     For Each cmdbtnUser In CommandBars("Standard").Controls
        If cmdbtnUser.Caption = "OT-Tools" Then
           cmdbtnUser.Delete
        End If
     Next cmdbtnUser
End Sub

¡¡

»èÁ¦¿Í °ü·ÃÇÏ¿© Á»´õ ¼¼·ÃÇÑ ±â¹ýÀ» º¸¿©µå¸®ÁÒ!
´ÙÀ½Àº º¸ÀÌÁö ¾Ê´Â ¸ðµç »ç¿ëÀÚ Á¤ÀÇ ¸í·É Ç¥½ÃÁÙÀ» »èÁ¦ÇÏ´Â ¿¹Á¦ÀÔ´Ï´Ù.
For Each bar In Application.CommandBars
If Not bar.BuiltIn And Not bar.Visible Then bar.Delete
Next
BuiltIn¼Ó¼ºÀº ¿¢¼¿ÀÇ ³»À尳üÀΰ¡ ¾Æ´Ï¸é »ç¿ëÀÚ°¡ ¸¸µç °³Ã¼Àΰ¡¸¦ True/False°ªÀ¸·Î µ¹·ÁÁÝ´Ï´Ù. ¶ÇÇÑ Visible¼Ó¼ºÀº ÇöÀç È­¸é»ó¿¡ Ç¥½Ã°¡ µÇ´Â °¡¸¦ È®ÀÎÇÏ´Â °ÍÀÌÁÒ.

ÀÌÁ¦ºÎÅÍ´Â ¿ö¹Ö-¾÷À» ³¡³»°í º»°ÝÀûÀÎ ÄÚµù¿¡ µé¾î°¡°Ú½À´Ï´Ù. ¸ÕÀú Äڵ带 º¸¿© µå¸®ÁÒ.
¡¡

¸ðµâ : user_toolbarOption Explicit
Const USER_TAG As String = "UserToolbar"
Const CBO_TAG As String = "UserCombo"
Const TOOL_NAME As String = "¿ÀÆ©°ø±¸ÇÔ"

Sub CreateUserToolbar()
     Dim cmdbarUser As CommandBar

     On Error GoTo ErrHandler

     Set cmdbarUser = Application.CommandBars.Add(Name:=TOOL_NAME, Position:=msoBarTop)

     CreateUserButton cmdbarUser
     CreateUserCombo cmdbarUser

     cmdbarUser.Visible = True
     Set cmdbarUser = Nothing
     Exit Sub

ErrHandler:
     DeleteUserToolbar
     Resume
End Sub

Sub DeleteUserToolbar()
     Dim cmdbarUser As CommandBar

     On Error GoTo ErrHandler

     Set cmdbarUser = Application.CommandBars(TOOL_NAME)
     cmdbarUser.Delete
     Set cmdbarUser = Nothing
     Exit Sub

ErrHandler:
     Exit Sub
End Sub

Sub CreateUserButton(cmdbarUser As CommandBar)
     Dim i As Byte
     Dim cmdctlUser As CommandBarButton
     Dim NameFaceIDMacroOfCmdBtn

     NameFaceIDMacroOfCmdBtn = Array( _
     "otHELP", 49, "sbShowHelp", False, _
     "otABOUT", 625, "sbShowAbout", True, _
     "otEXIT", 358, "sbExit", True _
     )

     For i = LBound(NameFaceIDMacroOfCmdBtn) To UBound(NameFaceIDMacroOfCmdBtn) Step 4
        Set cmdctlUser = cmdbarUser.Controls.Add(Type:=msoControlButton, before:=1)
        With cmdctlUser
           .Caption = NameFaceIDMacroOfCmdBtn(i)
           .FaceId = NameFaceIDMacroOfCmdBtn(i + 1)
           .OnAction = NameFaceIDMacroOfCmdBtn(i + 2)
           If NameFaceIDMacroOfCmdBtn(i + 3) Then .BeginGroup = True
        End With
      Next i
      Set cmdctlUser = Nothing
End Sub

Sub CreateUserCombo(cmdbarUser As CommandBar)
     Dim cmdctlUser As CommandBarComboBox

     Set cmdctlUser = cmdbarUser.Controls.Add(Type:=msoControlComboBox, Id:=1, before:=1)
     With cmdctlUser
        .Caption = "Date-Format Selector"
        .OnAction = "cmdcboUserAction"
        .BeginGroup = True
        .AddItem Text:="Select any Date-Format"
        .AddItem Text:="YYYY-MM-DD"
        .AddItem Text:="YY-MM-DD"
        .AddItem Text:="YY.MM.DD"
        .Width = 150
        .ListIndex = 1
        .Tag = CBO_TAG
     End With
     Set cmdctlUser = Nothing
End Sub

Sub cmdcboUserAction()
     Dim cmdctlUser As CommandBarComboBox

     Set cmdctlUser = Application.CommandBars(TOOL_NAME) _
        .FindControl(Type:=msoControlComboBox, Tag:=CBO_TAG)
     MsgBox "You Selected! " & vbCrLf & cmdctlUser.Text, vbInformation
End Sub

À§¿¡¼­ Áß½ÉÀÌ µÇ´Â ÇÁ·Î½ÃÁ®´Â °¡Àå ¸ÕÀú ³ª¿À´Â Sub CreateUserToolbar( )ÀÔ´Ï´Ù. ¿©±â¼­´Â ±âº»ÀÌ µÇ´Â µµ±¸¸ðÀ½À» ¸¸µé°í ±× µµ±¸¸ðÀ½À§¿¡¼­ ¹öÆ°À» ¸¸µå´Â Sub CreateUserButton(cmdbarUser As CommandBar) °ú ÄÞº¸¹Ú½º¸¦ ¸¸µå´Â Sub CreateUserCombo(cmdbarUser As CommandBar)¸¦ È£ÃâÇÕ´Ï´Ù. ±×°Ô ÀüºÎÀÔ´Ï´Ù. °£´ÜÇÏÁÒ.
¡¡

¸ñÂ÷ | ÀÌÀü | ´ÙÀ½

¡¡

¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º 135-880 ¼­¿ï½Ã °­³²±¸ »ï¼ºµ¿ 157-3 ¿¤ÁöÆ®À©ÅÚ 2Â÷ 1603È£ ¢Ï070-7098-2554
Copyright ¨Ï 1999-2008 Officetutor.com All rights reserved
¡¡