½Ð°Ý¦p¦ó¥ÎTextbox·s¼W¸ê®Æ¨ìSheet?
- ©«¤l
- 228
- ¥DÃD
- 62
- ºëµØ
- 0
- ¿n¤À
- 364
- ÂI¦W
- 20
- §@·~¨t²Î
- Win 10
- ³nÅ骩¥»
- Office 2007 & 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-3-5
- ³Ì«áµn¿ý
- 2024-9-16
|
½Ð°Ý¦p¦ó¥ÎTextbox·s¼W¸ê®Æ¨ìSheet?
½Ð°Ý¦p¦ó¥ÎTextbox·s¼W¸ê®Æ¨ìSheet?
±Í¤H¹ï©óVBA¤£À´¡A¥u·|°Å¶Kקï¤@¤U¡A©Ò¥Hµ{¦¡»Ýn¤j®vÀ°¦£×§ï¡C
¸Ì±UserForm1¬O¤§«eºô¸ô¤H®a°µ¦nªº¡AUserForm2¬O§Ú·Qn°µªº¡C
½ÐÀ°§Ú±NPart1¡BPart2¡BPart3¥\¯àµ²¦X°_¨Ó¡C |
|
|
|
|
|
|
- ©«¤l
- 228
- ¥DÃD
- 62
- ºëµØ
- 0
- ¿n¤À
- 364
- ÂI¦W
- 20
- §@·~¨t²Î
- Win 10
- ³nÅ骩¥»
- Office 2007 & 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-3-5
- ³Ì«áµn¿ý
- 2024-9-16
|
¦U¦ì¤j®v¡A¤§«e¥i¯à¬O¤p§Ì»¡ªº¤£²M·¡¡A¤~¨S¤H¦^À³¡A¦b¦¹§Ú¦A«»¡¤@¦¸»Ý¨D¡G
ÀÉ®×1¡Ghttp://www.FunP.Net/368256
¦¹Àɪí³æUserForm1¤w§¹¦¨
ÀÉ®×2¡Ghttp://www.FunP.Net/623352
¦¹Àɪí³æUserForem2¤¤ªºListbox1-5¤]»s§@¦n¤F
»Ý¨D¬O¡G±NUserForm1ªº¥\¯à¥[¨ìUserForm2¸Ì± |
|
|
|
|
|
|
- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 2# jeffrey628litw
¸Õ¸Õ¬Ý- Private Const Sh = "Sheet1" '¸ê®Æ®w '¼Ò²Õªº¨p¥Î±`¼Æ
- Dim d As Object '¼Ò²Õªº¨p¥ÎÅܼÆ
- Private Sub UserForm_Initialize()
- Set d = CreateObject("Scripting.Dictionary")
- With Sheets(Sh)
- K = 2
- Do While .Cells(K, "A") <> ""
- d(.Cells(K, "A").Value) = ""
- K = K + 1
- Loop
- End With
- ListBox_1.List = d.KEYS
- End Sub
- Private Sub ListBox_1_Change() '»Ý×§ï ºò«æ«× ListBox±±¨î¶µ¦WºÙ
- ¸ê®Æ¨î©w 1 '©I¥sµ{¦¡ ¶Ç»¼°Ñ¼Æ : 1
- End Sub
- Private Sub ListBox_2_Change() '»Ý×§ï »sµ{ ListBox±±¨î¶µ¦WºÙ
- ¸ê®Æ¨î©w 2 '©I¥sµ{¦¡ ¶Ç»¼°Ñ¼Æ : 2
- End Sub
- Private Sub ListBox_3_Change() '»Ý×§ï ³¡ªù ListBox±±¨î¶µ¦WºÙ
- ¸ê®Æ¨î©w 3 '©I¥sµ{¦¡ ¶Ç»¼°Ñ¼Æ : 3
- End Sub
- Private Sub ListBox_4_Change() '»Ý×§ï «ù¦³ªÌ ListBox±±¨î¶µ¦WºÙ
- ¸ê®Æ¨î©w 4 '©I¥sµ{¦¡ ¶Ç»¼°Ñ¼Æ : 2
- End Sub
- Private Sub ListBox_5_Change() '»Ý×§ï ®×¥ó ListBox±±¨î¶µ¦WºÙ
- ¸ê®Æ¨î©w 5 '©I¥sµ{¦¡ ¶Ç»¼°Ñ¼Æ : 2
- End Sub
- Private Sub ¸ê®Æ¨î©w(OB As Integer)
- Dim xValue As String, i As Integer, xCellValue As String
- '***®×¥ó°ò¥»¸ê®ÆªºTextBox ½Ðקï TextBox1-TextBox9
- For i = 1 To 9
- Controls("TextBox" & i) = "" '®×¥ó°ò¥»¸ê®ÆªºTextBox±±¨î¶µ.²MªÅ
- Next
- Set d = CreateObject("Scripting.Dictionary") '«³]ª«¥ó
- For i = 1 To 5
- If i <= OB Then
- xValue = xValue & Controls("ListBox_" & i).Value 'µ²¦XListBoxªºÈ
- Else
- Controls("ListBox_" & i).Clear 'ListBox²M³æ²M°£
- End If
- Next
- With Sheets(Sh)
- K = 2
- Do While .Cells(K, "A") <> ""
- xCellValue = Application.Phonetic(.Cells(K, "A").Resize(1, OB)) 'µ²¦XÀx¦s®æªºÈ
- If OB = 5 And xValue = xCellValue Then
- For i = 1 To 9
- Controls("TextBox" & i) = .Cells(K, OB).Offset(, i) '®×¥ó°ò¥»¸ê®ÆªºTextBox±±¨î¶µ.¿é¤J¸ê®Æ
- Next
- Exit Sub
- ElseIf OB < 5 And xValue = xCellValue Then
- d(.Cells(K, OB + 1).Value) = ""
- End If
- K = K + 1
- Loop
- If OB < 5 Then Controls("ListBox_" & OB + 1).List = d.KEYS
- End With
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 228
- ¥DÃD
- 62
- ºëµØ
- 0
- ¿n¤À
- 364
- ÂI¦W
- 20
- §@·~¨t²Î
- Win 10
- ³nÅ骩¥»
- Office 2007 & 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-3-5
- ³Ì«áµn¿ý
- 2024-9-16
|
¥ý¦b¦¹·P®¦ª©¤jªºÀ°¦£...^^...
ª©¥D¤j¤j±z¦n¡G§Ú¹ï©óVBA¹ê¦b«Ü²Â©å¡A½Ð°Ý§Ú±N§Aªºµ{¦¡¶K¹L¨Ó«á¡A¬O³o¸Ì¦³°ÝÃD¶Ü?
Private Sub UserForm_Initialize()
Set DY = CreateObject("Scripting.Dictionary")
Set DZ = CreateObject("Scripting.Dictionary")
Set Sht1 = Sheets("Sheet1")
K = Sht1.[A65536].End(xlUp).Row
With Sht1
For Y = 2 To K
Ts = .Range("A" & Y): If DY(Ts) <> "" Then Else DY(Ts) = Ts: ListBox5.AddItem Ts
If .Range("D" & Y) <> "" Then TS1 = .Range("D" & Y)
If DZ(TS1) <> "" Then Else DZ(TS1) = TS1: ListBox1.AddItem TS1
Next
End With
Set DY = Nothing: Set DZ = Nothing: Y = 0: Ts = "": TS1 = "" '³o¥H¤W¬°Private Sub ListBox5_Click()©MPrivate Sub ListBox4_Click()ªºµ{¦¡
End Sub
Private Const Sh = "Sheet1" '¸ê®Æ®w '¼Ò²Õªº¨p¥Î±`¼Æ
Set d = CreateObject("Scripting.Dictionary")
With Sheets(Sh)
K = 2
Do While .Cells(K, "A") <> ""
d(.Cells(K, "A").Value) = ""
K = K + 1
Loop
End With
ListBox_1.List = d.KEYS
End Sub
Àɮצb¦¹(¤è«Kªº¸Ü½Ð±z±NÀɮקï¦n¤W¶Çµ¹§Ú¤U¸ü)¡Ghttp://www.FunP.Net/823425 |
|
|
|
|
|
|
- ©«¤l
- 228
- ¥DÃD
- 62
- ºëµØ
- 0
- ¿n¤À
- 364
- ÂI¦W
- 20
- §@·~¨t²Î
- Win 10
- ³nÅ骩¥»
- Office 2007 & 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-3-5
- ³Ì«áµn¿ý
- 2024-9-16
|
¦^´_ 5# GBKEE
ª©¤j±z¦n¡A§Ú¸Õ¤F§â2ÓÀɮשM±zªºµ{¦¡³£¶K¶i¥h¡AÁÙ¬O¤£¦æ(§Ú¹ê¦b¬O¬Ý¤£À´¤£·|§ï)¡A¯à§_¦A½Ð±zÀ°¦£¬Ýnק諸¦a¤è¡G
¥Dn¤£ª¾¬O¤£¬O³oùئ³°ÝÃD?ÀɮפU¸üºô§}¡Ghttp://www.FunP.Net/678558
Private Sub UserForm_Initialize()
Set DY = CreateObject("Scripting.Dictionary") '³o¥H¤U¬°Private Sub ListBox5_Change()©MPrivate Sub ListBox4_Change()ªºµ{¦¡
Set DZ = CreateObject("Scripting.Dictionary")
Set Sht1 = Sheets("Sheet1")
K = Sht1.[A65536].End(xlUp).Row
With Sht1
For Y = 2 To K
Ts = .Range("A" & Y): If DY(Ts) <> "" Then Else DY(Ts) = Ts: ListBox5.AddItem Ts
If .Range("D" & Y) <> "" Then TS1 = .Range("D" & Y)
If DZ(TS1) <> "" Then Else DZ(TS1) = TS1: ListBox1.AddItem TS1
Next
End With
Set DY = Nothing: Set DZ = Nothing: Y = 0: Ts = "": TS1 = "" '³o¥H¤W¬°Private Sub ListBox5_Change()©MPrivate Sub ListBox4_Change()ªºµ{¦¡
yc = ListBox1.BackColor '³o¥H¤U¬°Listbox1¡BListbox2ªºµ{¦¡
wc = TextBox1.BackColor
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
With Sheets("¸ê®Æ®w")
.Unprotect Password:="69123"
rng = .[A1].CurrentRegion
End With
For r = 2 To UBound(rng)
mycase = "-" & rng(r, 2)
If Trim(rng(r, 1)) <> "" Then
myname = Trim(rng(r, 1))
br = r
d1(myname) = r & "-" & r
Else
d1(myname) = br & "-" & r
End If
d2(myname & mycase) = r
Next r
UserForm2.ListBox1.List = d1.KEYS
UserForm2.CommandButton11.SetFocus '³o¥H¤W¬°Listbox1¡BListbox2ªºµ{¦¡
Set d = CreateObject("Scripting.Dictionary")
With Sheets(Sh)
K = 2
Do While .Cells(K, "A") <> ""
d(.Cells(K, "A").Value) = ""
K = K + 1
Loop
End With
ListBox_1.List = d.KEYS
End Sub |
|
|
|
|
|
|
- ©«¤l
- 228
- ¥DÃD
- 62
- ºëµØ
- 0
- ¿n¤À
- 364
- ÂI¦W
- 20
- §@·~¨t²Î
- Win 10
- ³nÅ骩¥»
- Office 2007 & 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-3-5
- ³Ì«áµn¿ý
- 2024-9-16
|
¦^´_ 7# GBKEE
ª©¤j±z¦n¡A¤Sn³Â·Ð±z¤F¡A§Ú¤w¸g§ï¤F³¡¤À¤F¡A°ÝÃD1¡G¤£¹L§ï¨ìªí³æªº-->«ù¦³ªÌ"·s¼W«ù¦³ªÌ"³oÓOK-->®×¥ó"·s¼W®×¥ó"OK -->¦ý¬O "µ²§ô·s¼W" ´N¥X²{¥H¤U¤£ª¾þ¸Ì¿ù»~?
°ÝÃD2¡G¦p¦óÅý«ù¦³ªÌ"·s¼W«ù¦³ªÌ"ªº¸ê®Æ¦s¨ì¥¿½T¦ì¸m Sheet1ªºDÄæ
®×¥ó"·s¼W®×¥ó ªº¸ê®Æ¦s¨ì¥¿½T¦ì¸m Sheet1ªºEÄæ
ÀɮפU¸üºô§}¡Ghttp://www.FunP.Net/710388
Private Sub UpdateBox()
d1.RemoveAll <-------------------------------------------------------------------------------------------------³o¤@¬qµ{¦¡¤Ï¶À
d2.RemoveAll
rng = Sheets("Sheet1").[A1].CurrentRegion
For r = 2 To UBound(rng)
mycase = "-" & rng(r, 2)
If Trim(rng(r, 1)) <> "" Then
myname = Trim(rng(r, 1))
br = r
d1(myname) = r & "-" & r
Else
d1(myname) = br & "-" & r
End If
d2(myname & mycase) = r
Next r
UserForm2.ListBox_4.List = d1.keys
End Sub |
|
|
|
|
|
|
- ©«¤l
- 228
- ¥DÃD
- 62
- ºëµØ
- 0
- ¿n¤À
- 364
- ÂI¦W
- 20
- §@·~¨t²Î
- Win 10
- ³nÅ骩¥»
- Office 2007 & 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-3-5
- ³Ì«áµn¿ý
- 2024-9-16
|
¦^´_ 9# GBKEE
·PÁª©¤j¤@ª½À°¦£¸Ñ¨M°ÝÃD¡A§Aªº¸Ñµª¯u¬O¤@°w¨£¦å¡A§Ú¦b§ïªº³~¤¤¤S¹J¨ì°ÝÃD¡G
¦b³Ì«á"®×¥ó°ò¥»¸ê®Æ" «ö¤U "קï" ·|¥X²{¤U±µ{¦¡¤Ï¶À¡A§Ú¤£ª¾¹D¬O¬Æ»ò°ÝÃD¡A¤Sn½Ð±Ðª©¥D¤F¡C
ÀɮפU¸üºô§}¡Ghttp://www.FunP.Net/331482
Private Sub CommandButton4_Click()
With UserForm2
myday = Trim(.TextBox8.Value)
If myday <> "" And IsDate(myday) = False Then
MsgBox "±z¿é¤Jªº§¹¤u¤é´ÁµLªk¿ë§O³á¡ã", vbCritical + vbOKOnly, "½Ð«·s¿é¤J"
.TextBox8.SetFocus
Exit Sub
End If
.Frame1.Enabled = True
.Frame2.Enabled = True
.CommandButton2.Enabled = True
.CommandButton11.Enabled = True
myname = .ListBox_4.Text
mycase = .ListBox_5.Text
r = d2(myname & "-" & mycase)
For i = 1 To 9
Sheets("Sheet1").Cells(r, i + 1).Value = .Controls("TextBox" & i).Value <--------------------------------------------------------------³o¬qµ{¦¡·|¤Ï¶À
.Controls("TextBox" & i).ForeColor = -2147483640
.Controls("TextBox" & i).BackColor = yc
.Controls("TextBox" & i).Locked = True
Next i
Call UpdateBox
.ListBox_4.Text = myname
.ListBox_5.Text = mycase
.CommandButton4.Enabled = False
End With
MsgBox "¤w¸g§¹¦¨Àx¦sÅo¡ã", vbOKOnly, "½Ðª`·N"
End Sub |
|
|
|
|
|
|