[發問] 選擇ListBox內資料如何放到其他UserForm的TextBox

問題一
請問要如何可以點選ListBox的其中的資料後
按確定可以將點選的"編號"放到UserForm2的TextBox1

問題二
如果我有二個UserForm
我選UserForm2使用查詢時，"編號"就會存在UserForm2的TextBox1
我選UserForm3使用查詢時，"編號"就會存在UserForm3的TextBox1

Option Explicit
Dim xSh As Worksheet, xRng(1 To 3) As Range, Msg單號 As Boolean, d As Object, d2 As Object, d3 As Object
Private Sub CommandButton6_Click()
If TextBox1.Text <> "" Then
'將 UserForm2查出來的編號資料放入 UserForm1的TextBox1
UserForm1.TextBox1.Text = UserForm2.TextBox1.Text
'將 TextBox1 資料放入 TBox1
Unload UserForm2
'關閉表單
Else
End If
End Sub
Private Sub UserForm_Initialize()
Set xSh = Sheets("清單")
'宣告 xSH = Sheets("清單")
MultiPage1.Value = 0
Show_List
Dim i As Single, Rng As Range
Set d = CreateObject("scripting.dictionary")
'宣告 d = 建立並傳回 ActiveX 物件建立一個 Dictionary物件
Set Rng = xSh.Cells(5, "D")
'xSH = Sheets("基本資料") Rng =儲存格 Cells(第5欄,C列)
Do While Rng <> ""
'Rng = 儲存格 Cells(第5欄,C列) 不等於空白
If d.exists(Rng.Value) = False Then
'
Set d(Rng.Value) = Rng
Else
Set d(Rng.Value) = Union(Rng, d(Rng.Value))
End If
Set Rng = Rng.Offset(1)
Loop
ComboBox1.List = d.KEYS
'ComboBox1 等於 d KEYS
End Sub
Private Sub SpinButton1_Change()
單號.Caption = SpinButton1
Msg單號 = True
Show_List
Msg單號 = False
End Sub
Private Sub Show_List()
Dim i As Integer, E As Variant
Set xRng(1) = xSh.Range("c4").CurrentRegion
With xSh
i = .Columns.Count
Set xRng(1) = .Range("c4").CurrentRegion
Set xRng(2) = .Cells(1, i).CurrentRegion
xRng(2).Clear
If Msg單號 Then
.Cells(1, i) = "單號"
.Cells(2, i) = SpinButton1
Else
End If
Set xRng(2) = .Cells(1, i).CurrentRegion
End With
Set xRng(3) = xRng(2).Cells(1).Offset(, -20).Resize(, xRng(1).Columns.Count)
xRng(3).CurrentRegion.Clear
xRng(1).AdvancedFilter xlFilterCopy, xRng(2), xRng(3)
Set xRng(3) = xRng(3).CurrentRegion
If xRng(3).Rows.Count > 2 Then
Set xRng(3) = xRng(3).Rows("2:" & xRng(3).Rows.Count)
Else
Set xRng(3) = xRng(3).Rows(2)
End If
With ListBox1
.ColumnHeads = True
.ColumnCount = -1
.RowSource = xRng(3).Address(, , , 1, 1)
End With
xRng(2).Clear
End Sub

複製代碼

ZA0004001.rar (33.14 KB)

樓主熱帖

excel學習中!築出夢想
https://www.youtube.com/channel/UCPAiRNsHX3k_omp0YDgfDiA

kkooo15

　暱稱: 新手

中學生 Rank: 2

帖子: 123
主題: 37
精華: 0
積分: 177
點名: 0
作業系統: WIN10
軟體版本: Office 2010
閱讀權限: 20
性別: 男
來自: 台北
註冊時間: 2011-11-9
最後登錄: 2024-3-28

2^#

發表於 2019-7-29 23:22 | 只看該作者

本帖最後由 kkooo15 於 2019-7-29 23:25 編輯

回復 1# kkooo15

問題二
如果我有二個UserForm
我選UserForm2使用查詢時，"編號"就會存在UserForm2的TextBox1
我選UserForm3使用查詢時，"編號"就會存在UserForm3的TextBox1

我想到辦法解決了。

就是在開表單的時候把資料清空。