求大师改一下三级下拉菜单代码

1565785678

Private Sub Worksheet_Change(ByVal Target As Range)
    m = Target.Row
     Range("e" & m).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=类型"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
        Range("F" & m).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=indirect(e12)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
      Range("g" & m).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=indirect(f12)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
End Sub
这是录制宏得到的代码，想把"=indirect(e12)", Formula1:="=indirect(f12)"中的e12  和 f12改成变量，但是改成indirect(“f”&m)就报错，求大师帮忙改一下。谢谢了

Excel_muke

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim m As Long
   
3.     m = Target.Row
   
4. 
   
5.     ' 对 E 列的下拉列表进行验证设置
   
6.     With Cells(m, 5).Validation
   
7.         .Delete
   
8.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
   
9.         xlBetween, Formula1:="=类型"
   
10.         .IgnoreBlank = True
    
11.         .InCellDropdown = True
    
12.         .InputTitle = ""
    
13.         .ErrorTitle = ""
    
14.         .InputMessage = ""
    
15.         .ErrorMessage = ""
    
16.         .IMEMode = xlIMEModeNoControl
    
17.         .ShowInput = True
    
18.         .ShowError = True
    
19.     End With
    
20. 
    
21.     ' 对 F 列的下拉列表进行验证设置
    
22.     With Cells(m, 6).Validation
    
23.         .Delete
    
24.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    
25.         xlBetween, Formula1:="=INDIRECT(E" & m & ")"
    
26.         .IgnoreBlank = True
    
27.         .InCellDropdown = True
    
28.         .InputTitle = ""
    
29.         .ErrorTitle = ""
    
30.         .InputMessage = ""
    
31.         .ErrorMessage = ""
    
32.         .IMEMode = xlIMEModeNoControl
    
33.         .ShowInput = True
    
34.         .ShowError = True
    
35.     End With
    
36. 
    
37.     ' 对 G 列的下拉列表进行验证设置
    
38.     With Cells(m, 7).Validation
    
39.         .Delete
    
40.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    
41.         xlBetween, Formula1:="=INDIRECT(F" & m & ")"
    
42.         .IgnoreBlank = True
    
43.         .InCellDropdown = True
    
44.         .InputTitle = ""
    
45.         .ErrorTitle = ""
    
46.         .InputMessage = ""
    
47.         .ErrorMessage = ""
    
48.         .IMEMode = xlIMEModeNoControl
    
49.         .ShowInput = True
    
50.         .ShowError = True
    
51.     End With
    
52. End Sub
    

复制代码

1565785678

Excel_muke 发表于 2024-11-11 17:36

谢谢，正是想要的结果。

1565785678

Excel_muke 发表于 2024-11-11 17:36

您好，在公司电脑上可以用，但是在我的电脑上就显示出错。这个是什么问题呀？

Excel_muke

1565785678 发表于 2024-11-12 09:30
您好，在公司电脑上可以用，但是在我的电脑上就显示出错。这个是什么问题呀？

Excel版本不同？还是命名管理器定义了相同命名的名称？没见过