隐藏/取消隐藏命令按钮，具体取决于单元格值 [英] Hide/Unhide Command button depending on cell value
问题描述
你好，
Hello,
我正在寻找Command Button的帮助。
I'm looking for help on Command Button.
如何根据单元格值隐藏/取消隐藏？
How can it hide/unhide depending on cell value by atomatically?
以下是我要改变的形式B2（已过期，有效和提醒的状态）并希望仅对提交状态进行更改以使用Comm进行更改和每个单元格中的按钮，（FYI - F2是日期值）：
Below is a formart that I want to change on B2 (status of Expired, Active & Reminder) and wish to apply on Reminder status only to change with Command Button in every cell, (FYI - F2 is a date value) :
= IF（F2< = TODAY（），" EXPIRED"，IF（AND（F2-TODAY（）> 0，F2-TODAY（）> = 10）， ""ACTIVE"，"REMINDER"））
=IF(F2<=TODAY(), "EXPIRED", IF(AND(F2-TODAY()>0, F2-TODAY()>=10), "ACTIVE", "REMINDER"))
以下是标准模块中的代码：
Below is the code in a standard module :
Sub CreateButtons（）
Sub CreateButtons()
Dim i As Long
Dim shp As Object
; Dim dblLeft As Double
Dim dblTop As Double
Dim dblWidth As Double
Dim dblHeight As Double
With表格（"Sheet1"）
dblLeft = .Columns（" B：B"）。Left '所有按钮具有相同的左侧位置
dblWidth = .Columns（" B：B"）。Width '所有按钮具有相同的宽度
For i = 2 To 20 ; '从第2行开始并完成第20行。
&n BSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; dblHeight = .Rows（i）.Height '将高度设置为行的高度
dblTop = .Rows（i）.Top '设置行的顶部
设置shp = .Buttons.Add（dblLeft，dblTop，dblWidth，dblHeight）
shp.OnAction =" IdentifySelected"
shp.Characters.Text =" Reminder"
接下来我是
结束与$
结束子
Dim i As Long
Dim shp As Object
Dim dblLeft As Double
Dim dblTop As Double
Dim dblWidth As Double
Dim dblHeight As Double
With Sheets("Sheet1")
dblLeft = .Columns("B:B").Left 'All buttons have same Left position
dblWidth = .Columns("B:B").Width 'All buttons have same Width
For i = 2 To 20 'Starts on row 2 and finishes row 20
dblHeight = .Rows(i).Height 'Set Height to height of row
dblTop = .Rows(i).Top 'Set Top top of row
Set shp = .Buttons.Add(dblLeft, dblTop, dblWidth, dblHeight)
shp.OnAction = "IdentifySelected"
shp.Characters.Text = "Reminder"
Next i
End With
End Sub
Sub IdentifySelected（）
'注意：该按钮将始终位于活动工作表上。
Dim strButtonName
Dim lngRow As Long
strButtonName = ActiveSheet.Shapes（Application.Caller）.Name
lngRow = ActiveSheet.Shapes（strButtonName）.TopLeftCell.Row
MsgBox" Button is on row" &安培; lngRow
结束子
Sub IdentifySelected()
'NOTE: The button will always be on the active sheet
Dim strButtonName
Dim lngRow As Long
strButtonName = ActiveSheet.Shapes(Application.Caller).Name
lngRow = ActiveSheet.Shapes(strButtonName).TopLeftCell.Row
MsgBox "Button is on row " & lngRow
End Sub
推荐答案
您可以使用这样的代码，这将隐藏公式中的任何按钮你发布的（我在G栏中假设）已过期：
You can use code like this, which will hide any button where the formula you posted (I assumed, in column G) is EXPIRED:
1）复制此代码。
2 ）右键单击感兴趣的工作表标签。
3）选择"查看代码"
4）将代码粘贴到出现的窗口中。
5）将文件另存为启用宏的.xlsm文件。
6）根据需要进行更改
1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed
Private Sub Worksheet_Calculate（）
&NBSP; Dim shp As Shape
&NBSP; Dim lngr As Long
&NBSP;每个shp In Me.Shapes
&NBSP; &NBSP; &NBSP; lngr = shp.TopLeftCell.Row
&NBSP; &NBSP; &NBSP; shp.TextFrame.Characters.Text = Me.Cells（lngr，" G"）。值
&NBSP; &NBSP; &NBSP; shp.Visible = msoTrue
&NBSP; &NBSP; &NBSP;如果Me.Cells（lngr，"G"）。Value =" EXPIRED"然后shp.Visible = msoFalse
&NBSP;下一个shp
结束子
Private Sub Worksheet_Calculate()
Dim shp As Shape
Dim lngr As Long
For Each shp In Me.Shapes
lngr = shp.TopLeftCell.Row
shp.TextFrame.Characters.Text = Me.Cells(lngr, "G").Value
shp.Visible = msoTrue
If Me.Cells(lngr, "G").Value = "EXPIRED" Then shp.Visible = msoFalse
Next shp
End Sub
这篇关于隐藏/取消隐藏命令按钮，具体取决于单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
