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

²Õ¦X¤è¶ô¼g¤JVBA°ÝÃD

²Õ¦X¤è¶ô¼g¤JVBA°ÝÃD

¦p°Æ¥ó©Ò«üVBA¥i¦æ¶Ü½Ð«ü¾ÉÁÂÁÂ

²Õ¦X¤è¶ô123.rar (6.1 KB)

¦p°Æ¥ó©Ò«üVBA¥i¦æ¶Ü½Ð«ü¾ÉÁÂÁÂ
gca78000 µoªí©ó 2014-11-4 12:56


Sheets("Sheet1").ComboBox1.LinkedCell = "$A$2"

TOP

·PÁÂluhpro ¤j¤j«ü¾É¦ý¤p§Ì´ú¸Õ¦n¹³¨B¦æ­C½Ð¦b«ü¾ÉÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-11-7 03:03 ½s¿è
·PÁÂluhpro ¤j¤j«ü¾É¦ý¤p§Ì´ú¸Õ¦n¹³¨B¦æ­C½Ð¦b«ü¾ÉÁÂÁÂ
gca78000 µoªí©ó 2014-11-5 12:17

Sheets("Sheet1").ComboBox1.LinkedCell = "²M³æ!$A$2"

¸Ü»¡§Aªº½d¨ÒÀɨS¦³ Sheets("²M³æ"),
§Ú¥H¬°§A¬O­n Link ¨ì ¦P¤@­ÓSheet(Sheets("Sheet1")) ªº [A2] ¸ê®Æ.

¥t¥~§Aªº Combobox¥u­n¤@­Ó­È(²M³æ!$A$2)¶Ü?
ÁÙ¬O´X­Ó¸ê®ÆÅçÃÒªº²M³æ?
­Y¬O:
°²³] Sheets("²M³æ ") ªº B2 ~ B4 = ±i¤T,§õ¥|,¤ý¤­
Private Sub Workbook_Open()
  With Sheets("²M³æ")
    Sheets("Sheet1").ComboBox1.ListFillRange = "²M³æ!" & Range( .[B2],  .[B2].End(xlDown)).Address
  End With
End Sub
«h²M³æ¤º®e·|¦³ ±i¤T,§õ¥|,¤ý¤­ µ¥3­Ó¿ï¶µ.

TOP

luhpro¤j¤j¯u¬O°ª©Û¤p§Ì¥Ì«ô¤U­·ÁÂÁ­@¤ß«ü¾É¤p§Ì¥i§_¦A°Ý¤@ÃD¦p¤U¦C
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$3" Then UserForm1.Show
End Sub
§Ú­nÅýa3-a7+e3e7+g3-g7+i3-i7+k3-k7+m3-m7¤@¼Ë·|¨q¥XUserForm1.Show½Ð°ÝVBA­n¦p¦ó§ï

TOP

luhpro¤j¤j¯u¬O°ª©Û¤p§Ì¥Ì«ô¤U­·ÁÂÁ­@¤ß«ü¾É¤p§Ì¥i§_¦A°Ý¤@ÃD¦p¤U¦C
Private Sub Worksheet_SelectionCha ...
gca78000 µoªí©ó 2014-11-7 19:27


???
¬Ý§A¥Î Target.Address §Ú²q¬O­n¹ï¬Y¨ÇÀx¦s®æ°µ³B²z,
¦ý§A¼gªº : a3-a7+e3e7+g3-g7+i3-i7+k3-k7+m3-m7 ©Ç©Çªº.

e3e7 ¬O­þ­Ó(©Î­þ¨Ç) Àx¦s®æ¦ì§}ªºªí¥Ü¤è¦¡?
©Î¬O­n¥Î e3:e7 (½d³òÀx¦s®æ)¶Ü?
¥t a3-a7... ¤S¥[¤S´îªº,
¬O­n¹ïÀx¦s®æ¤º®e°µ­pºâ,
ÁÙ¬O­n¹ïÀx¦s®æ¦ì§}§@¼W´î?

§Ú²q´ú§A¥i¯à¬O­n·íÂIÀ»¤F :
a3,a7,e3,e7,g3,g7,i3,i7,k3,k7,m3,m7
µ¥Àx¦s®æ´N­nÅã¥ÜUserForm1.

­Y¬O³o¼Ë§Ú­Ì¥i¥H¥ýÂk¯Ç³o¨ÇÀx¦s®æ¦ì§}ªº³W«ß:

1. ³£¸¨¦b²Ä 3 ¦C»P²Ä 7 ¦C -> ¦Cªºªí¥Ü¤è¦¡¬° Range.Row.
³o¼Ë¥u­n¤@¦æ :
  If Target.Row=3 Or Target.Row=7 Then
´N¯à¹ê²{.

2. Äæ§Ç¤À§O¬° a,e,g,i,k,m ³o 6 Äæ,«h¥Î :
If Target.Column=1 Or Target.Column=5 Or Target.Column=7 Or Target.Column=9 Or Target.Column=11Or Target.Column=13 Then
¨Ó¹ê²{.

¦ý³o¼Ë¼gµ{¦¡½X¤Óªø¤F,
¹ï©ó«D³sÄò©Î¤£¨ã³W«ß©Ê¦h²Õ¼Æ¦rªº§PÂ_¥i¥H¥Î
Select Case Target.Column
  Case 1,5,7,9,11,13
    ...
end select
¨Ó¹ê²{,
¦n³B¬O¬üÆ[, ¨ãµ²ºc©Ê©ö°£¿ù¤ÎºûÅ@.

ºî¤W©Ò­zµ{¦¡¥i¥H­×§ï¦p¤U:
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.   Dim bShow As Boolean
  3.   
  4.   bShow = False
  5.   With Target
  6.     If .Row = 3 Or .Row = 7 Then
  7.       Select Case .Column
  8.         Case 1, 5, 7, 9, 11, 13
  9.           bShow = True
  10.       End Select
  11.     End If
  12.   End With
  13.   If bShow Then UserForm1.Show
  14. End Sub
½Æ»s¥N½X

TOP

·P谢luhpro¤j¤j­@¤ß«ü¾É¤p§Ì¨ü¯q¨}¦h´ú¸Õ¤w¥i¥HÁÂÁ±оɷP®¦§A

TOP

¦^´_ 4# luhpro
¤p§Ì¨ü±Ð¤F¥H¸g¸Ñ¨M¥i¥H¤F·P®¦±Ð¾É

TOP

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