将代码用于不同的范围
情况 你好，所以 我有一个情况，我有以下代码，我想使用这些代码，而不是其他范围。但是在这种情况下，当情况范围变化时，清晰的内容和OTEHR定义的范围也会改变。我发现我必须为其他范围编写重复代码。我是否有可能使用另一种方法来定义我的范围，并将此代码也用于其他范围。为了正确解释，我发现很困难，但我的目的是优化程序，可以通过不一次又一次地编写代码，而只能更改范围可以做到这一点。
Sub DescriptionisActivated()
Select Case Range("A12")
Case Is = ""
If Sheet3.Range("B16") <> "" Then
MsgBox "Input1" & vbNewLine & vbNewLine & "-Imput2", vbExclamation, " missing"
Sheet3.Range("B16:L16").ClearContents: Sheet3.Range("A12").Select
End If
Case Is <> ""
If Sheet3.Range("B16") <> "" Then
Sheet3.Range("P16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("B16").Offset(0, 2).Select
Else
Sheet3.Range("N16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("P16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("N16,P16:S16").ClearContents: Sheet3.Range("B16").Select
End If
End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$16" Then
Sheet3.Unprotect ""
Call DescriptionisActivated
Sheet3.Protect ""
End If
Situation
Hello SO,
I have a situation where, I have the following code which I would like to use over other ranges to. But in this case, when the case ranges changes, the clear contents and otehr defined ranges will also change. I find that I have to write a repeatative code for other ranges. Is it possible that I could possibly use another way to define my range and use this code for other range as well. To explain properly, I am finding it difficult but my aim is to optimize the program where I could do by not writing the code again and again but only change of ranges could be porrible.
Sub DescriptionisActivated()
Select Case Range("A12")
Case Is = ""
If Sheet3.Range("B16") <> "" Then
MsgBox "Input1" & vbNewLine & vbNewLine & "-Imput2", vbExclamation, " missing"
Sheet3.Range("B16:L16").ClearContents: Sheet3.Range("A12").Select
End If
Case Is <> ""
If Sheet3.Range("B16") <> "" Then
Sheet3.Range("P16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("B16").Offset(0, 2).Select
Else
Sheet3.Range("N16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("P16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("N16,P16:S16").ClearContents: Sheet3.Range("B16").Select
End If
End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$16" Then
Sheet3.Unprotect ""
Call DescriptionisActivated
Sheet3.Protect ""
End If
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
因此，如果您更改
并将
其发送到范围时，
则它具有一个范围。
完成此操作后，您需要更改描述的每个范围重复依赖目标范围。因此，例如替换：
当您这样做时要
小心，以确保它们将指向您发送给它的每个可能目标所需的正确单元格，
让我知道它的发展方式！
so if you change
to
and send it a range when you call it i.e.
then it has a range to start with.
Once that is done you need to change every range refence in DescriptionisActivated to rely on the Target Range. So for example replace:
with
be careful as you do this to make ensure that they will point to the right cells that are required for each possible Target you would send to it
let me know how it goes!