麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 如何讓下拉選單選取後自動跳到儲存格相對應處? [打印本頁]

作者: av8d 時間: 2013-11-1 08:48 標題: 如何讓下拉選單選取後自動跳到儲存格相對應處?

[attach]16526[/attach]
在規則名稱下拉選單選取後，如何自動跳到相對應的數量區？

例如：
點一下A2~會出現下拉選單，選取後~如何自動跳到C2？
然後有多筆資料時，會繼續點一下A3，會出現下拉選單~選取後~如何自動跳到C3？

作者: av8d 時間: 2013-11-4 09:45

回復 1# av8d

或是說改成~當數量輸入後按下Enter後~自動跳到規格名稱

例如：C2輸入數字後按下Enter後~自動跳到A2

作者: stillfish00 時間: 2013-11-4 20:15

本帖最後由 stillfish00 於 2013-11-4 20:18 編輯

Private Sub ComboBox1_Change()
Application.EnableEvents = False
ComboBox1.Visible = False
Range(ComboBox1.LinkedCell).Offset(, 2).Select
Application.EnableEvents = True
End Sub

複製代碼

回復 1# av8d

作者: c_c_lai 時間: 2013-11-5 08:56

回復 3# stillfish00
順帶請教一個問題：
(假設新增一個 Excel 檔案)
在 "工作表2" A 欄位中如何去設定 ActiveCell.Validation.Formula1 對應下拉式 List 的指定欄位值，
譬如： "=工作表1!$A$3:$A$20" (即從 "工作表2" A2:A10 都能自動去對應 "工作表1" 的 A3:A20)
謝謝你！
(不好意思，我對這方面的應用從沒觸摸過 - 欠學)

作者: stillfish00 時間: 2013-11-5 09:40

本帖最後由 stillfish00 於 2013-11-5 09:43 編輯

回復 4# c_c_lai
你可以從資料>資料驗證>去設定儲存格內清單，再選來源。
VBA可參考 Validation.Add 方法。

With Sheets("工作表2").[A2:A10].Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=工作表1!$A$3:$A$20"
End With

複製代碼

作者: c_c_lai 時間: 2013-11-5 09:43

回復 5# stillfish00
感激，終於又學到如何設定 Validation 了，
再次謝謝你！

作者: c_c_lai 時間: 2013-11-5 13:28

回復 2# av8d
綜合了 stillfish00 大大提供的函數，以及加上整合你的需求而成。
1. " 點一下A2~會出現下拉選單，選取後~如何自動跳到C2 "，
2. " 當數量輸入後按下Enter後~自動跳到規格名稱 (C2輸入數字後按下Enter後~自動跳到A2) "
第二項需求，修改成：
當數量輸入後按下Enter後~自動跳到下一個規格名稱 (C2輸入數字後按下Enter後~自動跳到A3 )

Public ckCurr As Boolean
Private Sub ComboBox1_Change() ' stillfish00 提供
If ckCurr Then Exit Sub
Application.EnableEvents = False
ckCurr = False
ComboBox1.Visible = False
Range(ComboBox1.LinkedCell).Offset(, 2).Select
Application.EnableEvents = True
End Sub
Private Sub CommandButton1_Click()
If Me.ComboBox1.Visible Then ckCurr = True: Me.ComboBox1.Visible = False
Range("A2:A25,C2:C25").Select
Selection.ClearContents
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim StrVdFml As String
' If ckCurr Then Exit Sub
On Error Resume Next
StrVdFml = Replace(ActiveCell.Validation.Formula1, "=", "")
' ActiveCell.Validation.Formula1 ： "=工作表1!$A$3:$A$20"
' ComboBox1.ListFillRange ：工作表1!$A$3:$A$20
' Replace(ActiveCell.Validation.Formula1, "=", "") : "工作表1!$A$3:$A$20"
' StrVdFml : "工作表1!$A$3:$A$20"
ActiveCell.Validation.InCellDropdown = False
On Error GoTo 0
If StrVdFml = "" Then
If Me.ComboBox1.Visible Then Me.ComboBox1.Visible = False
Else
With Me.ComboBox1
' ComboBox1.progID =EMBED("Forms.ComboBox.1","")
' ComboBox1： ComboBox
' LinkedCell： $A$2
' ListFillRange ：工作表1!$A$3:$A$20
.Left = ActiveCell.Left
.Top = ActiveCell.Top
' .Width = ActiveCell.Width + 140
.Width = ActiveCell.Width
' .Height = ActiveCell.Height + 10
.Height = ActiveCell.Height
' .Font.Size = 22
.Font.Size = 12
.LinkedCell = ActiveCell.Address ' "$A$2"
.ListFillRange = StrVdFml ' "工作表1!$A$3:$A$20"
.Visible = 1 ' 顯示下拉符號
.Object.SpecialEffect = 3
'.Object.Font.Size = ActiveCell.Font.Size
End With
End If
ckCurr = False
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' Target.Font.ColorIndex = 5
If Not Intersect(Target, Range("C2:C25")) Is Nothing Then
If Target(1, 1) = 0 Then Exit Sub
ckCurr = True
' MsgBox Target.Address
Target.Offset(1, -2).Select
End If
End Sub
Sub CellValidation() ' stillfish00 提供
With Sheets("工作表2").[A2:A25].Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=工作表1!$A$3:$A$20"
End With
End Sub

複製代碼

作者: av8d 時間: 2013-11-6 09:03

謝謝~stillfish00 大大的解答
讓c_c_lai大大解答了我真正想要使用的功能~

兩位大大謝謝~學習了

作者: ML089 時間: 2013-11-6 09:14

回復 7# c_c_lai

我下載你的檔案，點選Sheet2 - A2沒有出現下拉選單，
請問我還需要做哪些設定。

作者: c_c_lai 時間: 2013-11-6 10:13

回復 c_c_lai

我下載你的檔案，點選Sheet2 - A2沒有出現下拉選單，
請問我還需要做哪些設定。
ML089 發表於 2013-11-6 09:14

[attach]16585[/attach]

作者: ML089 時間: 2013-11-6 10:36

回復 10# c_c_lai

謝謝你提供那麼詳細的說明

我的EXCEL開啟(安全同意也開啟)也依照畫面步驟去點選A2，可是沒有出現下拉表，是否我缺少什麼物件

[attach]16586[/attach]

作者: ML089 時間: 2013-11-6 10:49

回復 10# c_c_lai

我的EXCEL是2007
開啟你的檔案，其工作表名稱是 Sheet1、 Sheet2、 Sheet3
但VBA程式中用的工作表名稱是工作表1、工作表2
我試著將 VBA中的 "工作表" 改為 "Sheet" 也不行
該如何是好

例如
Sub CellValidation()    '  stillfish00 提供
With Sheets("工作表2").[A2:A25].Validation
      .Delete
      .Add Type:=xlValidateList, Formula1:="=工作表1!$A$3:$A$20"
End With
End Sub

作者: c_c_lai 時間: 2013-11-6 11:52

回復 c_c_lai

我的EXCEL是2007
開啟你的檔案，其工作表名稱是 Sheet1、 Sheet2、 Sheet3
但VBA程式中 ...
ML089 發表於 2013-11-6 10:49

之前我是另外新增一Excel檔案測試，完成後便直接將程式碼貼入到原本的
"相片輸出價目表.xlsm" 內，忘了將其對應的表單名稱一併修正，真是對不起！
[attach]16587[/attach]

作者: c_c_lai 時間: 2013-11-6 11:56

回復 12# ML089
你再試試看，測試結果如何再行告訴我。
謝謝！

作者: ML089 時間: 2013-11-6 12:47

回復 14# c_c_lai

還是不行，按A2、A3...都沒有反應
之前樓主的檔案也不行，後來我自行設定資料驗證(清單) ，下拉選單才出現

請問 ComboBox1 是否需要自行增設，還是程式已經內定就有

作者: c_c_lai 時間: 2013-11-6 12:52

回復 c_c_lai

還是不行，按A2、A3...都沒有反應
之前樓主的檔案也不行，後來我自行設定資料驗證(清單 ...
ML089 發表於 2013-11-6 12:47

請問你是下載 "相片輸出價目表.xlsm" 直接用它來測試，還是
自己另外開啟一新檔案，而僅複製程式碼來運作？

作者: owen06 時間: 2013-11-6 13:14

回復 16# c_c_lai

我也是耶，直接開啟下載的檔案，然後點了A2就出現錯誤訊息了，
二個檔案都一樣。

作者: c_c_lai 時間: 2013-11-6 13:58

回復 17# owen06
請你直接開啟 #1 的附件，然後將以下程式碼貼入到 Sheet2 的程式碼區再試試看 (完全複製過去)，
結果如何請回復告知，謝謝！ (我這邊無論是另行新增、或者是使用 #1 的附件都是OK的 )

Public ckCurr As Boolean
Private Sub ComboBox1_Change() ' stillfish00 提供
If ckCurr Then Exit Sub
Application.EnableEvents = False
ckCurr = False
ComboBox1.Visible = False
Range(ComboBox1.LinkedCell).Offset(, 2).Select
Application.EnableEvents = True
End Sub
Private Sub CommandButton1_Click()
If Me.ComboBox1.Visible Then ckCurr = True: Me.ComboBox1.Visible = False
Range("A2:A25,C2:C25").Select
Selection.ClearContents
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim StrVdFml As String
' If ckCurr Then Exit Sub
On Error Resume Next
StrVdFml = Replace(ActiveCell.Validation.Formula1, "=", "")
' ActiveCell.Validation.Formula1 ： "=Sheet1!$A$3:$A$20"
' ComboBox1.ListFillRange ： Sheet1!$A$3:$A$20
' Replace(ActiveCell.Validation.Formula1, "=", "") : "Sheet1!$A$3:$A$20"
' StrVdFml : "Sheet1!$A$3:$A$20"
ActiveCell.Validation.InCellDropdown = False
On Error GoTo 0
If StrVdFml = "" Then
If Me.ComboBox1.Visible Then Me.ComboBox1.Visible = False
Else
With Me.ComboBox1
' ComboBox1.progID =EMBED("Forms.ComboBox.1","")
' ComboBox1： ComboBox
' LinkedCell： $A$2
' ListFillRange ： Sheet1!$A$3:$A$20
.Left = ActiveCell.Left
.Top = ActiveCell.Top
' .Width = ActiveCell.Width + 140
.Width = ActiveCell.Width
' .Height = ActiveCell.Height + 10
.Height = ActiveCell.Height
' .Font.Size = 22
.Font.Size = 12
.LinkedCell = ActiveCell.Address ' "$A$2"
.ListFillRange = StrVdFml ' "Sheet1!$A$3:$A$20"
.Visible = 1 ' 顯示下拉符號
.Object.SpecialEffect = 3
'.Object.Font.Size = ActiveCell.Font.Size
End With
End If
ckCurr = False
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' Target.Font.ColorIndex = 5
If Not Intersect(Target, Range("C2:C25")) Is Nothing Then
If Target(1, 1) = 0 Then Exit Sub
ckCurr = True
' MsgBox Target.Address
Target.Offset(1, -2).Select
End If
End Sub
Sub CellValidation() ' stillfish00 提供
With Sheets("Sheet2").[A2:A25].Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=Sheet1!$A$3:$A$20"
End With
End Sub

複製代碼

作者: ML089 時間: 2013-11-6 15:08

回復 18# c_c_lai

開啟 #1 的附件，然後將程式碼貼入到 Sheet2 的程式碼區，還是不行

自行設定資料驗證後，是可以執行，下拉選單 - 選完自動跳到 C欄數量 - 輸入數量也自動跳到下一列。
可是下拉選單又好像不是資料驗證的下拉選單，應該是ComboBox1得下拉選單

我自己用資料驗證配合Worksheet_Change也可以達到效果，只是資料驗證選單功能比較陽春。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(, 2).Select
ElseIf Target.Column = 3 Then
Target.Offset(1, -2).Select
End If
End Sub

[attach]16590[/attach]

作者: c_c_lai 時間: 2013-11-6 15:49

回復 19# ML089
再試試看 (有整理過)
[attach]16591[/attach]
[attach]16592[/attach]

作者: c_c_lai 時間: 2013-11-6 15:57

回復 19# ML089
附上整理後之建置設定，供做參考之用：
[attach]16593[/attach]
[attach]16594[/attach]

作者: ML089 時間: 2013-11-6 16:10

回復 20# c_c_lai

A2完全空白，沒有你圖上多出來的選單

若開起設計模式後才會看到 COMBOBOX物件

[attach]16595[/attach]

作者: ML089 時間: 2013-11-6 16:21

回復 21# c_c_lai

既然用 COMBOBOX物件
可否將規格名稱、尺寸、單價合併於下拉選單中顯示，
選中後規格名稱、尺寸、單價直接寫至欄位中，
這樣會更專業感一些。

不好意思，偷偷加碼一下

作者: c_c_lai 時間: 2013-11-6 16:24

回復 23# ML089
請再仔細回味 #10 的過程圖片說明。
(設計模式與操作模式不能混為一談)

作者: ML089 時間: 2013-11-6 16:30

回復 21# c_c_lai

圖一，就是我原先說的，好像要設資料驗證 - 清單，這樣程式才能啟動。

圖二，內容相同

作者: ML089 時間: 2013-11-6 16:35

回復 24# c_c_lai

沒錯在操作模式之下，沒有出現下拉選單的三角符號，就是一般空白

在設計模式之下有看見 COMBOBOX 物件

可能是我EXCEL 2007的問題嗎? 請問你用的是幾板?
若轉存為 2003板可以用嗎 ? 給我試試看

作者: c_c_lai 時間: 2013-11-6 16:41

本帖最後由 c_c_lai 於 2013-11-6 16:42 編輯

回復 25# ML089
原檔案、加上我自個兒新增應用測試的檔、以及你附上的檔
我都一一測試無訛，實在無法明瞭為何你那方不行。
況且 #10 的圖表操作過程，以上三個檔操作過程都一致。
我看你只好去請教 #1 的 av8d 大大了。

作者: c_c_lai 時間: 2013-11-6 17:00

回復 26# ML089
[attach]16596[/attach]

作者: ML089 時間: 2013-11-6 17:09

回復 3# stillfish00

請問 stillfish00大及 av8d大
你們可以TEST 19樓或20樓的檔案可以正常使用嗎?
我的下拉選單一直出不來

感謝

作者: stillfish00 時間: 2013-11-6 17:31

回復 29# ML089
我的可以唷，你的Excel是32位元還是64位元(不是作業系統哦)?
我是Excel 2010 (32位元)，是有聽說過32位元的一些activex控制項在64位元不能使用，
但combobox應該不算在內吧!? (不確定)

你也可以開設計模式刪掉combobox1，用你的Excel重新隨便新增一個combobox1，再點看看。
再不然debug一下StrVdFml是不是空字串

作者: c_c_lai 時間: 2013-11-7 05:39

回復 30# stillfish00
回復 29# ML089
經 stillfish00 提醒，我上網查了一下，有一篇文章可供參考：
Compatibility Between the 32-bit and 64-bit Versions of Office 2010
文章內有關 ActiveX Control and COM Add-in Compatibility 的說明。

作者: ML089 時間: 2013-11-7 10:05

回復 30# stillfish00

謝謝回覆

我是Excel 2007 (32位元)

debug StrVdFml 是空字串

開設計模式刪掉combobox1，用自己的Excel重新新增一個combobox1，也是不行

如果是我自己EXCEL的問題就不要再浪費大家的時間，謝謝大家

作者: ML089 時間: 2013-11-7 10:11

回復 31# c_c_lai

謝謝
這太深奧了，有看沒有懂
假日多一點時間我再來研究

作者: stillfish00 時間: 2013-11-7 10:54

回復 32# ML089
這樣看起來和控制項無關，是你的資料驗證清單消失了，導致判斷後隱藏控制項，我下載回來驗證清單是有來源的。

作者: ML089 時間: 2013-11-7 11:06

回復 34# stillfish00

我下載的檔案裡是沒有設資料驗證清單，所以只要設定了資料驗證清單就執行無誤。

我其中比較不懂的是，既然使用資料驗證清單為何還要用 COMBOBOX 選單，
如果要用 COMBOBOX 選單也設了LIST項目，應該就不需使用資料驗證清單才對，
覺得有點畫蛇添足的感覺

作者: stillfish00 時間: 2013-11-7 11:29

回復 35# ML089
同意你的看法，要看原本它的目的是什麼，或許就是為了能控制清單外觀或字型/字體大小吧，
為了美觀而採用控制項實現也是很有可能的。

作者: c_c_lai 時間: 2013-11-7 14:47

回復 33# ML089
假設你電腦安裝的作業系統是 Win7 64 Bits，
Office 如果為 64 Bits，則建議你使用 (安裝) 32 位元的 Office，
以免產生支援上的技術問題。同理、如為 2007 亦如上述建議。

作者: c_c_lai 時間: 2013-11-7 14:54

回復 stillfish00

我下載的檔案裡是沒有設資料驗證清單，所以只要設定了資料驗證清單就執行無誤 ...
ML089 發表於 2013-11-7 11:06

理論上，只要在第一次設定了 "資料驗證清單"，並同時予以儲存，
關閉後再行觀察 "資料驗證清單" 已否業已存在，便知答案了。

作者: c_c_lai 時間: 2013-11-7 15:23

回復 35# ML089
回復 34# stillfish00
最簡潔快速、正確的解決方式如下，請修正：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim StrVdFml As String
On Error Resume Next
CellValidation ' 2013/11/7 增修 by stillfish00
StrVdFml = Replace(ActiveCell.Validation.Formula1, "=", "")
ActiveCell.Validation.InCellDropdown = False
On Error GoTo 0

複製代碼

作者: c_c_lai 時間: 2013-11-7 15:25

回復 36# stillfish00

Sub CellValidation() ' stillfish00 提供
With Sheets("Sheet2").[A2:A25].Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=Sheet1!$A$3:$A$20"
End With
End Sub

複製代碼

作者: ML089 時間: 2013-11-7 15:33

回復 38# c_c_lai

我下載你提供的檔案，Sheet2!A欄是沒有設定資料驗證清單，
所以 Worksheet_SelectionChange 判斷所選儲存格沒有設定資料驗證清單，就沒有作用

重設資料驗證清單儲存，在打開仍會存在。

程式中有下面這一段，但都沒有被呼叫，我也沒有仔細研究，所以一直以為VBA應該會將  資料驗證清單自動設好。
Sub CellValidation()    '  stillfish00 提供
With Sheets("Sheet2").[A2:A25].Validation
      .Delete
      .Add Type:=xlValidateList, Formula1:="=Sheet1!$A$3:$A$20"
End With
End Sub

以這例子可能一開始是使用  資料驗證清單，後來改用COMBOBOX來替代資料驗證清單，
讓我一直搞不清楚(VBA功力太弱)，既然使用COMBOBOX來當驗證清單，為何又要設定儲存格的  資料驗證清單，
感覺2個相同功能互疊在一起。
總之謝謝你一直耐心回覆我的問題，感謝

作者: GBKEE 時間: 2013-11-7 15:49

看了 ML089, c_c_lai,stillfish00三位的討論,關鍵是舊版Excel開啟新版檔案的控制項是無法使用.
我是2003版就常遇這狀況,解決方式
將新版檔案的資料複製(文字,格式,不含控制項)在一新增舊版活頁簿,(舊版控制項再一一新增上去,程式碼複製到VBA裡)

作者: c_c_lai 時間: 2013-11-7 15:56

回復 41# ML089
別客氣，大家互相學習。
看了你與 stillfish00 大大的訊息，亦讓我從中學習到很多我沒想到的突發狀況，
因我不是原創者 (av8d 大大) 所以只單純的套用現成的程式 (因原本只是協助 av8d 解決他的提問)，
竟沒想到會沿生出 ActiveCell.Validation.Formula1 的異狀。剛剛從外頭回到家看了你們的對談內容，
不禁亦想到為何不去使用由 stillfish00 大大提供的 CellValidation() ，在 StrVdFml 判斷之前加入它，
這麼一來管它原本的 ActiveCell.Validation.Formula1 有否資料，於 CellValidation() 執行後，
它便自動把 A2:A25 的資料驗證資料一一寫入 "=工作表1!$A$3:$A$20" 連結參造字串。
如此一來接下來的作業便迎刃而解了。

作者: c_c_lai 時間: 2013-11-7 17:05

回復 41# ML089
A2:A25 的欄位與 ComboBox1:ComboBox 是相互依存的。
欄位資料的資料驗證，一般應用於測試資料及驗證，以確認運作正確無誤。
此處之運用是使用 "事先設定的清單範圍 ("=Sheet1!$A$3:$A$20")" 做為
資料驗證的內存值，提供給後續處理之 ComboBox1 的 LinkedCell、以及
ListFillRange 資訊。 LinkedCell對應的是使用者目前點選的 A 欄位，
譬如目前是位於 A4 欄位，則此處 LinkedCell 對應的內容值為 $A$4，
同時 ListFillRange 對應的內容值則為 Sheet1!$A$3:$A$20。
這時使用者如點選 "下拉符號"，ComboBox1 則隨即展開 ListFillRange
對應的欄值 (A2:A25 的欄值內容) 清單供使用者點選。
點選完成後隨即自動將點選欄值寫入到 LinkedCell 對應的欄位內。
總結、應用 CellValidation() 將 "=Sheet1!$A$3:$A$20" 同時寫入至
A2:A25 的 .Validation.Formula1，然後再利用此資料驗證一一帶入到
每次執行的 ComboBox1 中。
(如此、A2:A25 以及 ComboBox1 都可不用事先去做任何設定值的動作，
而全部交由 CellValidation() 去處理、及給值。)
如此說明是否對你有所助益？

作者: ML089 時間: 2013-11-7 18:03

回復 44# c_c_lai

謝謝說明，已經了解問題所在

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)