返回列表 上一主題 發帖

一個清單驗證問題

一個清單驗證問題

請問各位先進
我下列程式碼有哪裡錯誤嗎?
為何我輸入清單以外的文字
無法出現錯誤訊息

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & bb
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

回復 1# dafa
  1. Sub Ex()
  2.    Dim BB(3), CC
  3.    BB(0) = 1
  4.    BB(1) = 3
  5.    BB(2) = 4
  6.    BB(3) = 5
  7.    'CC = Join(BB, ",")          '陣列
  8.    CC = "=" & [A1:A5].Address   '儲存格
  9.    With Selection.Validation
  10.         .Delete
  11.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  12.         xlBetween, Formula1:=CC
  13.         .IgnoreBlank = True
  14.         .InCellDropdown = True
  15.         .InputTitle = ""
  16.         .ErrorTitle = ""
  17.         .InputMessage = ""
  18.         .ErrorMessage = ""
  19.         .ShowInput = True
  20.         .ShowError = True
  21.     End With
  22. End Sub
複製代碼

TOP

回復 2# GBKEE


    感謝G大的回覆
我去試試看

TOP

回復 2# GBKEE


    再請問G大一個問題
陣列BB(30)內30可以是變數嗎?

TOP

回復 4# dafa
  1. Option Explicit
  2. Sub Ex()
  3.     Dim BB(5 To 30), i As Integer
  4.     For i = 5 To 30
  5.         BB(i) = i + 2
  6.     Next
  7.     MsgBox Join(BB, ",")
  8. End Sub
複製代碼

TOP

回復 5# GBKEE

請問G大我執行到這一段會出現錯誤
Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & bb
會出現錯誤

    Sub 清單明細()
If ActiveCell.Cells.Column <> "4" Then Exit Sub
aa = ActiveCell
If aa = "" Then Exit Sub

g = Worksheets("清單明細").Cells(65536, 1).End(xlUp).Row
For i = 1 To g
If Worksheets("清單明細").Cells(i, 1) = aa Then
b = 0
    For a = 4 To 34
        kk(b) = Worksheets("清單明細").Cells(i, a)
        b = b + 1
    Next a
bb = Join(kk, ",")

'bb = Worksheets("清單明細").Cells(i, 3)

ActiveCell.Cells(, 4).Select
Exit For
End If
Next i
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & bb
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With


End Sub

TOP

回復 5# GBKEE

請問G大
   會不會是因為我的陣列資料內有空值
而發生的錯誤

TOP

回復 7# dafa
xlBetween, Formula1:="=" & bb 改成  xlBetween, Formula1:=bb

TOP

回復 8# GBKEE


    感謝G大的熱心
幫我解決難題

TOP

回復 8# GBKEE


    再請問G大一下
為何我的警告訊息不會出現


Sub 清單明細()

Dim bb, kk(30), aa
If ActiveCell.Cells.Column <> "4" Then Exit Sub

aa = ActiveCell
If aa = "Account Code " Then Exit Sub
If aa = "" Then Exit Sub
g = Worksheets("清單明細").Cells(65536, 2).End(xlUp).Row
For i = 1 To g
e = Worksheets("清單明細").Cells(i, 2)
If Worksheets("清單明細").Cells(i, 2) = aa Then

b = 0
j = Worksheets("清單明細").Cells(i, 34).End(xlToLeft).Column
    For a = 3 To j
        kk(b) = Worksheets("清單明細").Cells(i, a)
        b = b + 1
    Next a
bb = Join(kk, ",")
Exit For

End If
Next i
pp = Worksheets("清單明細").Cells(i, 3)
MsgBox pp, , "Account_Code說明"

ActiveCell.Cells(, 4).Select
ActiveCell = ""
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=bb
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "You must enter a number from five to ten"
        .ShowInput = True
        .ShowError = True
    End With
Set kk(30) = Nothing
ActiveCell.Offset(, -5) = Date
ActiveCell.Offset(, 1) = Environ("UserName")
End Sub

TOP

        靜思自在 : 閒人無樂趣,忙人無是非。
返回列表 上一主題