validation - Excel 数据验证引用电子表格位置与静态列表

Question

我在某些 Excel 数据验证方面遇到了问题。在一张纸上，我有一个值列表，其中包含填充验证下拉列表的列表，如下所示:

A1 |1.)   New ItemA2 |2.)  MiscellaneousA3 |3.)  Change OrderA4 |4.)  Choose the item being broken out:A5 |          Caulk NP-1A6 |          Safety signsA7 |          Warning linesA8 |          Snow fenceA9 |          Drain Lead 4 lb 30"x30" A10 |          Cant StripA11 |          Screws 1 5/8" A12 |          SS Sheet Metal 24 gaugeA13 |          Sheet Metal Aluminum 040 mill finish A14 |          Sheet Metal Aluminum .050 mill finishA15 |          Termination BarA16 |          Lead Boots 2"A17 |          Lead Boots 3" A18 |          Modified Bitumen Smooth APPA19 |          TREMCO ELS MasticA20 |          TREMCO Polyroof SFA21 |          TREMCO Base sheet fastenersA22 |          TREMCO BURmastic CompositeA23 |          TREMCO PowerPly StandardA24 |          TREMCO BURmastic SFA25 |          TREMCO PowerPly HD Base SheetA26 |          TREMCO PowerPly Standard FRA27 |          TREMCO Burmesh 6"A28 |          TREMCO Site visitsA29 |          TREMCO Reglet SealantA30 |          TREMCO WB PrimerA31 |          TREMCO Ice CoatingA32 |          TREMCO Tremflash tapeA33 |          TREMCO WarrantyA34 |          1/4" x 1 1/2" drive pinsA35 |          SS Roof Nails 1 1/4"A36 |          FreightA37 |          Auto FuelA38 |          PA Direct Labor SupervisorA39 |          PA Direct Labor Sheet MetalA40 |          PA Direct Labor Roof CoatingA41 |          Equipment Crane 45 tonA42 |          Equipment Crane 70 tonA43 |          Platform Hoist R&G 400 28'A44 |          Sqeegies 24" NotchedA45 |          DumpstersA46 |          Porta JohnA47 |          PermitA48 |          Subcontractor RK Hydro Vac A49 |          Subcontractor Roofing ICGA50 |          Subcontractor Lightning ProtectionA51 |          MiscA52 |          Subtotal

When I have another sheet reference this list, the validation works properly. However, I have a VBA macro that at some point copies this other sheet into a workbook by itself to be emailed. For this and other reasons, I need the validation drop-down list on this column to not be dependent on the other sheet, so I have the macro loop through all of the cells and create a validation string that looks like this:

  1.)   New Item,2.)  Miscellaneous,3.)  Change Order,4.)  Choose the item being broken
 out:,          Caulk NP-1 ,          Safety signs,         Warning lines

As you can see, it's the exact same list, but it's a text string, with each selection separated by commas. It works almost like I need it to, but there is a problem - The 10 leading spaces are not working like they should in the second scenario. I have them there so that those selections are indented in the drop-down box to make it more intuitive. The 10 leading spaces are in the actual "validation list formula", but don't show up when I click the drop-down or make a selection!

Any ideas?

EDIT:As requested, here's the actual code:

Range("A1").Value = "1.)   New Item"
Range("A2").Value = "2.)  Miscellaneous"
Range("A3").Value = "3.)  Change Order"
Range("A4").Value = "4.)  Choose the item being broken out:"
Range("A5:A350").Formula = "=""          ""&INDIRECT(""'Buy Out'!B""&MATCH(""Description"",'Buy Out'!$B:$B,0)+ROW()-3)"
Application.Calculate

' build ValidationList string for later use (this will also have to happen when misc section isn't built, so this isn't the best place for it)
Range("A1").Select
ValidationList = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Row = 350
    If ActiveCell.Value = "          Subtotal" Or ActiveCell.Offset(3, 0).Value = "          Subtotals" Then 'end of the loop
        Exit Do
    End If

    If ActiveCell.Value <> "" Then
        ValidationList = ValidationList & "," & vbTab & ActiveCell.Value
    End If
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Row = 349 Then 'this shouldn't ever happen
        ValidationListMessedUp = True
        'MsgBox ("There appears to be a problem creating the drop-down list for the ""Addon Category/Item to Break Out"".")
    End If
Loop


'back to buy out and populate validation as dynamic formula
Sheets("Buy Out").Select
Cells.Find(What:="Addon Category/Item to Break Out", after:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate '* EH and message? "Is this job on the most recent MBO template? It does not appear to have the words "Release #" anywhere on the Buy Out tab

With Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(TotalRow - ActiveCell.Row - 2, 0)).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:= _
        "=INDIRECT(""reference!$A$1:$A$""&IFERROR(MATCH(""          Subtotal"",reference!$A:$A,0)-1,IFERROR(MATCH(""          Grand Total"",reference!$A:$A,0)-1,MATCH(""          Subtotals"",reference!$A:$A,0)-3)))"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

因此，使用间接公式填充验证的代码的最后一部分是正常工作的。稍后在代码中，我使用字符串填充“静态”验证列表。这是另一部分:

    With Range(ActiveCell.Offset(MiscStartRow - ActiveCell.Row + 1, 0), ActiveCell.Offset(TotalRow - ActiveCell.Row + 5, 0)).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ValidationList
    .IgnoreBlank = False
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

非常感谢帮忙。这真是令人沮丧。

Answer 1

使用 vbTab
例如

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

快照



希望这是你想要的？

跟进

使用上述方法有一个缺点。 DVlist 不能超过 255 个字符。

替代品

将列表复制到工作表的最左列(Column XFD (excel 2007/2010) 或 Column IV (Excel 2003))，使其隐藏，然后在 DV 中使用