2.Excel_菜单的事件更新程序

1，主级菜单如果改变（c3改变），则次级菜单更新，规则是根据主菜单找到次级菜单名并生成序列；

2，次级菜单如果变动，则先清除列标题包括菜单代码，清除单笔或批量查询的条件数据，清除之前查询的数据；因为接口的头数据都是一样的，所以头列名直接从配置表读取，并生成批量查询的标题，并组合出条件脚本，根据次级菜单选择的名字，从行表找到行字段，并生成批量查询的标题，并组合出条件脚本；通过前面生成的条件和配置表的关键字段，生成sql查询脚本，并保存至配置表。

 

 

源代码

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Dim xulie2 As String
Dim qh_tiaojian_01, sqldaima_t_01, sqldaima_t_02, qh_tiaojian_02, qh_xulie_01 As String
Dim qhn01 As Long
Dim qhn02, qhn03, qhn04, qhn05 As Long
Dim qhi01, qhi02, qhi03 As Long

With Sheets("中台接口查询") '生成接口序列
If Target.Column = 3 And Target.row = 3 Then
    .Range("c4").Validation.Delete
    .Range("c4").ClearContents
    If Range("c3").Value <> "" Then  '过滤
        qhn01 = Sheets("中台接口头表").Range("c1000000").End(xlUp).row
        For qhi01 = 3 To qhn01
            If Sheets("中台接口头表").Cells(qhi01, 3).Value = .Range("c3").Value Then
                xulie2 = xulie2 & Sheets("中台接口头表").Cells(qhi01, 6) & ","
            End If
        Next
        With Sheets("中台接口查询").Range("c4").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=xulie2
        End With
    End If
Else
    If Target.Column = 3 And Target.row = 4 Then
    
        .[d7:xfd7].ClearContents   '清除条件标题
        .[d8:xfd8].ClearContents    '清除条件代码
        .[c10:xfd10].ClearContents    '清除字段标题
        .[d11:bz1000000].ClearContents    '清除查询数据
                
       qhn02 = Sheets("中台接口头表").Range("f1000000").End(xlUp).row
       
       For qhi01 = 3 To qhn02
            If Sheets("中台接口头表").Cells(qhi01, 6).Value = Sheets("中台接口查询").Range("c4").Value Then
            
            qhn05 = 5
            .Cells(7, 4) = "序号"
            .Cells(10, 4) = "序号"
            
            With Sheets("配置表")
            qhn04 = .Range("l1000000").End(xlUp).row
            For qhi03 = 3 To qhn04   '生成头字段和生成表的字段名称
                qh_tiaojian_02 = qh_tiaojian_02 & .Range("f5") & "." & .Cells(qhi03, 12).Value & " " & .Cells(qhi03, 11).Value & "," & vbCrLf
                
                qh_xulie_01 = qh_xulie_01 & .Cells(qhi03, 11).Value & "," '给头表条件加序列
                
                With Sheets("中台接口查询")
                    .Cells(7, qhn05) = Sheets("配置表").Cells(qhi03, 11).Value
                    .Cells(8, qhn05) = Sheets("配置表").Range("f5") & "." & Sheets("配置表").Cells(qhi03, 12).Value
                    .Cells(10, qhn05) = Sheets("配置表").Cells(qhi03, 11).Value
                    qhn05 = qhn05 + 1
                End With
            Next
            End With

                qhn03 = Sheets("中台接口行表").Range("f1000000").End(xlUp).row
                Sheets("配置表").Range("f16") = Sheets("中台接口头表").Cells(qhi01, 5).Value  '获取接口名
                For qhi02 = 2 To qhn03 '生成行字段和生成表的字段名称
                    If Sheets("中台接口行表").Cells(qhi02, 2).Value = Sheets("中台接口头表").Cells(qhi01, 4).Value Then
                        If Right(Sheets("中台接口行表").Cells(qhi02, 3).Value, 2) <> "映射" Then  '屏蔽掉映射字段
                        
                            qh_tiaojian_01 = qh_tiaojian_01 & Sheets("配置表").Range("f6") & "." & Sheets("中台接口行表").Cells(qhi02, 5).Value & " " & Sheets("中台接口行表").Cells(qhi02, 3).Value & "," & vbCrLf
                            
                            qh_xulie_01 = qh_xulie_01 & Sheets("中台接口行表").Cells(qhi02, 3).Value & ","  '给行表条件加序列
                            
                            With Sheets("中台接口查询")
                                .Cells(7, qhn05) = Sheets("中台接口行表").Cells(qhi02, 3).Value
                                .Cells(8, qhn05) = Sheets("配置表").Range("f6") & "." & Sheets("中台接口行表").Cells(qhi02, 5).Value
                                .Cells(10, qhn05) = Sheets("中台接口行表").Cells(qhi02, 3).Value
                                
                            End With
                            qhn05 = qhn05 + 1
                        End If
                    End If
                Next
                
                With Sheets("配置表")  '生成最终主次查询脚本
                sqldaima_t_01 = .Range("i3") & " " & _
                                vbCrLf & qh_tiaojian_02 & _
                                Left(qh_tiaojian_01, Len(qh_tiaojian_01) - 3) & " " & _
                                vbCrLf & .Range("i4") & " " & _
                                vbCrLf & .Range("f3") & " " & .Range("f5") & "," & _
                                vbCrLf & .Range("f4") & " " & .Range("f6") & " " & _
                                vbCrLf & .Range("i5") & " " & _
                                vbCrLf & .Range("f5") & "." & .Range("f7") & " = " & .Range("f6") & "." & .Range("f8") & _
                                vbCrLf & .Range("i6") & " " & .Range("f5") & "." & .Range("f9") & " = " & "'" & .Range("f16") & "'"
                                
                sqldaima_t_02 = .Range("i3") & " " & _
                                vbCrLf & "count(*) " & _
                                vbCrLf & .Range("i4") & " " & _
                                vbCrLf & .Range("f3") & " " & .Range("f5") & "," & _
                                vbCrLf & .Range("f4") & " " & .Range("f6") & " " & _
                                vbCrLf & .Range("i5") & " " & _
                                vbCrLf & .Range("f5") & "." & .Range("f7") & " = " & .Range("f6") & "." & .Range("f8") & _
                                vbCrLf & .Range("i6") & " " & .Range("f5") & "." & .Range("f9") & " = " & "'" & .Range("f16") & "'"
                End With
                
                With Sheets("中台接口查询").Range("c10").Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=qh_xulie_01
                End With
                
                .Rows(8).EntireRow.Hidden = True
                Sheets("配置表").Range("f50") = sqldaima_t_01 '调试点 存储查询脚本（主SQL）
                Sheets("配置表").Range("e50") = sqldaima_t_02 '调试点 存储查询脚本（次SQL）
            End If
       Next
    
    End If
End If
End With
Application.ScreenUpdating = True
End Sub