ActiveX命令按钮,如果输入值,则在单元格旁边取消隐藏;如果单元格为空,则隐藏 [英] ActiveX Command Button that unhides next to a Cell if a value is entered, and hides if the cell is empty
问题描述
我有80行,用户可以在其中在Ward列下输入预定值.这会取消隐藏它旁边的按钮.单击它后,它将清空相邻的值,并根据原始值对另一张工作表中的特定单元格进行递增(+1).
I have 80 rows where the user can enter a predetermined value under column Ward. This unhides a button next to it. Upon clicking it, it empties the adjacent value and increments (+1) a particular cell in another sheet depending on the original value.
当前,我在Ward单元格旁边有80个ActiveX按钮,根据Ward单元格的值进行隐藏/取消隐藏.我注意到添加更多按钮会使电子表格的运行速度变慢,原因是我拥有的If If语句数量庞大.
Currently, I have 80 ActiveX buttons next to the Ward cells that hides/unhides depending on the value of the Ward cells. I've noticed that adding more buttons slows down the spreadsheet because of the sheer volume of If Then statements I have.
If Range("F8").Value = 0 Then
Sheets("Admissions").EDAdmit1.Visible = False
Else
Sheets("Admissions").EDAdmit1.Visible = True
End If
If Range("L8").Value = 0 Then
Sheets("Admissions").ElecAdmit1.Visible = False
Else
Sheets("Admissions").ElecAdmit1.Visible = True
End If
If Range("F9").Value = 0 Then
Sheets("Admissions").EDAdmit2.Visible = False
Else
Sheets("Admissions").EDAdmit2.Visible = True
End If
If Range("L9").Value = 0 Then
Sheets("Admissions").ElecAdmit2.Visible = False
Else
Sheets("Admissions").ElecAdmit2.Visible = True
End If
..依此类推.
更不用说每次单击按钮时都有的If If语句.
Not to mention the If Then statements I have for every button click.
Private Sub EDAdmit1_Click()
If Range("F8") = "ICU" Then
Worksheets("Overview").Range("AD11").Value = Worksheets("Overview").Range("AD11") + 1
ElseIf Range("F8") = "HDU" Then
Worksheets("Overview").Range("AF11").Value = Worksheets("Overview").Range("AF11") + 1
ElseIf Range("F8") = "DPU" Or Range("F8") = "Other" Then
Else
Col = WorksheetFunction.VLookup(Range("F8"), Range("U1:V27"), 2)
Worksheets("Overview").Range(Col).Value = Worksheets("Overview").Range(Col).Value + 1
End If
Range("F8").ClearContents
End Sub
是否有更有效的方法?
入场名单:
推荐答案
您可以考虑在Ward选项旁边的单元格中使用允许"超链接:这样,您只需要一个处理程序(工作表模块中的Worksheet_FollowHyperlink).请注意,此处需要使用Insert >> Hyperlink
而不是HYPERLINK()公式类型的链接(因为基于公式的链接不会触发FollowHyperlink
事件).
You could consider using "admit" hyperlinks in the cells next to the Ward selections: that way you only need one handler (Worksheet_FollowHyperlink in the worksheet module). Note you need to use Insert >> Hyperlink
and not the HYPERLINK() formula-type links here (because formula-based links don't trigger the FollowHyperlink
event).
您可以放弃隐藏/显示代码,而是使用条件格式更改链接字体的颜色,以在未选择Ward时隐藏链接.如果用户单击其中一个隐藏链接,那么您什么也做不了.
You can ditch the hide/show code and instead use conditional formatting to change the link font color to hide the links when there's no Ward selected. If a user clicks on one of the hidden links then you can just do nothing.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim rngSrc As Range, addr, ward
Set rngSrc = Target.Range '<< the cell with the link
ward = rngSrc.Offset(0, 1).Value '<< cell with Ward
'only do anything if a ward is selected
If Len(ward) > 0 Then
'find the cell to update
Select Case ward
Case "ICU"
addr = "AD11"
Case "HDU"
addr = "AF11"
Case "DPU", "Other"
addr = ""
Case Else
addr = Application.VLookup(ward, Me.Range("U1:V27"), 2, False)
End Select
'if we have a cell to update then
If Len(addr) > 0 Then
With Worksheets("Overview").Range(addr)
.Value = .Value + 1
End With
End If
rngSrc.Offset(0, 1).ClearContents
End If
rngSrc.Select '<< select the clicked-on link cell
' (in case the link points elsewhere)
End Sub
这篇关于ActiveX命令按钮,如果输入值,则在单元格旁边取消隐藏;如果单元格为空,则隐藏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!