编译-语法错误:使用If语句切换选择过滤器时 [英] Compile - Syntax Error: When toggling select filter with If Statement

问题描述

又是我-最终我会更好地了解这种语言的.

it's me again - I'll get to know this language better eventually.

基本上-我有一个很大的数据表，其中包含自动过滤功能-范围"$ B $ 5:$ Z $ 1697"

Basically - I have a big table of data that has autofilter on - range "$B$5:$Z$1697"

但是，我想在R列上添加或关闭其他过滤器.

However, there is an additional filter on the R column that I want to be toggled on or off.

因此，我需要一个If语句，该语句指示在打开其他过滤器时，将其删除，但是，如果在按下按钮时该过滤器未处于打开状态，请应用它.

Therefore I need an If statement that says when the additional filter is on, remove, whereas, if the filter is not on at the time you press the button - apply it.

我已经试过了，看了更多我想承认的视频.但是，一定有一些我忽略或不了解的东西.

I've played around with this and watched more videos that I care to admit. However, there must be something I'm overlooking or don't understand.

代码会一直处理到其他行，然后返回:

The code works up until the Else line, which returns:

编译错误，语法错误".

"Compile Error, Syntax Error".

任何人都可以解释发生了什么事吗?

Can anyone explain what's happening?

If Sheets(4).Range("$B$5:$Z$1697").AutoFilter(Field:=17, Criteria1:="=") = True Then
    'If there specific filter on column R is on then

    Sheets(4).Range("$B$5:$Z$1697").AutoFilter Field:=17
    'Turn off that filter.

Else: Sheets(4).Range("$B$5:$Z$1697").AutoFilter(Field:=17, Criteria1:="=")
'Else, if the filter is off, turn it on.

End If

End Sub

我已更正代码，将此ELSE行修改为此

I have corrected the code, amending this ELSE line to this

Else: Sheets(4).Range("$B$5:$Z$1697").AutoFilter(Field:=17, Criteria1:="=") = True

但是，当我现在运行此按钮时，这意味着它可以通过按一下按钮将过滤器打开，然后再次关闭.我如何使它一次改变.

However, when I run this now it means that it turns the filter On and then Off again with one push of the button. How do I make it so it onl makes on change at a time.

即如果按下按钮时过滤器已打开，则仅将其关闭.

I.e. if the filter is on when the button is pressed it ONLY turns it off.

反之亦然

推荐答案

打开/关闭过滤器的最简单方法是使用ActiveX Toggle button.单击(启用)切换按钮时，将应用过滤器，再次单击(禁用)时，将删除过滤器.更改toggle button和Criteria1的名称以满足您的需求.

The easiest way to toggle a filter on/off is to use an ActiveX Toggle button. When the toggle button is clicked (enabled) your filter will be applied, when clicked again (disabled) your filter is removed. Change the name of the toggle button and Criteria1 to meets your needs.

Private Sub ToggleButton1_Click()
    'when selected(enabled) the filter for Col "Q" will be enabled 
    If ToggleButton1.Value = True Then
        Sheets(4).Range("$B$5:$Z$1697").AutoFilter Field:=17, Criteria1:="2"
    Else
    'when selected again(disabled) the filter for Col "Q" will be Disabled 
        Sheets(4).Range("$B$5:$Z$1697").AutoFilter Field:=17
    End If
End Sub

这篇关于编译-语法错误:使用If语句切换选择过滤器时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！