ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] «ç»ò¦bUSERFORM¤¤¥[¤J¥i¿z¿ï½Æ¿ï¤§¾ðª¬¹Ï

¦A¥t¥~
§â EXCELªí³æ³B²z¤¶­± ¸Ì­±ªº³o¬q ¥[¤JÂŦr³¡¤À

Private Sub CommandButton1_Click()
    If Application.FindFile = False Then
        MsgBox "±z¨S¦³¶}±Ò¥ÀÀÉ"
    End If
Dim lRrow&
    lRrow = 3
vD.RemoveAll
While Cells(lRow, 5) <> ""
      If Not vD.Exists(CStr(Cells(lRow, 5))) Then
        EXCELªí³æ³B²z¤¶­±.ListBox1.AddItem CStr(Cells(lRow, 5))
        vD(CStr(Cells(lRow, 5))) = lRow
      End If
      lRow = lRow + 1
End With
End Sub

TOP

¦^´_ 50# c_c_lai

§Ú¯uªº...¤£ª¾¹D­n«ç»ò·PÁ¨â¦ìªº¤j®¦¤j¼w
°®¯Ü½Ð¦Y¶º¦n¤F!!

¦ý¬O­«ÂI....
§Ú....
¤S¿ù»~¤F ³o¦¸¬O¥þ·sªº¿ù»~1004
¥X²{¦b§Ú¿é¯Ã¤ÀªRªº¤º³¡
¤§«e¤ÀªR³£¨S¦³°ÝÃD «ç»ò²{¦b«e­±°ÝÃD¸Ñ¨M¤§«á·|¶]¥X³o­Ó©O?
¦p¹Ï


¤W¤Ñ¯uªºµ¹§Ú¦n¦hªº¦ÒÅçªü....

TOP

¦^´_ 52# chaoyiho
³o¬O§A¨ÃµL¥ô¦ó¿ï¶µ¡A±µµÛª½±µÂI¿ï "°õ¦æ¤ÀªR" ©Ò²£¥Íªº¡A
¬O»ò¡H

TOP

¥»©«³Ì«á¥Ñ jackyq ©ó 2016-4-28 10:26 ½s¿è

¦^´_ 52# chaoyiho


#51 ¼Ó §Ú¬Ý¿ù¤F  ( While  ¬Ý¦¨ With )
§ó¥¿¤@¤U
¤j¤j¯uªº¬O¨Ó¦Ò¤j®a²´¤Oªº ( §ä¤£¦Pªº¹CÀ¸ ?? )

Private Sub CommandButton1_Click()
    If Application.FindFile = False Then
        MsgBox "±z¨S¦³¶}±Ò¥ÀÀÉ"
    End If
Dim lrow&   ' c_c_lai ¤j¤j§ä¥X
    lrow = 3
vD.RemoveAll
While Cells(lrow, 5) <> ""
      If Not vD.Exists(CStr(Cells(lrow, 5))) Then
        EXCELªí³æ³B²z¤¶­±.ListBox1.AddItem CStr(Cells(lrow, 5))
        vD(CStr(Cells(lrow, 5))) = lrow
      End If
      lrow = lrow + 1
Wend
End Sub

TOP

¦^´_ 52# chaoyiho
³o¬O§A¨ÃµL¥ô¦ó¿ï¶µ¡A±µµÛª½±µÂI¿ï "°õ¦æ¤ÀªR" ©Ò²£¥Íªº¡A
¬O»ò¡H
§Ú¤w E-Mail ¶Ç ªí³æ³B²z¨t²Î3.2µ¥«Ý­×§ï.xlsm¡A ¥H¤Î­ì¥ýªº
ÀÉ®×.rar ¤F¡A§A°õ¦æ¬Ý¬Ý¡C

TOP

¦^´_ 52# chaoyiho
ªí³æ³B²z¨t²Î3.2µ¥«Ý­×§ï.rar (633.3 KB)
³o°¦¬O§AÀ³¥Î luhpro ¤j¤jªº³B²z¼Ò¦¡¡G
ÀÉ®×.rar (305.26 KB)

TOP

¦^´_ 54# jackyq

§Úµo²{§Ú±`±`·|³o¼Ë
½Ð°Ý«e½ú Ãö©ó³o¼Ëªº°ÝÃD ¦³¤°»ò²ßºD©Î°Ê§@¥i¥H¹w¨¾©Î¾i¦¨¶Ü?

¦Ó¥B±z·|µo²{
§Úªºµ{¦¡½X¤º®e³£¶ë¦bUSERFORM¤¤
«Ü¦h­«½ÆªºÂØ¥y>>¦ü¥G¦³¤è¦¡¥i¥H¦X¨Ö¦¨¤@­Ó¤@°_«Å§i(Public?)

½Ð±Ð³o¸Ó«ç»ò³B²z©O?

TOP

¦^´_ 55# c_c_lai

«e½ú±zªºÀɮקڤw¸g´ú¸Õ¹L¤F ªº½T¥i¥H¥Î!
¸g¹L§Ú¤@¦æ¤@¦æªº¤ñ¹ï»yªk«á
§Úµo²{§Úªº¿ù¦rº|¦rÁÙ¯uªº«Ü¦h...

«e½úªº³B²z¤è¦¡¬O³o­ÓÅÞ¿è
1.¶}±ÒÂÂÀÉ
2.¿z¿ï²î²í
3.«ö¤U¤ÀªR
4.µ²ªG

¦ý¬OÃö©ó2.»P3.
¤£ª¾¹D¦³¨S¦³¥i¥H­«½Æ°Ê§@ªº¤è¦¡©O?
¦]¬°³o¼Ë¤lªº¤è¦¡¦ü¥G¬O¤@¦¸©Êªº
¤ÀªR¹L«á´NµLªk¦A°µ¿z¿ï ¥²¶·­n­«·s¦A¤@¦¸¤~¦³¿ìªk³B²z¤F(?)
©Î¬O§Ú¤S°µ¿ù¤F©O?
½Ð«e½ú½ç±Ð!

TOP

¦^´_  c_c_lai

«e½ú±zªºÀɮקڤw¸g´ú¸Õ¹L¤F ªº½T¥i¥H¥Î!
¸g¹L§Ú¤@¦æ¤@¦æªº¤ñ¹ï»yªk«á
§Úµo²{§Úªº¿ù¦r ...
chaoyiho µoªí©ó 2016-4-28 11:19

¨ä¹ê³o«D§A¤§°ÝÃD¡AActiveWorkbook.PivotCaches ¦b°µ¹L¤@¦¸¤§«á¡A
¦ü¥GµLªk¦A¦¸³B²z¡A´Nºâ¬O§â©Ò¦³¤u§@ªí³æ§R°£¥u³Ñ¤U "FORM_REPORT"
¤@¼Ë·|¥X²{

°õ¦æ¶¥¬q¿ù»~ '5'
µ{§Ç©I¥s©Î¤Þ¼Æ¤£¥¿½T

»¡¯u®æªº¡A§Ú¨Ã¤£±E±x Excel ªº PivotTable ³B­ù¡A§A¥i¯à­n½Ð±Ð
ª©¤Wªº¦U¦ì¤j¤j¤F¡C

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-4-29 17:14 ½s¿è

¦^´_ 58# chaoyiho
°w¹ï #58
¤£ª¾¹D¦³¨S¦³¥i¥H­«½Æ°Ê§@ªº¤è¦¡©O?
¦]¬°³o¼Ë¤lªº¤è¦¡¦ü¥G¬O¤@¦¸©Êªº
¤ÀªR¹L«á´NµLªk¦A°µ¿z¿ï ¥²¶·­n­«·s¦A¤@¦¸¤~¦³¿ìªk³B²z¤F(?)
------------------------------------------------------------------------
¬°¸Ñ¨M¶È¯à°õ¦æ¤@¦¸ªº§xÂZ°ÝÃD¡A­×¥¿¦¨¯à°÷­«½Æ¡u¥i¦h¦¸°õ¦æ¡v¡A§Y
¡y°õ¦æ¤ÀªR¡z¤§¡u¼Ï¯Ã¤ÀªRªí¡v¡A­×§ï¤º®e¦p¤U (¶È¦C¥Ü¥X­×§ï³¡¤À)¡G
¤@¡B
  1. Private Sub CommandButton3_Click()
  2.     If TestBookOpen("FORM_REPORT.xlsx") = "" Then MsgBox "ÀÉ®×¥¼¶}±Ò": Exit Sub '¸õ¥Xµ{§Ç

  3.     §R°£¤ÀªRµ²ªGªí                '  ¼W¥[§R°£¤ÀªR¤u§@ªí³æ¡A¥H«K¦A¦¸¡y°õ¦æ¤ÀªR¡z

  4.     Range("A1:M50192").Select
  5.     Range("A2").Activate
  6.     Sheets.Add
  7.     '  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  8.         "FORM_REPORT!R2C1:R50192C13", Version:=xlPivotTableVersion14). _
  9.         CreatePivotTable TableDestination:="¤u§@ªí1!R3C1", TableName:="¼Ï¯Ã¤ÀªRªí1", _
  10.         DefaultVersion:=xlPivotTableVersion14
  11.     '  2016/04/29  ­×¥¿¶È¯à°õ¦æ¤@¦¸ªº§xÂZ°ÝÃD
  12.     '  ­ì¥»¡y°õ¦æ¤ÀªR¡z¤§¡u¼Ï¯Ã¤ÀªRªí¡v¶È¯à°õ¦æ¤@¦¸¡A¬°¸Ñ¨M¦¹¤@§xÂZ°ÝÃD¡A¬G±N TableDestination
  13.     '  ¶Ç¤J­È "¤u§@ªí1!R3C1" ¤©¥H­×¥¿¬° ActiveSheet.Name & "!R3C1"¡A¦p¦¹«K¥i¦h¦¸°õ¦æ¤F¡C
  14.     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  15.         "FORM_REPORT!R2C1:R50192C13", Version:=xlPivotTableVersion14). _
  16.         CreatePivotTable TableDestination:=ActiveSheet.Name & "!R3C1", TableName:="¼Ï¯Ã¤ÀªRªí1", _
  17.         DefaultVersion:=xlPivotTableVersion14
  18.     '  Sheets("¤u§@ªí1").Select          '  ­ì¬°¡u¤u§@ªí1¡v¡A¦ý°õ¦æ²Ä¤G¦¸®É¬°¡u¤u§@ªí10¡v, «h·|²£¥Í¿ù»~°T®§
  19.     Sheets(ActiveSheet.Name).Select      '  ¬°°t¦X¡u¥i¦h¦¸°õ¦æ¡v¡A±Nªí³æ¦WºÙ§ï¥H°ÊºAÀH¾÷³B²z (2016/04/29)
  20.     Cells(3, 1).Select
½Æ»s¥N½X
¤G¡B
  1.     '  2016/04/26  ¼W¥[»P "EXCELªí³æ³B²z¤¶­±.ListBox" ªº³sµ²¡C
  2.     '  ©Ò¦³¼W¦C¤§ Sav¡BJ.C.W¡BP.C.O¡BPmax¡BStuffing Box¡BF.O.¡B
  3.     '  Exh¡BExh ¤u§@ªí³æ VSL §¡»P ¡uÁ`ªí¡v¹ïÀ³¤§ VSL ¦P¨B¡F
  4.     '  «h±N "¥H¤U With ~ End With" ·s¼Wªº¡uµ{¦¡½X¡v¤º®e²¾¦Ü¦¹³B¡C
  5.     Dim cts As Integer
  6.    
  7.     With ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").PivotFields(" VSL")
  8.         For cts = 0 To EXCELªí³æ³B²z¤¶­±.ListBox1.ListCount - 1
  9.             '  .PivotItems(EXCELªí³æ³B²z¤¶­±.ListBox1.List(cts)).Visible = IIf(EXCELªí³æ³B²z¤¶­±.ListBox1.Selected(cts), True, False)
  10.             '  §ó²¼ä¡B§ã­nªº Assignment ªºªí¥Ü¦¡¡C
  11.             .PivotItems(EXCELªí³æ³B²z¤¶­±.ListBox1.List(cts)).Visible = EXCELªí³æ³B²z¤¶­±.ListBox1.Selected(cts)
  12.         Next cts
  13.     End With

  14.     '  ¬°°t¦X¡u¥i¦h¦¸°õ¦æ¡v¡A¤©¥H­×¥¿§ï¥H°ÊºA¹ïÀ³·s²£¥Íªí³æ¦WºÙ¡A¥H§K²£¥Í¿ù»~  (2016/04/29)
  15.     '  Sheets("¤u§@ªí1").Select
  16.     Sheets(ActiveSheet.Name).Select
  17.     '  Sheets("¤u§@ªí1").Name = "Liner"
  18.     Sheets(ActiveSheet.Name).Name = "Liner"
  19.     '  Sheets("¤u§@ªí1").Copy Before:=Sheets(1)
  20.     Sheets(ActiveSheet.Name).Copy Before:=Sheets(1)
  21.     '  Sheets("¤u§@ªí1 (2)").Select
  22.     Sheets(ActiveSheet.Name).Select
  23.     '  Sheets("¤u§@ªí1 (2)").Name = "Exh"
  24.     Sheets(ActiveSheet.Name).Name = "Exh"
  25.     '  Sheets("¤u§@ªí1 (2)").Copy Before:=Sheets(1)
  26.     Sheets(ActiveSheet.Name).Copy Before:=Sheets(1)
  27.     '  Sheets("¤u§@ªí1 (3)").Select
  28.     Sheets(ActiveSheet.Name).Select
  29.     '  Sheets("¤u§@ªí1 (3)").Name = "F.O Inlet"
  30.     Sheets(ActiveSheet.Name).Name = "F.O Inlet"
  31.     '  Sheets("¤u§@ªí1 (3)").Copy Before:=Sheets(1)
  32.     Sheets(ActiveSheet.Name).Copy Before:=Sheets(1)
  33.     '  Sheets("¤u§@ªí1 (4)").Select
  34.     Sheets(ActiveSheet.Name).Select
  35.     '  Sheets("¤u§@ªí1 (4)").Name = "Stuffing Box"
  36.     Sheets(ActiveSheet.Name).Name = "Stuffing Box"
  37.     '  Sheets("¤u§@ªí1 (4)").Copy Before:=Sheets(1)
  38.     Sheets(ActiveSheet.Name).Copy Before:=Sheets(1)
  39.     '  Sheets("¤u§@ªí1 (5)").Select
  40.     Sheets(ActiveSheet.Name).Select
  41.     '  Sheets("¤u§@ªí1 (5)").Name = "Pmax"
  42.     Sheets(ActiveSheet.Name).Name = "Pmax"
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD