时间戳

首页 > 解决方案 > VBA 下拉列表

excel - VBA 下拉列表

问题描述

我有Sheet1数据

Column J 
Name of header AccNo

在Sheet2我制作下拉列表

891-0001 PARKING EXPENSES
891-0002 AIRFARE EXPENSES

我想从中显示下拉列表Sheet1，Sheet2但仅显示结果891-0001。请帮助宏代码。

标签： excelvbadropdown

解决方案

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$1" Then     'Cell J1 is the dropdown list
    On Error GoTo errorhandler
    Application.EnableEvents = False
    Selection.Validation.Delete
    Target.Value = Left(Target.Value, 8)
    Selection.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=List"   'Define selection as List name
 End If

ErrorExit:
Application.EnableEvents = True

Exit Sub
errorhandler:
Resume ErrorExit

End Sub

推荐阅读