隐藏/取消隐藏命令按钮,具体取决于单元格值
你好,
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