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

[µo°Ý] ¦p¦óÅý¤U©Ô¿ï³æ¿ï¨ú«á¦Û°Ê¸õ¨ìÀx¦s®æ¬Û¹ïÀ³³B?

¦^´_ 38# c_c_lai

§Ú¤U¸ü§A´£¨ÑªºÀɮסASheet2!AÄæ ¬O¨S¦³³]©w ¸ê®ÆÅçÃÒ²M³æ¡A
©Ò¥H Worksheet_SelectionChange §PÂ_©Ò¿ïÀx¦s®æ¨S¦³³]©w ¸ê®ÆÅçÃÒ²M³æ¡A´N¨S¦³§@¥Î

­«³] ¸ê®ÆÅçÃÒ²M³æ Àx¦s¡A¦b¥´¶}¤´·|¦s¦b¡C

µ{¦¡¤¤¦³¤U­±³o¤@¬q¡A¦ý³£¨S¦³³Q©I¥s¡A§Ú¤]¨S¦³¥J²Ó¬ã¨s¡A©Ò¥H¤@ª½¥H¬°VBAÀ³¸Ó·|±N  ¸ê®ÆÅçÃÒ²M³æ ¦Û°Ê³]¦n¡C
Sub CellValidation()      '  stillfish00 ´£¨Ñ
    With Sheets("Sheet2").[A2:A25].Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=Sheet1!$A$3:$A$20"
    End With
End Sub

¥H³o¨Ò¤l¥i¯à¤@¶}©l¬O¨Ï¥Î  ¸ê®ÆÅçÃÒ²M³æ ¡A«á¨Ó§ï¥ÎCOMBOBOX¨Ó´À¥N ¸ê®ÆÅçÃÒ²M³æ¡A
Åý§Ú¤@ª½·d¤£²M·¡(VBA¥\¤O¤Ó®z)¡A¬JµM¨Ï¥ÎCOMBOBOX¨Ó·í ÅçÃÒ²M³æ¡A¬°¦ó¤S­n³]©wÀx¦s®æªº  ¸ê®ÆÅçÃÒ²M³æ¡A
·Pı2­Ó¬Û¦P¥\¯à¤¬Å|¦b¤@°_¡C
Á`¤§ÁÂÁ§A¤@ª½­@¤ß¦^ÂЧڪº°ÝÃD¡A·PÁÂ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¬Ý¤F ML089, c_c_lai,stillfish00¤T¦ìªº°Q½×,ÃöÁä¬Oª©Excel¶}±Ò·sª©Àɮתº±±¨î¶µ¬OµLªk¨Ï¥Î.
§Ú¬O2003ª©´N±`¹J³oª¬ªp,¸Ñ¨M¤è¦¡
±N·sª©Àɮתº¸ê®Æ½Æ»s(¤å¦r,®æ¦¡,¤£§t±±¨î¶µ)¦b¤@·s¼Wª©¬¡­¶Ã¯,(ª©±±¨î¶µ¦A¤@¤@·s¼W¤W¥h,µ{¦¡½X½Æ»s¨ìVBA¸Ì)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 41# ML089
§O«È®ð¡A¤j®a¤¬¬Û¾Ç²ß¡C
¬Ý¤F§A»P stillfish00 ¤j¤jªº°T®§¡A¥çÅý§Ú±q¤¤¾Ç²ß¨ì«Ü¦h§Ú¨S·Q¨ìªº¬ðµoª¬ªp¡A
¦]§Ú¤£¬O­ì³ÐªÌ (av8d ¤j¤j) ©Ò¥H¥u³æ¯Âªº®M¥Î²{¦¨ªºµ{¦¡ (¦]­ì¥»¥u¬O¨ó§U av8d ¸Ñ¨M¥Lªº´£°Ý)¡A
³º¨S·Q¨ì·|ªu¥Í¥X ActiveCell.Validation.Formula1 ªº²§ª¬¡C­è­è±q¥~ÀY¦^¨ì®a¬Ý¤F§A­Ìªº¹ï½Í¤º®e¡A
¤£¸T¥ç·Q¨ì¬°¦ó¤£¥h¨Ï¥Î¥Ñ stillfish00 ¤j¤j´£¨Ñªº CellValidation() ¡A¦b StrVdFml §PÂ_¤§«e¥[¤J¥¦¡A
³o»ò¤@¨ÓºÞ¥¦­ì¥»ªº ActiveCell.Validation.Formula1 ¦³§_¸ê®Æ¡A©ó CellValidation() °õ¦æ«á¡A
¥¦«K¦Û°Ê§â A2:A25 ªº¸ê®ÆÅçÃÒ¸ê®Æ¤@¤@¼g¤J "=¤u§@ªí1!$A$3:$A$20" ³sµ²°Ñ³y¦r¦ê¡C
¦p¦¹¤@¨Ó±µ¤U¨Óªº§@·~«Kªï¤b¦Ó¸Ñ¤F¡C

TOP

¦^´_ 41# ML089
A2:A25 ªºÄæ¦ì»P ComboBox1:ComboBox ¬O¬Û¤¬¨Ì¦sªº¡C
Äæ¦ì¸ê®Æªº¸ê®ÆÅçÃÒ¡A¤@¯ëÀ³¥Î©ó´ú¸Õ¸ê®Æ¤ÎÅçÃÒ¡A¥H½T»{¹B§@¥¿½TµL»~¡C
¦¹³B¤§¹B¥Î¬O¨Ï¥Î "¨Æ¥ý³]©wªº²M³æ½d³ò ("=Sheet1!$A$3:$A$20")" °µ¬°
¸ê®ÆÅçÃÒªº¤º¦s­È¡A´£¨Ñµ¹«áÄò³B²z¤§ ComboBox1 ªº LinkedCell¡B¥H¤Î
ListFillRange ¸ê°T¡C LinkedCell¹ïÀ³ªº¬O¨Ï¥ÎªÌ¥Ø«eÂI¿ïªº A Äæ¦ì¡A
Ä´¦p¥Ø«e¬O¦ì©ó A4 Äæ¦ì¡A«h¦¹³B LinkedCell ¹ïÀ³ªº¤º®e­È¬° $A$4¡A
¦P®É ListFillRange ¹ïÀ³ªº¤º®e­È«h¬° Sheet1!$A$3:$A$20¡C
³o®É¨Ï¥ÎªÌ¦pÂI¿ï "¤U©Ô²Å¸¹"¡AComboBox1 «hÀH§Y®i¶} ListFillRange
¹ïÀ³ªºÄæ­È (A2:A25 ªºÄæ­È¤º®e) ²M³æ¨Ñ¨Ï¥ÎªÌÂI¿ï¡C
ÂI¿ï§¹¦¨«áÀH§Y¦Û°Ê±NÂI¿ïÄæ­È¼g¤J¨ì LinkedCell ¹ïÀ³ªºÄæ¦ì¤º¡C
Á`µ²¡BÀ³¥Î CellValidation() ±N "=Sheet1!$A$3:$A$20" ¦P®É¼g¤J¦Ü
A2:A25 ªº .Validation.Formula1¡AµM«á¦A§Q¥Î¦¹¸ê®ÆÅçÃÒ¤@¤@±a¤J¨ì
¨C¦¸°õ¦æªº ComboBox1 ¤¤¡C
(¦p¦¹¡BA2:A25 ¥H¤Î ComboBox1 ³£¥i¤£¥Î¨Æ¥ý¥h°µ¥ô¦ó³]©w­Èªº°Ê§@¡A
¦Ó¥þ³¡¥æ¥Ñ CellValidation() ¥h³B²z¡B¤Îµ¹­È¡C)
¦p¦¹»¡©ú¬O§_¹ï§A¦³©Ò§U¯q¡H

TOP

¦^´_ 44# c_c_lai

ÁÂÁ»¡©ú¡A¤w¸g¤F¸Ñ°ÝÃD©Ò¦b
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD