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

[µo°Ý] ¦p¦ó¦bªÑ»ù¹Ï¤U¤èÀ³¥Î VBA ¼W¦C¤@©ÔÅQ¡H

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-5-15 16:55 ½s¿è

¦^´_ 10# c_c_lai
³o¸Ì¥i¥H°Ñ¦Ò
§AªþÀɪº¬O ªí³æªºScrollBar
  1. Sub ±²¶b7()    'ªí³æªºScrollBar ¶·«ü©w¥¨¶°
  2.     Dim S As Object
  3.     Set S = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object
  4.     MsgBox S.Name
  5.     MsgBox S.Value
  6.     MsgBox S.Max
  7. End Sub
  8. Sub ±²¶b7A()    'ªí³æªºScrollBar ¶·«ü©w¥¨¶°
  9.     MsgBox ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Name
  10.     MsgBox ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value
  11.     MsgBox ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Max
  12. End Sub
½Æ»s¥N½X
  1. Private Sub ScrollBar1_Change() '±±¨î¤u¨ã½c ScrollBar1ª«¥óªºµ{§Ç
  2.     MsgBox ScrollBar1.Name
  3.     MsgBox ScrollBar1.Value
  4.     MsgBox ScrollBar1.Max
  5. End Sub
½Æ»s¥N½X
¦p¹Ï


TOP

¥»©«³Ì«á¥Ñ alexliou ©ó 2012-5-15 20:38 ½s¿è

¦^´_ 10# c_c_lai
1.¥ÎVBA code ²£¥Íªº ªí³æ±±¨î¶µ ¥¦ªº¦WºÙ¬O¥ÑExcel ¨M©wªº (ex. "¨÷¶b 7")
¬°¤F´x±±³o­Ó±±¨î¶µ, §A³Ì¦n¬OÀ°¥¦­«·s©R¦W
³o¥i§Q¥Î .Name ÄݩʨӹF¦¨
(ex.  .Name = "MyScrollBar1")


2. ³]©w¦¹¤@±±¨î¶µªºEvent Handler
³o¥i§Q¥Î .OnAction ÄݩʨӹF¦¨
(ex.  .OnAction =  "MySrollBar1_Change"

3. ¼¶¼g MyScrollBar1_Change µ{§Çªºµ{¦¡½X

4. ²Ä¤@¨B¥i¬Ù²¤, ¦ý°µ¤F·|¨Ï¤@­P©Ê(±±¨î¶µªº¦WºÙ©M¥¦ªºEvent Handler ¦WºÙ)´£°ª
§A¥i§â .Name = "MyScrollBar1" ¨º¦æ Mark±¼
µ{¦¡¥i¥H¹B§@
¥u¤£¹L "±²¶b x"ªº¨Æ¥ó³B²z¾¹¬O MyScrollBar1_Change
¨âªÌ¦WºÙ¤£¤@­P

5. ScrollBar ¦³¨âºØ  ¥i¥H¬Oªí³æ±±¨î¶µ©Î¬OActive X ±±¨î¶µ
¼Ó¤WGBKEE¤j¤j¦³Demo
¨âªÌ¥ÎVBA Code«Ø¥ß»P³B²z¤èªk¤£¦P
§A¥Îªº¬Oªí³æ±±¨î¶µ

©ÔÅQ¨Æ¥ó²£¥Í¾¹2.zip (11.89 KB)

Alex

TOP

¦^´_ 11# GBKEE
¦^´_ 12# alexliou
ÁÂÁ¨â¦ìªºÀ°¦£¡A³o¨â¤Ñ§Ú¤W¤F°ê¥~ºô¯¸¦¬´M¸ê®Æ¡A§ä¨ì¤F¤@½gÁÙ¤£¿ù¡A¦p¤U¡G
  1. Sheet1.ScrollBars.Add(624763.5, 4271616, 5095730.25, 189166.5).Select

  2. With Selection
  3.     .Value = 60
  4.     .Min = SMin
  5.     .Max = SMax
  6.     .SmallChange = 1
  7.     .LargeChange = 1
  8.     .LinkedCell = "Sheet1!$O$5"
  9.     .Display3DShading = True
  10.      
  11.     With .ShapeRange
  12.         .Left = 0
  13.         .Width = 680.32
  14.         .Top = 0
  15.         .Height = 15
  16.     End With
  17.      
  18.     .OnAction = "ScrollBarChange"
  19. End With
½Æ»s¥N½X
¦ý¬O§Ú«oÆp¶i¤F¦º­J¦P  .OnAction = "ScrollBarChange"  ¦Ñ¬O§i¶D§ÚµLªk¨Ï¥Î¦¹¥¨¶°¡A
¦Ê«ä¤£¸Ñ¤§¤U¤~¨Ó¨D´©¡Aµ²ªG¬O Alex¥S¥s§Ú¨Ï¥Î .OnAction = "¤u§@ªí1.ScrollBarChange"  ¡A
«Kªï¤b¦Ó¸Ñ¤F¡C ´N®t§O¦b¥²¶·¥[¤W "¤u§@ªí1."¦r¼Ë¤~¯à¥¿±`¹B§@¡CÁÂÁ±z­Ì¡I
¥t¥~¦A½Ð±Ð¡A¦b ThisWorkbook ¤WÀY¤w¸g«Å§i¤F Public «¬ºA¡A²z½×¤W¥¦À³ÄÝ©ó Global¡A
¬°¦ó¦b"¤u§@ªí1"¤º«oµLªk¨ú±o¸Óµ¥ÅܼƤº®e­È©O¡H

TOP

¥»©«³Ì«á¥Ñ alexliou ©ó 2012-5-16 03:09 ½s¿è

¦^´_ 13# c_c_lai
¨º¨ÇªþÄݦbª«¥ó(¦pThisWorkBook, ¤u§@ªí1, .......)¤UªºÅܼƩε{§Ç
§Y¨Ï«Å§i¬°Public , ­n¥h¦s¨ú©Î©I¥s,  »Ý³z¹Lª«¥óªº¶¥¼h  
¦pThisworkbook.VariableA, ¤u§@ªí1.ScrollBarChange,.....

¦ý¦b¼Ò²Õ(Module)¤¤«Å§iªºÅܼƩε{§Ç
¦p«Å§i¬°Public, §Y¨Ï¬O¦bModule¥~¨ú¥Î©Î©I¥s
¤]¤£¥Î¥[¤W¼Ò²Õªº¦WºÙ

¹³§A«e­zªº°ÝÃD
¦pªG§âScrollBarChange µ{§Ç ©ñ¦b¼Ò²Õªí1¤¤
¨Ã§â¥¦«Å§i¬°Public (³o¸Ì¤£«Å§i¤]¦æ  ¦¹±¡§Î¬O¯S¨Ò)
¨º§A¨Ï¥Î   .OnAction = "ScrollBarChange" ¤]·|work

¤u§@ªí¤Wªº±±¨î¶µªºEvent Handler ³q±`·|©ñ¦bªþÄÝ©ó¤u§@ªíªºµ{§Ç¸Ì
¦]¬°¦P¤@Ãþ§Oªº±±¨î¶µ¥i¯à·|¦b¤£¦Pªº¤u§@ªí¤¤¥X²{
¦pªG¯à½T«O¨C­Ó±±¨î¶µºÙ³£¤£¦P¦W
Event Handlers¥þ³£©ñ¦b¼Ò²Õªí¸Ì¥ç¥i
Alex

TOP

¦^´_ 14# alexliou
§Ú·|¦A¹ê¦aºt½m¦h¦¸¥HÁA¸Ñ¥¦­Ì佊¦¹¶¡¤¬°ÊÃöÁpªº¡A ÁÂÁ±z¡I
¨Ì±z©Ò¨¥¡A±z©Ò¦³¸ê®Æ³£ª½±µ¥ÑDDE¶×¤J¨ìAcess¡A¨º½u¤W±z¥u¬Ý¨é°Óªº¹Ïªí¡A
Excel ¤Wªº²Î­p¹Ïªí¥u¬O¨Ó·í§@¾ú¥v¸ê®Æ°Ñ¦Ò¨Ì¾Ú¤F¡A ©Î¬O¨âªÌ¬ÒÀx¦s¡A¦P¨B°Ñ¦Ò¡H

TOP

¦^´_ 15# c_c_lai
§ÚªºAccess ¸ê®Æ®w¤¤©Ò¦sªº³£¬O¾ú¥v¸ê®Æ
¸ê®Æ·½¨Ã«D¨é°ÓªºDDE

¦ý¦b»Ý­n§Y®É¥æ©ö¸ê®Æªºª¬ªp¤U
¨é°ÓªºDDE¸ê®Æ·½«h¬O­Ó¤£¿ùªº¿ï¾Ü
¦Ü¤Ö¸ò¨ä¥L¤@¯ë§K¶Oªº¸ê®Æ·½(¦p¥æ©ö©Òªººô­¶¡^
¥¦ªºÃ­©w©Ê©M§Y®É©Ê¡]§ó·sÀW²v¡^³£¸û¨Î
Alex

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-5-21 10:15 ½s¿è

¦^´_ 16# alexliou
¥Ø«e§Úªº ScrollBar ¬O¥ÑVBA°ÊºA²£¥Íªº¡A¥ç§Y·í¶i¤J(±Ò°Ê) Excelªí³æ¡A¸Ó¨t²Î§Y¦Û°Ê²£¥Í¤@ScrollBar¡A
Sheets("²Î­p¹Ïªí").ScrollBars.Add(Cells(20, 1).Left, Cells(20, 1).Top, 879, Rows(1).Height).Select
·íÂ÷¶}Excel®É¡A¤S¦Û°Ê±N¥¦§R°£±¼¡C
§Úªº´£°Ý¬O¡G
§Ú·Q¯à±N¸Ó°ÊºA²£¥ÍªºScrollBar¥[¤W­I´º©ÎªÌ¬O«e´ºÃC¦â¡A¦p¦¹¤ñ¸ûÅãµÛ¡A§_ªÌ¨Ï¥ÎªÌ·|¬Ý¤£²MScrollBar¦b­þ¸Ì¡A
¦]¬°VBA²£¥ÍªºScrollBarªºÃC¦â¬ODefault­È¡A¤£ª¾¹D¥i¤£¥i¥H³o»ò°µ¡H   ¤S¡A               
.InvertIfNegative = True
.InvertColor = RGB(255, 124, 128)                     
¥u¯àÀ³¥Î¦b¬Wª¬¹Ï¡A§Ú¦b·Q¦pªG¬O xlLine «¬ºA®É¡A·íªí¦C¼Æ­È¬°­t­È®É¡A¦³§_±N¨äÅã¥Ü¤§ÃC¦â§óÅܦ¨¾ï¬õ¦â¡A
¥¿­È®É«h¿ï¾Ü¨ä¥¦ÃC¦â¡A¤£ª¾¯à§_¬ð¯}¡H
ÁÂÁ«ü¾É¡I

TOP

¥»©«³Ì«á¥Ñ alexliou ©ó 2012-5-22 19:39 ½s¿è

¦^´_ 17# c_c_lai
¥ý¦^µª§A¦³Ãö©óScrollBar ÃC¦âªº°ÝÃD

1. «e­±»¡¹L  ¤u§@ªí¥i¨Ï¥Îªº±±¨î¶µ¥i¤À¨âºØ:
ªí³æ±±¨î¶µ©MActiveX±±¨î¶µ
¦b¤£¨Ï¥ÎVBA¨Ó¦Û°Ê«Ø¥ßScrollBar±±¨î¶µªºª¬ªp¤U
¤]´N¬O¦b¤u§@ªíÀô¹Ò¤¤  §Ú­Ì¿ï¾Ü[¶}µo¤H­û] > [´¡¤J]®É
´N¥i¥Hµo²{ EXCEL´£¨Ñ¨âºØ¿ï¾Ü : ªí³æ±±¨î¶µ©MActiveX±±¨î¶µ
¦Ó¥Bªí³æ±±¨î¶µªºÄݩʸû¤Ö  
ActiveX±±¨î¶µªºÄÝ©Ê¿ï¾Ü¸û¦h (·íMouse ¦bActiveX±±¨î¶µ¤W®É«ö¥kÁä, §A·|µo²{¦h¤F¤@­Ó[¤º®e]¼ÐÅÒ)
³o¨âºØ±±¨î¶µ¦b¥ÎVBA «Ø¥ß®É¤Î¾Þ§@®É
¨Ï¥ÎªºCode¤]¤£¤@¼Ë
ªþ¥ó¤¤¥ÎVBA code «Ø¥ß¤F¨âºØ¤£¦PªºScrollBar±±¨î¶µ ·q½Ð°Ñ¦Ò

©ÔÅQ¨Æ¥ó²£¥Í¾¹3.zip (28.09 KB)
2. ªí³æ±±¨î¶µªºScrollBar ¨ÃµLBackColor ©M ForeColorµ¥ÄÝ©Ê
©Ò¥HµLªk§ïÅÜ¥¦ªºÃC¦â
¦pªG§A­nÅýScrollBar ¦³ÃC¦â
´N¥u¯à¨Ï¥ÎActiveX±±¨î¶µScrollBar¤F
¥¦ªºBackColorÄÝ©Ê¥i¥H½Õ¾ãBarªºÃC¦â
¥¦ªºForeClorÄÝ©Ê¥i¥H½Õ¾ãBar¨âºÝ½bÀYªºÃC¦â

3. ·í§A¥ÎVBA Code¦Û°Ê«Ø¥ßActiveX±±¨î¶µ®É
·|¹J¨ì¤@­Ó³Â·Ð   ¬O¥¦¤£¤ä´©¤¤Â_¼Ò¦¡
·|Åý§A°»¿ù«Ü¤£¤è«K
(¦ý§ÚÁÙ¬O¤ñ¸û³ßÅw¥ÎActiveX±±¨î¶µ)

4. ActiveX±±¨î¶µªº¦WºÙÁÙ¦³¤@¨ÇTrickyªº¦a¤è
(§A¥i¥H¦bVBA ªº IDE¤U §Q¥ÎHelp¬dActiveX±±¨î¶µ)
©Ò¥HºÉ¶q¦b³]­p¶¥¬q®É«Ø¸m
¦Ó¤£­n¦bµ{¦¡°õ¦æ¶¥¬q§Q¥ÎVBA Code«Ø¸m¥¦
Alex

TOP

¥»©«³Ì«á¥Ñ alexliou ©ó 2012-5-22 21:34 ½s¿è

¦^´_ 17# c_c_lai

§é½u¹Ï¨Ã¨Sª½±µªº¿ìªk¨Ó³]©w¤£¦PªºÃC¦â
¤]´N¬O»¡  InvertIfNegative »P§é½u¹Ï¦X¥Î®É¨Ã¤£·|µo´§§@¥Î
(½u¬q¬O³s±µ¨âÂI, InvertIfNegtative ¬O°w¹ï"ÂI")

­Y­n³B²z§é½u¹Ï¦b¤£¦Pªº½d³ò(¥i¥H¬OX¶b, ¤]¥i¥H¬O¤£¦PªºY­È)Åã¥Ü¤£¦PªºÃC¦â
¤@¯ë¸Ñªk¬O§â¤@­Ó¸ê®Æ¼Æ¦C®Ú¾Ú½d³ò©î¦¨¤£¦Pªº¼Æ¦C
¨C­Ó§Ç¦C½á¤©¤£¦PªºÃC¦â

PS. ¥H¤W»¡±o«Ü²³æ ¦ýImplement°_¨Ó·|¦³·sªº°ÝÃDµo¥Í
°²³]²Ä¤@ÂIªºY­È¬O 10, ²Ä¤GÂIªºY­È¬O-5
§A·Q­n¦b0¥H¤Wªº½u¬q©M0¥H¤Uªº½u¬q§e²{¤£¦PªºÃC¦â
³o¼Ë´N±o¤H¤u´¡¤J¤@­ÓÂI0 (¤À¬ÉÂI¤S·|§Î¦¨¤@­Ó¸ê®Æ¼Æ¦C)
§A¥i¥H´Â³o­Ó¤è¦V¥h«ä¦Ò
³o¬O¤@­Ó¤£ºâ¤pªº¤uµ{
Alex

TOP

¦^´_ 18# alexliou
Alex¥S¡A
ÁÂÁ±z¡A§Ú¸ÕµÛ¦bµ{¦¡°õ¦æ¶¥¬q§Q¥ÎVBA Code«Ø¸m¥¦¡A¥¦¤£¶È¥Rº¡µÛ Tricky, ¦Ó¥BÁÙ Difficult to manage,
©Ò¥H§ÚÁÙ¬O°µ­Ó¨ÄÄ_Ä_¨Ï¥Î­ì¥»ªº ScrollBar ¤ñ¸û¿Ë©M©Ê¤]¤£·|§@©Ç¡AÁöµM¨S¦³ BackColor & ForeColor¡C
Anyway ÁÙ¬O­n¦V±z»¡ÁnÁÂÁÂÅo¡I

TOP

        ÀR«ä¦Û¦b : ¯u¥¿ªº·R¤ß¡A¬O·ÓÅU¦n¦Û¤vªº³oÁû¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD