请问excel中如何利用VBA,实现利用选中复选框(下拉框?)的内容,然后在指定单元格显示选中的内容? EXCEL如何在复选框选中之后,在某个单元格显示文字
\u5982\u4f55\u5728Excel\u4e2d\u7528VBA\u5b9e\u73b0\u5728\u4e0b\u62c9\u5217\u8868\u4e2d\u9009\u4e2d\u4e00\u4e2a\u503c\uff0c\u7136\u540e\u4e3a\u5176\u4ed6\u5355\u5143\u683c\u8d4b\u503c\u7684\u64cd\u4f5cPrivate Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$G$13" Then Select Case Target.Value Case "\u4e0a\u6d77": [G38] = 1: [G51] = 1: [G55] = 1 Case "\u5317\u4eac": [G38] = 2: [G51] = 2: [G55] = 2 Case "\u5929\u6d25": [G38] = 3: [G51] = 3: [G55] = 3 Case "\u6b66\u6c49": [G38] = 27: [G51] = 27: [G55] = 27 End Select End IfEnd Sub\u8be6\u89c1\u9644\u4ef6\uff0c\u4fee\u6539\u8d77\u6765\u5e94\u8be5\u4e0d\u590d\u6742\u5427\uff01
\u53ef\u4ee5\u501f\u52a9\u8f85\u52a9\u5217\u6765\u8fbe\u5230\u8fd9\u4e2a\u6548\u679c\u7684\uff0c\u4e00\u822c\u4f60\u7684\u590d\u9009\u6846\u90fd\u8981\u5360\u4e00\u5b9a\u7684\u4f4d\u7f6e\uff0c\u6bd4\u5982\u590d\u9009\u6846\u5728A1\uff0c\u53f3\u952e\u70b9\u590d\u9009\u6846\u9009\u201c\u8bbe\u7f6e\u63a7\u4ef6\u683c\u5f0f\u201d\u540e\uff0c\u5728\u5f39\u51fa\u7684\u5bf9\u8bdd\u6846\u9009\u201c\u63a7\u5236\u201d\u6807\u7b7e\uff0c\u5728\u201c\u5355\u5143\u683c\u94fe\u63a5\u201d\u4e2d\u9009A1\uff0c\u786e\u5b9a\u540e\u53ea\u8981\u52fe\u9009\u590d\u9009\u6846\uff0cA1\u5c31\u4f1a\u51fa\u73b0\u201cTRUE\u201d,\u53d6\u6d88\u52fe\u9009\u5c31\u4f1a\u51fa\u73b0\u201cFALSE\u201d\u5b57\u6837\u7684(\u5373\u201c\u771f\u201d\u548c\u201c\u5047\u201d\u5224\u65ad)\u3002
\u5982\u679c\u4f60\u5e0c\u671b\u5728B1\u663e\u793a\u6587\u5b57\uff0c\u6bd4\u5982\u4e0d\u9009\u590d\u9009\u6846\u5c31\u7a7a\u767d\uff0c\u5982\u679c\u9009\u4e2d\u590d\u9009\u6846\u5c31\u5728B1\u663e\u793a\u6587\u5b57\u201c\u9009\u4e2d\u4e86\u201d\uff0c\u53ef\u5728B1\u8f93\u5165\u516c\u5f0f\uff1a
=IF(A1=TRUE,"\u9009\u4e2d\u4e86","")
\u8fd9\u6837\u5c31\u884c\u4e86\uff0c\u6700\u540e\uff0c\u56e0\u4e3aA1\u662f\u88ab\u590d\u9009\u6846\u906e\u4f4f\u7684\uff0c\u53ea\u8981\u5c06A1\u7684\u5b57\u4f53\u989c\u8272\u9009\u4e3a\u767d\u8272\uff0c\u5c31\u5b8c\u5168\u4e0d\u5f71\u54cd\u5916\u89c2\u6548\u679c\u4e86\u3002
\u8865\u5145\uff1a\u6211\u7528\u7684\u4e5f\u662f2003\u7248\uff0c\u591a\u53f0\u7535\u8111\u90fd\u662f\uff0c\u4e5f\u662f\u6709\u7684\uff0c\u4f60\u7ec6\u5fc3\u770b\u770b\u3002\u662f\u5c06\u5149\u6807\u505c\u5728\u590d\u9009\u6846\u4e0a\uff0c\u5149\u6807\u53d8\u6210\u624b\u6307\u56fe\u6807\uff0c\u8fd9\u65f6\u70b9\u53f3\u952e\uff0c\u5e94\u8be5\u89c1\u5230\u5f39\u51fa\u7684\u6700\u540e\u9009\u9879\u662f\u201c\u8bbe\u7f6e\u63a7\u4ef6\u683c\u5f0f\u201d\uff0c\u70b9\u51fb\u8fdb\u5165\u8bbe\u7f6e\u6846\u540e\uff0c\u53f3\u4e0a\u89d2\u5c31\u662f\u201c\u63a7\u5236\u201d\u6807\u7b7e\u4e86\u3002
\u518d\u8865\u5145\uff1a\u6709\u53ef\u80fd\u4f60\u7684EXCEL\u5b89\u88c5\u65f6\u6709\u4e9b\u7ec4\u4ef6\u6ca1\u6709\u5b89\u88c5\u4e86\uff0c\u518d\u88c5\u4e00\u4e0b\u5427\uff0c\u6216\u8005\u5230\u5176\u4ed6\u7535\u8111\u91cc\u5f04\u597d\u4e86\u624d\u5728\u81ea\u5df1\u7684\u7535\u8111\u91cc\u7528\u3002
右键设置控件格式。控制里,看到源数据区域,和单元格链接了么?
分别指定即可。
由于返回的是序号,用vlookup函数再查一下源数据区域表即可。
绛旓細1銆佸湪浣跨敤vba涓紝璋冪敤瀹鐨鏃跺欎細鍑虹幇鏃犳硶杩愯瀹忕殑鎯呭喌锛屽涓嬪浘鎵绀猴細2銆鍦‥XCEL琛ㄦ牸鐨勭晫闈笅锛屽涓嬪浘鎵绀猴紝鎵惧埌鈥滶XCEL閫夐」鈥滃苟鐐瑰嚮銆3銆佸脊鍑篍XCEL閫夐」瀵硅瘽妗嗭紝濡備笅鍥炬墍绀猴細4銆佸湪EXCEL閫夐」鐨勭晫闈笅锛岀偣鍑烩滀俊浠讳腑蹇冣濆涓嬪浘锛5銆佸湪淇′换涓績鐨勭獥浣撲笅锛屽崟鍑烩濅俊浠讳腑蹇冭缃滐紝濡備笅鍥炬墍绀猴細6銆佸脊鍑轰俊浠...
绛旓細瑕佸湪Excel涓繍琛屼竴涓猇B浠g爜锛棣栧厛闇瑕佹墦寮Excel骞堕夋嫨鈥滃紑鍙戜汉鍛樷濋夐」鍗★紝鐒跺悗鐐瑰嚮鈥淰isual Basic鈥濇寜閽墦寮VB缂栬緫鍣銆傚湪VB缂栬緫鍣ㄤ腑锛屽彲浠ユ柊寤轰竴涓ā鍧楋紝骞跺湪鍏朵腑缂栧啓闇瑕佽繍琛岀殑VB浠g爜銆傚畬鎴愪唬鐮佸悗锛屽彲浠ラ氳繃鐐瑰嚮鈥滆繍琛屸濇寜閽垨鎸変笅F5閿潵鎵ц浠g爜銆傚彟澶栵紝涔熷彲浠ュ皢浠g爜涓庣壒瀹氱殑浜嬩欢鍏宠仈锛屼緥濡傛寜閽偣鍑讳簨浠舵垨...
绛旓細鐢╲ba瀹氫箟鍑芥暟姝ラ1锛氫緥锛氫笅闈㈣〃鏍间腑闇瑕佽绠椾竴浜涗笁瑙掑舰鐨闈㈢Н 鐢╲ba瀹氫箟鍑芥暟姝ラ2锛欱鍒楁槸搴曡竟闀匡紝C鍒楁槸楂橈紝瑕佹眰鍦―鍒楅氳繃鍏紡璁$畻涓夎褰㈤潰绉excel鍒╃敤vba瀹氫箟鍑芥暟鐨勬暀绋 鍥1 (閫氬父鎴戜滑浼氬湪D3鍗曞厓鏍肩敤鍏紡 =B3*C3/2 鏉ヨ绠楋紝鐒跺悗鎶婅繖涓叕寮忓悜D鍒椾笅鏂规嫋鍔ㄥ鍒讹紝寰楀埌鍏朵粬鍏紡銆傝繖鍙槸涓涓畝鍗曠殑渚嬪瓙...
绛旓細1.鐐瑰嚮榧犳爣鍙抽敭锛岄夋嫨鈥滄柊寤衡濋夐」锛岀劧鍚庣偣鍑烩渕ircosoft office Excel 2007鈥濋夐」鏂板缓涓涓狤xcel鏂囦欢銆2.鍒朵綔闇瑕侀殣钘忕殑鐩稿叧琛ㄦ牸銆3.閫夋嫨鐣岄潰涓婃柟鐨勨滃紑鍙戝伐鍏封濋夐」锛岀劧鍚庣偣鍑烩滃畯鈥濋夐」銆4.鍐欐槑VBA浠g爜鍚嶇О锛岀劧鍚庣偣鍑烩滃垱寤衡濇寜閽5.鍐欏叆鎸囧畾鐨VAB浠g爜銆備綔鑰呰繖閲岄渶瑕侀殣钘忚〃鏍间腑鐨2-4鍜9-11琛屻6.鐐瑰嚮...
绛旓細End Sub 褰撴寜F5閿紝灏辨姤锛氳繍琛屾椂閿欒1004銆傜粡鑰佸笀鎸囩偣锛屽崟鍑绘枃浠垛曗曢夐」鈥曗曚俊浠讳腑蹇冣曗曟墦寮鈥滀俊浠讳腑蹇冣濆璇濇锛屽畯璁剧疆鈥曗曞嬀閫夆滀俊浠诲VBA宸ョ▼瀵硅薄妯″瀷鐨璁块棶鈥濄傚啀娆¤繍琛岋紝灏变笉浼氬嚭鐜拌繍琛屾椂閿欒1004鎻愮ず銆傚悗鏉ュ湪寰蒋瀹樻柟涔熺湅鍒颁簡姝ゆ秷鎭傚啀鍦ㄦ悳绱㈠紩鎿庢煡璇紝鎵嶇煡閬撹繍琛屾椂閿欒1004涓嶅彧鏄湪涓婇潰杩欑鎯呭喌...
绛旓細鍦‥xcel涓锛屾墦寮VBA缂栬緫鍣紝閫夋嫨宸ヤ綔绨匡紝鍦ㄥ乏渚ч」鐩爲涓夋嫨锛岀劧鍚庨夋嫨鈥滆繍琛屸->鈥滆繍琛屽瓙/鐢ㄦ埛琛ㄥ崟鈥濇垨鎸塅5閿墽琛屽畯锛屽疄鐜拌嚜鍔ㄥ~鍏呫俈BA绋嬪簭鐨勫吀鍨嬪簲鐢ㄥ満鏅寘鎷暟鎹鐞嗕笌鍒嗘瀽銆佽嚜瀹氫箟鍑芥暟鍒涘缓鍜屾暟鎹獙璇併備緥濡傦紝閫氳繃VBA绋嬪簭璁$畻Excel琛ㄦ牸涓墍鏈夋暟鎹诲拰锛屽垱寤鸿绠楀钩鏂圭殑鑷畾涔夊嚱鏁帮紝浠ュ強楠岃瘉杈撳叆鏃ユ湡鏄惁鍚堟硶...
绛旓細Excel涓璙BA鍑芥暟寮曠敤鍗曞厓鏍煎瓧绗︿覆浠g爜鐨勬搷浣滄楠ゅ涓嬶細鍗曞嚮Visual Basic宸ュ叿鏍忎笂鐨勫畨鍏ㄦ寜閽紝鍦ㄥ脊鍑虹殑瀹夊叏鎬у璇濇涓彲闈犲彂琛屽晢鏍囩椤瑰簳涓嬬殑鈥樹俊浠诲浜庘淰isual Basic 椤圭洰鈥濈殑璁块棶鈥欏墠鎵撯垰銆侫LT+F11鎵撳紑VBE缂栬緫鍣紝鍗曞嚮鑿滃崟宸ュ叿-寮曠敤锛屽湪寮瑰嚭鐨勫紩鐢-VBAProject瀵硅瘽妗嗕腑锛岄夋嫨寮曠敤Microsoft Visual Basic 6.0...
绛旓細鍦‥xcel涓褰曞叆鏁版嵁鏈夊緢澶氱殑鎶宸э紝鍚堢悊鍦拌繍鐢ㄥ畠浠彲浠ユ彁楂樻垜浠殑宸ヤ綔鏁堢巼銆傛湰鏂囧彟杈熻箠寰勶紝鍚戝ぇ瀹朵粙缁嶄竴涓濡備綍鍒╃敤VBA浠g爜瀹炵幇鏁版嵁鐨勫揩閫熷綍鍏ャ傝鐪嬩笅闈㈢殑渚嬪瓙銆傚亣璁惧湪濉啓鐨勬煇鍒楁暟鎹腑锛屽憳宸ユ墍灞炵殑閮ㄩ棬鏈夆滀竴杞﹂棿鈥濄佲滀簩杞﹂棿鈥濄佲滈攢鍞儴鈥濈瓑绛夛紝濡傛灉鎴戜滑鑳界敤鏁板瓧浠f浛杩欎簺鏂囧瓧鍐呭杈撳叆鐨勮瘽锛屽繀灏嗗ぇ澶ф彁楂...
绛旓細Q锛氭垜鎯浣跨敤VBA浠g爜鍦ㄥ崟鍏冩牸涓緭鍏ユ暟缁勫叕寮忥紝濡備綍瀹炵幇锛烝锛歊ange瀵硅薄鎻愪緵浜嗕竴涓狥ormulaArray灞炴э紝鍙互鐢ㄦ潵璁剧疆鎴栬呰繑鍥炲崟鍏冩牸鍖哄煙涓鐨鏁扮粍鍏紡锛屼篃灏辨槸璇达紝鍦ㄥ伐浣滆〃鍗曞厓鏍间腑杈撳叆瀹屽悗闇瑕佹寜Ctrl+Shift+Enter缁勫悎閿墠鑳芥渶缁堝畬鎴愮殑鍏紡銆傚涓嬫墍绀猴紝瑕佹眰宸ヤ綔琛⊿heet2涓墍鍒楀嚭鐨勬按鏋滄荤殑閿鍞噾棰濓紝鍗冲垎鍒娇鐢ㄥ悇绉...
绛旓細閫夋嫨VBA鐨勪紭鍔垮湪浜庡叾楂樻晥渚挎嵎鎬э紝鍙互蹇熷鐞嗗ぇ閲忔暟鎹紝瀹炵幇浠诲姟鑷姩鍖栵紱鑷畾涔夌▼搴﹂珮锛岃兘婊¤冻涓у寲闇姹傦紱鑰屼笖璺ㄨ蒋浠跺吋瀹癸紝鏃犺浣犳槸鍦ㄥ摢涓狾ffice鐜锛岄兘鑳芥棤缂浣跨敤銆傝鍒涘缓VBA绋嬪簭锛屽彧闇鍦‥xcel涓鎸変笅Alt+F11锛岃繘鍏BA缂栬緫鍣紝閫氳繃鎻掑叆妯″潡缂栧啓浠g爜锛屽瀹炵幇A鍒楄嚜鍔ㄥ~鍏1鍒10鐨勭畝鍗曟搷浣溿傚湪瀹為檯搴旂敤涓紝...