时间戳

首页 > 解决方案 > 如何禁用工作表上的按钮？

excel - 如何禁用工作表上的按钮？

问题描述

我正在尝试禁用工作表上的按钮。

我可以让它不可见，但我宁愿把它放在屏幕上，但不能点击和变灰，所以它显然是不可点击的。

这是我的按钮脚本：

Sub LinkFile()
Call Module1.setup
Dim btn1 As Button
Dim t As Range

Set t = Range(Cells(3, "G"), Cells(4, "I"))

Set btn1 = ACImport.Buttons.Add(Left:=t.Left, Top:=t.Top, Width:=t.Width, Height:=t.Height)
btn1.Name = "Button1"
btn1.OnAction = "FileSearch"    ' MySub is executed when btn is clicked
btn1.Caption = "GO!"
End Sub

标签： excelvbabutton

解决方案

一种解决方法可能是“禁用”这样的按钮

Sub DisableBtn()

    Dim btn As Button
    Dim wks As Worksheet
    Set wks = ActiveSheet ' replace with the sheetname the button is on

    Set btn = wks.Buttons("Button1")
    btn.Font.ColorIndex = 15
    btn.Enabled = False

End Sub

但是您仍然可以单击该按钮，因此您需要进行Filesearch类似的修改

Sub FileSearch()

    Dim btn As Button
    Dim wks As Worksheet

    Set wks = ActiveSheet
    Set btn = wks.Buttons("Button1")

    If btn.Enabled Then
        ' Your action here
        MsgBox "FileSearch"
    End If
End Sub

要再次“启用”按钮，请使用此代码

Sub Enablebtn()
    Dim btn As Button
    Dim wks As Worksheet
    Set wks = ActiveSheet ' replace with the sheetname the button is on

    Set btn = wks.Buttons("Button1")
    btn.Font.ColorIndex = 1
    btn.Enabled = True

End Sub

如前所述，这是一种解决方法，仍然可以单击灰色按钮:-(

推荐阅读