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

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

17. ¼¼ °¡Áö ½ÃÆ®°ü·Ã ¸ÅÅ©·Î

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

»õÇØ Àλ簡 ´Ê¾ú½À´Ï´Ù. »õÇØ º¹ ¸¹ÀÌ ¹ÞÀ¸½Ã°í, °Ç°­ÇϽʽÿÀ. ¿À´ÃÀº ½ÃÆ®¿Í °ü·ÃÇÑ 3°¡Áö ¸ÅÅ©·Î¸¦ º¸¿©µå¸®°Ú½À´Ï´Ù. ¿©·¯ ÀåÀÇ ¿öÅ©½ÃÆ®¸¦ Á¤·ÄÇÏ´Â ¸ÅÅ©·Î, °³º° ¿öÅ©½ÃÆ®¸¦ ÇϳªÀÇ ÅëÇÕ¹®¼­·Î µû·Î ÀúÀåÇÏ´Â ¸ÅÅ©·Î, µÎ °³ÀÇ ½ÃÆ®¸¦ ºñ±³ÇÏ¿© ´Ù¸¥ ºÎºÐÀ» Ç¥½ÃÇÏ´Â ¸ÅÅ©·Î ÀÔ´Ï´Ù. ¿À´ÃÀº ´Ù·ç´Â ³»¿ëÀÌ ¸¹¾Æ ¾ÕÀÇ µÎ °³ÀÇ ¸ÅÅ©·Î´Â °£´ÜÈ÷ ³Ñ¾î°¡°í ¸¶Áö¸· ¸ÅÅ©·Î¸¸ ¼³¸íÇÒ °ÍÀÔ´Ï´Ù.

¿©·¯ ÀåÀÇ ¿öÅ©½ÃÆ®¸¦ Á¤·ÄÇÏ´Â ¸ÅÅ©·Î´Â ÀÌ¹Ì °ø°³µÈ °ÍÀÌ ¿©·µ ÀÖ½À´Ï´Ù. ±×¸®°í °ø±³·Ó°Ôµµ Áö³­ Ä÷³ÀÇ ÁÖÁ¦µµ Á¤·Ä¿¡ °üÇÑ °ÍÀ̾úÀ¸´Ï Á¦°¡ µû·Î ¸¸µé ÇÊ¿ä°¡ ¾øÀ» °Í °°±º¿ä. ±×·¡¼­ À̹ø¿¡´Â ±× À¯¸íÇÑ John Walkenbach´ÔÀÇ ¼Ò½º¸¦ ±×´ë·Î »ç¿ëÇÒ±î ÇÕ´Ï´Ù. Á¤·Ä¾Ë°í¸®ÁòÀº °£´ÜÇÑ ¹öºí¼ÒÆ®¸¦ »ç¿ëÇß½À´Ï´Ù. ±×¸®°í ±× ¼ø¼­¿¡ µû¶ó Sheet°³Ã¼ÀÇ Move¸Þ¼Òµå¸¦ »ç¿ëÇÕ´Ï´Ù. 

modSortSheets.bas
Sub SortSheets()
     ' This routine sorts the sheets of the
     ' active workbook in ascending order.

     Dim SheetNames() As String
     Dim i As Integer
     Dim SheetCount As Integer
     Dim VisibleWins As Integer
     Dim Item As Object
     Dim OldActive As Object

     On Error Resume Next
     SheetCount = ActiveWorkbook.Sheets.Count
     If Err <> 0 Then Exit Sub ' No active workbook

     ' Check for protected workbook structure
          If ActiveWorkbook.ProtectStructure Then
               MsgBox ActiveWorkbook.Name & " is protected.", _
                    vbCritical, "Cannot Sort Sheets."
               Exit Sub
          End If

     ' Disable Ctrl+Break
     Application.EnableCancelKey = xlDisabled

     ' Get the number of sheets
     SheetCount = ActiveWorkbook.Sheets.Count

     ' Redimension the array
     ReDim SheetNames(1 To SheetCount)

     ' Store a reference to the active sheet
     Set OldActive = ActiveSheet

     ' Fill array with sheet names and hidden status
     For i = 1 To SheetCount
          SheetNames(i) = ActiveWorkbook.Sheets(i).Name
     Next i

     ' Sort the array in ascending order
     Call BubbleSort(SheetNames)

     ' Turn off screen updating
     Application.ScreenUpdating = False

     ' Move the sheets
     For i = 1 To SheetCount
          ActiveWorkbook.Sheets(SheetNames(i)).Move _
               ActiveWorkbook.Sheets(i)
     Next i

    ' Reactivate the original active sheet
     OldActive.Activate
End Sub

Sub BubbleSort(List() As String)
     ' Sorts the List array in ascending order
     Dim First As Integer, Last As Integer
     Dim i As Integer, j As Integer
     Dim Temp

     First = LBound(List)
     Last = UBound(List)
     For i = First To Last - 1
          For j = i + 1 To Last
               If List(i) > List(j) Then
                    Temp = List(j)
                    List(j) = List(i)
                    List(i) = Temp
               End If
          Next j
    Next i
End Sub

´ÙÀ½Àº °³º° ¿öÅ©½ÃÆ®¸¦ ÇϳªÀÇ ÅëÇÕ¹®¼­·Î µû·Î ÀúÀåÇÏ´Â ¸ÅÅ©·Î¸¦ »ìÆ캸°Ú½À´Ï´Ù. »ç½Ç ÇÑ ÁÙÀÇ ¸ÅÅ©·ÎÀÔ´Ï´Ù. 

Sub CopyActiveSheetAsNewWorkbook()
     ActiveSheet.Copy
End Sub

ÇöÀçÀÇ ¿öÅ©½ÃÆ®¸¦ °¡¸®Å°´Â ActiveSheet°³Ã¼ÀÇ Copy ¸Þ¼Òµå¸¦ »ç¿ëÇÏ´Â °ÍÀÌ ÀüºÎÀÔ´Ï´Ù. Á¶±Ý ´õ ±â´ÉÀ» ºÎ°¡ÇÏÀÚ¸é ¼±ÅÃÇÑ ¿öÅ©½ÃÆ®¿¡ ´ëÇؼ­ ÅëÇÕ¹®¼­·Î ÀúÀåÇÏ´Â ±â´ÉÀÌ ÀÖÀ¸¸é ÁÁÀ» °ÍÀÔ´Ï´Ù. 

¸¶Áö¸· ¼Ò°³ÇÏ´Â °ÍÀº µÎ °³ÀÇ ½ÃÆ®¸¦ ºñ±³ÇÏ¿© ´Ù¸¥ ºÎºÐÀ» Ç¥½ÃÇÏ´Â ¸ÅÅ©·Î ÀÔ´Ï´Ù. »ç¿ë¹æ¹ýÀº °£´ÜÇÕ´Ï´Ù. ÄÞº¸»óÀÚ¿¡¼­ ºñ±³ÇÒ µÎ °³ÀÇ ½ÃÆ®¸¦ ¼±ÅÃÇÏ°í, Ç¥½ÃÇÒ »ö»óÀ» ¼±ÅÃÇÕ´Ï´Ù. ±×¸®°í Compare¹öÆ°À» Ŭ¸¯ÇÏ¸é ´Ù¸¥ ºÎºÐÀ» ÁöÁ¤ÇÑ »ö»óÀ¸·Î Ç¥½ÃÇÕ´Ï´Ù. 

ºñ±³ÇÏ´Â ¹æ¹ýÀº ¹«Ã´ ´Ü¼ø¹«½ÄÇÕ´Ï´Ù. µ¥ÀÌÅÍ°¡ ±â·ÏµÈ ¿µ¿ª¿¡¼­ µ¿ÀÏÇÑ À§Ä¡ÀÇ °¢ ¼¿À» ¼­·Î ºñ±³ÇÕ´Ï´Ù. µû¶ó¼­ ºñ±³ÇÒ ¿µ¿ªÀÌ ¹«Ã´ Å©´Ù¸é ºÎÇÏ°¡ ¸¹ÀÌ °É¸± °ÍÀÔ´Ï´Ù. 

»ö»óÀ» ¼±ÅÃÇÏ´Â ºÎºÐÀº John Walkenbach´ÔÀÇ ColorPicker¶ó´Â ÇÁ·Î±×·¥¼Ò½º¸¦ ÀÀ¿ëÇß½À´Ï´Ù. ÀÌ ºÎºÐÀº Ŭ·¡½º¸ðµâÀ» »ç¿ëÇÏ¿´´Âµ¥, Áö³­ Ä÷³Áß Å¬·¡½º¿¡ ´ëÇÑ ¾ð±ÞÀÌ ¾ø¾î °øºÎ¸¦ À§ÇØ ÀϺη¯ ³¢¿ö³õ¾Ò½À´Ï´Ù.

frmCompareSheets.frm ¡¡
À¯ÀúÆû »ó´ÜÀÇ µÎ °³ÀÇ ÄÞº¸»óÀÚ¿¡¼­´Â ºñ±³ÇÒ ½ÃÆ®¸¦ ¼±ÅÃÇÕ´Ï´Ù. À̶§ ±âÁØÀÌ µÇ´Â °ÍÀº ù¹ø° ÄÞº¸»óÀÚ¿¡ Ç¥½ÃµÈ Sheet1À̸ç Sheet2¿Í ´Ù¸¥ºÎºÐÀÌ ÀÖ´Ù¸é Sheet1¿¡ ¾Æ·¡¾Ö¼­ ¼±ÅÃÇÑ »ö»óÀÌ Ç¥½ÃµË´Ï´Ù.

¾Æ·¡ÀÇ »ö»ó¼±ÅùöÆ°Àº John Walkenbach´ÔÀÇ ColorPicker¶ó´Â ÇÁ·Î±×·¥¼Ò½º¸¦ ÀÀ¿ëÇÑ °ÍÀ¸·Î Ŭ·¡½º¸ðµâÀ» »ç¿ëÇÏ¿´½À´Ï´Ù.
Private Sub CancelButton_Click()
     ColorValue = False
     Unload Me
End Sub
ColorValue´Â »ç¿ëÀÚ°¡ ¿øÇÏ´Â »ö»óÀ» °¡Áö´Â VariantÇü º¯¼öÀÔ´Ï´Ù. ¸¸ÀÏ Ãë¼Ò¹öÆ°À» Ŭ¸¯ÇÏ´Â °æ¿ì À§ÀÇ À̺¥Æ®(CancelButton_Click)ÇÁ·Î½ÃÁ®°¡ ½ÇÇàµÇ°í ¿©±â¿¡¼­ ColorValue¿¡ False°ªÀÌ ³Ö°í ÆûÀ» ¾ð·ÎµåÇÕ´Ï´Ù(Unload Me)

Private Sub cboSheet1_Change()
     If cboSheet1.Text = cboSheet2.Text Then
          MsgBox "¼±ÅÃÇÏ·Á´Â ½ÃÆ®°¡ ÀÌ¹Ì ¼±ÅõǾî ÀÖ½À´Ï´Ù", vbInformation
          cboSheet1.Text = ""
     End If
End Sub

Private Sub cboSheet2_Change()
     If cboSheet1.Text = cboSheet2.Text Then
          MsgBox "¼±ÅÃÇÏ·Á´Â ½ÃÆ®°¡ ÀÌ¹Ì ¼±ÅõǾî ÀÖ½À´Ï´Ù", vbInformation
          cboSheet2.Text = ""
     End If
End Sub

À§´Â ÄÞº¸»óÀÚÀÇ ¼±ÅÃÇÑ Ç׸ñÀÌ ¹Ù²ð ¶§ ½ÇÇàµÇ´Â À̺¥Æ®ÇÁ·Î½ÃÁ®ÀÔ´Ï´Ù. ÀÌ À̺¥Æ®´Â µÎ °³ÀÇ ÄÞº¸»óÀÚ(cboSheet1,cboSheet2)°¡ °°Àº ½ÃÆ®¸¦ ¼±ÅÃÇÏ´Â °ÍÀ» ¸·±â À§ÇÑ °ÍÀÔ´Ï´Ù. Sheet1´ë Sheet1ó·³ ÀÚ±â ÀڽŰú ºñ±³ÇÏ´Â °Ç ¾µµ¥¾ø´Â ÀÏÀÏ °ÍÀÔ´Ï´Ù.

Private Sub OKButton_Click()
     If Len(cboSheet1.Text) = 0 Or Len(cboSheet2.Text) = 0 Then
          MsgBox "½ÃÆ®¸¦ ¼±ÅÃÇÏÁö ¾Ê¾Ò½À´Ï´Ù.", vbCritical
     Else
          CompareSheetsMain cboSheet1.Text, cboSheet2.Text
          Unload Me
     End If
End Sub

Compare¹öÆ°(OKButton)À» Ŭ¸¯ÇÏ¸é ½ÃÆ®¸¦ ºñ±³ÇÏ´Â CompareSheetsMainÀ» È£ÃâÇÏ°í ¿©±â¿¡ cboSheet1°ú cboSheet2ÀÇ ¼±ÅÃÇÑ ³»¿ë(Text)À» Àü´ÞÇÕ´Ï´Ù. 

¡¡

modCompareSheets.bas
Public ColorValue      As Variant
ColorValue´Â »ç¿ëÀÚ°¡ ¼±ÅÃÇÑ »ö»óÀ» ÀúÀåÇÏ´Â º¯¼öÀÔ´Ï´Ù. ÀÌ°ÍÀº ¸ðµâ¼öÁØ¿¡¼­ ¼±¾ðÇÏ¿´°í PublicÀ̹ǷΠ´Ù¸¥ ¸ðµâ¿¡¼­µµ ÀÌ¿ë°¡´ÉÇÕ´Ï´Ù(frmCompareSheets.frm¸ðµâ¿¡¼­ »ç¿ëÇÑ °ÍÀ» º¸¼ÌÁÒ?)

Dim Buttons(1 To 56) As New ColorButtonClass
Buttons Àº »ö»ó¹öÆ° 56°³¸¦ ÀúÀåÇÏ´Â ¹è¿­ÀÔ´Ï´Ù. ´Ù¸¸ µ¥ÀÌÅÍÇüÀÌ VBA¿¡¼­ Á¦°øÇÏ´Â °ÍÀÌ ¾Æ´Ñ »ç¿ëÀÚÁ¤ÀÇ °³Ã¼(ColorButtonClass)ÀÔ´Ï´Ù. ÀÌ°Í¿¡ ´ëÇؼ­´Â ´ÙÀ½¿¡ ¼³¸íµå¸®ÁÒ.

Sub CompareSheetsStart()
     Initialize
     PaintColor

     frmCompareSheets.Show
End Sub
À§ÀÇ ÇÁ·Î½ÃÁ®´Â ¸Þ´º¿¡ °É¾îµÐ °ÍÀÔ´Ï´Ù. ÀÌ°ÍÀº ÆûÀ» È­¸é¿¡ ¶ç¿ì±â Àü¿¡ ¹Ì¸® ÁغñÇÏ´Â ³»¿ëÀ» ½ÇÇàÇÕ´Ï´Ù. ¹«½¼ Áغñ¸¦ ÇÏ´À³Ä±¸¿ä, ÇöÀçÀÇ ¿öÅ©½ÃÆ®¸ñ·ÏÀ» cboSheet1, cboSheet2¿¡ ÀúÀåÇÏ°í(Sub Initialize()), »ö»ó¹öÆ°¿¡ »öÀ» ÀÔÈ÷´Â µî(Sub PaintColor()) ÁغñÇÒ °ÍÀÌ ¸¹½À´Ï´Ù. 

Sub PaintColor()
     Dim ctl      As Control
     Dim ButtonCount      As Integer

     For Each ctl In frmCompareSheets.Controls
           If ctl.Tag = "ColorButton" Then
                ButtonCount = ButtonCount + 1
                Set Buttons(ButtonCount).ColorButton = ctl
             Buttons(ButtonCount).ColorButton.BackColor = _

                     ActiveWorkbook.Colors(ButtonCount)
           End If
     Next ctl
End Sub

À§ÀÇ ÇÁ·Î½ÃÁ®´Â 56°³ÀÇ »ö»ó¹öÆ°¿¡ »ö»óÀ» ÀÔÈ÷´Â °ÍÀÔ´Ï´Ù. ctlÀº ÄÁÆ®·ÑÀ» ´ë½ÅÇÏ´Â °³Ã¼º¯¼öÀÔ´Ï´Ù. 

frmCompareSheets.Controls´Â ÆûÀÇ °¢Á¾ ÄÁÆ®·ÑÀ» ´ë½ÅÇÒ ¼ö Àִµ¥, cboSheet1, cboSheet2°°Àº ComboBox, Label, CommandButtonµîÀ» ´ë½ÅÇÒ ¼ö ÀÖ½À´Ï´Ù. 

±×·±µ¥ »ö»óÀ» ÀÔÇô¾ß ÇÒ °ÍÀº ¸ðµç ÄÁÆ®·ÑÀÌ ¾Æ´Ï¶ó ¾Æ·¡ ±×¸²°ú °°ÀÌ Èò»ö»óÀڷΠǥ½ÃÇÑ ¹öÆ°µéÀÔ´Ï´Ù. 

ÀÌ ¹öÆ°Àº ¸ðµÎ TagÇÁ·ÎÆÛƼ¿¡ "ColorButton"À̶ó´Â °ªÀ» °¡Áö°í ÀÖ¾î ´Ù¸¥ ÄÁÆ®·Ñ°ú ÄÚµå»óÀ¸·Î ±¸ºÐÀÌ µË´Ï´Ù( If ctl.Tag = "ColorButton" Then ) 

±×¸®°í Ä÷¯¹öÆ°À¸·Î ºÐ·ùµÈ ÄÁÆ®·ÑÀº ButtonsÄ÷º¼Ç °³Ã¼ÀÇ ColorButtonÀ̶ó´Â °³Ã¼·Î ÀúÀåµË´Ï´Ù. ( Set Buttons(ButtonCount).ColorButton = ctl )

¡¡

¶ÇÇÑ ¹öÆ°ÀÇ ¹è°æ»ö»óÀ» ¾Æ·¡¿Í °°ÀÌ ÁöÁ¤ÇÕ´Ï´Ù. ¾Æ·¡ÀÇ ÄÚµå´Â ColorButton¹öÆ°ÀÇ BackColorÇÁ·ÎÆÛƼ¸¦ ÇöÀçÅëÇÕ¹®¼­ÀÇ »ö»ó¹öÆ°»öÀ» ÁöÁ¤ÇÕ´Ï´Ù.
Buttons(ButtonCount).ColorButton.BackColor = _
ActiveWorkbook.Colors(ButtonCount)

Public Sub Initialize()
     Dim cltSht() As String
     Dim sht As Worksheet
     Dim i As Byte

     If ActiveWorkbook.Sheets.Count <= 1 Then
         MsgBox "ºñ±³ÇÒ ´ë»óÀÇ ½ÃÆ®°¡ 2°³ÀÌ»óÀ̾î¾ß ÇÕ´Ï´Ù", vbInformation
           Unload frmCompareSheets
     End If 
     ReDim cltSht(ActiveWorkbook.Sheets.Count - 1)
     For Each sht In ActiveWorkbook.Sheets
          cltSht(i) = sht.Name
          i = i + 1
     Next 
     frmCompareSheets.cboSheet1.Clear
    frmCompareSheets.cboSheet2.Clear
   
For i = LBound(cltSht) To UBound(cltSht)
          frmCompareSheets.cboSheet1.AddItem cltSht(i)
        frmCompareSheets.cboSheet2.AddItem cltSht(i)
   
Next
End Sub

À§ÀÇ ÇÁ·Î½ÃÁ®´Â ÀÛ¾÷´ë»óÀÎ ÇöÀçÅëÇÕ¹®¼­ÀÇ ¿öÅ©½ÃÆ®¸ñ·ÏÀ» ÄÞº¸»óÀÚ cboSheet1,cboSheet2¿¡ ÀúÀåÇÏ´Â ÇÁ·Î½ÃÁ®ÀÔ´Ï´Ù. À̶§ ¸î °¡Áö ÁÖÀÇÇÒ Á¡Àº ÇöÀçÅëÇÕ¹®¼­¿¡ ½ÃÆ®ÀÇ °¹¼ö°¡ ÃÖ¼Ò 2°³ÀÌ»óÀÎÁö¸¦ üũÇÏ°í(If ActiveWorkbook.Sheets.Count <= 1 Then), frmCompareSheets.cboSheet1.Clear¿Í °°ÀÌ ÄÞº¸»óÀÚ¿¡ ±âÁ¸ÀÇ ³»¿ëÀÌ ¾øµµ·Ï Áö¿ì´Â ÀÏÀ» ÇØ¾ß ÇÑ´Ù. ±×¸®°í AddItem¸Þ¼Òµå¸¦ »ç¿ëÇÏ¿© cboSheet1,cboSheet2ÀÇ ¸ñ·ÏÀ» ½ÃÆ®À̸§À¸·Î ä¿ó´Ï´Ù.

Public Sub CompareSheetsMain(sht1 As String, sht2 As String)
     Dim sh1 As Worksheet
     Dim sh2 As Worksheet

     Dim Row As Integer
     Dim Col As Integer

     Dim Cmp1
    Dim Cmp2


     Dim r As Integer
     Dim c As Integer

     Set sh1 = ActiveWorkbook.Sheets(sht1)
     Set sh2 = ActiveWorkbook.Sheets(sht2)

     Row = sh1.Range("A1").End(xlDown).Row
    Col = sh1.Range("A1").End(xlToRight).Column


     For r = 1 To Row
          For c = 1 To Col
               Cmp1 = sh1.Cells(r, c)
               Cmp2 = sh2.Cells(r, c)
               If Cmp1 <> Cmp2 Then
                    sh1.Cells(r, c).Interior.Color = ColorValue
                    If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
               End If
          Next 
     Next

     Set sh1 = Nothing
     Set sh2 = Nothing
End Sub

µÎ °³ÀÇ ¿öÅ©½ÃÆ®¸¦ ºñ±³ÇÏ´Â ºÎºÐÀÔ´Ï´Ù. ºñ±³´ë»óÀ» ÀúÀåÇϵµ ÀÖ´Â Cmp1°ú Cmp2´Â º°´Ù¸¥ ÇüÀ» ÁöÁ¤ÇÏÁö ¾Ê°í VariantÇüÀÌ µÇµµ·Ï Çß½À´Ï´Ù. ºñ±³ÇÏ´Â °ÍÀÌ Á¤¼öÀÏ ¼öµµ ÀÖ°í, ¹®ÀÚ¿­ÀÏ ¼öµµ ÀÖÀ¸¸ç, ȤÀº ³¯Â¥ÇüÀÏ ¼öµµ Àֱ⠶§¹®ÀÔ´Ï´Ù. 
ºñ±³ÇÏ·Á´Â ¹üÀ§´Â ½ÃÆ®Àüü°¡ ¾Æ´Õ´Ï´Ù. »ç¿ëÀÚ°¡ µ¥ÀÌÅ͸¦ ±â·ÏÇØ´ø ºÎºÐ±îÁö¸¸ ºñ±³ÇÏ´Â °ÍÀÌ È¿À²ÀûÀÔ´Ï´Ù. ±×·¡¼­ Çà°ú ¿­ÀÇ ³¡ºÎºÐÀ» ´ÙÀ½°ú °°ÀÌ Ã£½À´Ï´Ù.
Row = sh1.Range("A1").End(xlDown).Row
Col = sh1.Range("A1").End(xlToRight).Column

ÀÌÁ¦ °ªÀÌ ¼­·Î ÀÏÄ¡ÇÏ´Â °æ¿ì ¼±ÅÃÇÑ »ö»óÀ» Ä¥ÇØÁÝ´Ï´Ù.
sh1.Cells(r, c).Interior.Color = ColorValue

¿©±â¿¡¼­ Ŭ·¡½º¸ðµâÀ» »ç¿ëÇÏ´Â ÀÌÀ¯´Â ÇÁ·Î±×·¥ÀÇ ÀåȲÇÔÀ» ÁÙÀÏ ¼ö Àֱ⠶§¹®ÀÔ´Ï´Ù. °¡·É Ŭ·¡½º¸ðµâÀ» »ç¿ëÇÏÁö ¾Ê´Â °æ¿ì¿¡ °¢ 56°³ÀÇ ¹öÆ°¸¶´Ù ¼Ó¼º(BackColor)À» ÁöÁ¤ÇÏ°í À̺¥Æ®¸¦ ¸¸µé¾î¾ß ÇÑ´Ù(¹°·Ð ¼Ó¼ºÁöÁ¤°ú À̺¥Æ®´Â °ÅÀÇ µ¿ÀÏÇÑ ³»¿ëÀÌÁö¸¸)

ColorButtonClass.cls
Public WithEvents ColorButton As msforms.CommandButton

ColorButtonÀ» À¯ÀúÆûÀÇ CommandButton °³Ã¼º¯¼ö·Î ÁöÁ¤ÇÏ¿´´Ù. ÀÌ°ÍÀº ColorButtonClassŬ·¡½ºÀÇ ¸â¹öº¯¼ö°¡ µÈ´Ù. ±×¸®°í WithEvents¸¦ ºÙ¿© CommandButtonÀÌ °¡Áö´Â À̺¥Æ®¸¦ »ó¼ÓÇϵµ·Ï ÇÏ¿´´Ù. ´öºÐ¿¡ ¾Æ·¡ÀÇ À̺¥Æ®ÇÁ·Î½ÃÁ®¿Í °°Àº À̺¥Æ®¸¦ ¸¸µé¼ö ÀְԵȴÙ.

Private Sub ColorButton_MouseDown(ByVal Button As Integer, _
     ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)

     frmCompareSheets.SampleLabel.BackColor = ColorButton.BackColor
     ColorValue = ColorButton.BackColor
End Sub

ColorButton¹öÆ°À» Ŭ¸¯ÇÏ´Â °æ¿ì ¹öÆ°ÀÇ ¹è°æ»ö(backColor) ÇÁ·ÎÆÛƼ¸¦ ColorValueº¯¼ö¿¡ ³Ñ°ÜÁØ´Ù

¿À´ÃÀº ½ÃÆ®¿Í °ü·ÃÇÑ ¸ÅÅ©·Î¸¦ ¸ðµÎ Á¤¸®ÇغýÀ´Ï´Ù. ¾Õ¼­ µÎ °³ÀÇ °ÍÀº ±×´ÙÁö Å©°Ô ¼³¸íÇÒ °ÍÀÌ ¾ø¾î °°ÀÌ ¹­¾î Çعö·È½À´Ï´Ù. ¶Ç ½Ã°£À» ³Ê¹« ³¶ºñÇÑ °Í °°¾Æ¼­¿ä... ´ÙÀ½½Ã°£¿¡´Â ÅëÇÕ¹®¼­¿Í °ü·ÃÇÑ ¸ÅÅ©·Î¸¦ °¡Áö°í ¿À°Ú½À´Ï´Ù. ±×·³....

¡¡

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

¡¡

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