[已解决]帮忙看看保留数值的宏选择区域时无虚线框提示是什么原因

huchuanxing

以下是两段VBA代码：
Sub 去除选定区域公式保留数值()
    Dim rng As Range, rng1 As Range, address As String
    On Error GoTo VeryEnd
    Application.ScreenUpdating = False
    If TypeName(Selection) = "Range" Then address = Selection.address Else address = ""
    '弹出一个对话框, 让用户选择区域, 默认显示变量address的值。然后将该用户选择区域赋予变量rng
    Set rng = Application.InputBox("请选择去除公式的区域", "选定区域", address, , , , , 8)
    If rng Is Nothing Then Exit Sub

    For Each rng1 In rng
        If rng1.HasFormula Then rng1.Value = rng1.Value
    Next
    MsgBox "选定区域公式去除完毕，保留了数值！"
    Application.ScreenUpdating = True
VeryEnd:
End Sub

Sub 数字转文本()

    Dim rng As Range, rng1 As Range, address As String
    If TypeName(Selection) = "Range" Then address = Selection.address Else address = ""
    '弹出一个对话框, 让用户选择区域, 默认显示变量address的值。然后将该用户选择区域赋予变量rng
    Set rng = Application.InputBox("请选择待转换的区域", "转换区域", address, , , , , 8)
    If rng Is Nothing Then Exit Sub
    For Each rng1 In rng
        If rng1.Value <> "" Then
            rng1.Value = "'" & rng1.Value
        End If
    Next
End Sub


数字转文本代码，当你选择单元格区域时，选定区域会有虚线框提示选择的区域，如图一所示，而另一段代码，在选定区域时无虚线框提示选择的区域，如图二所示，这是为什么？可否对“去除选定区域公式保留数值”代码进行修改让它在选择区域时也能有虚线框提示选定的区域吗？

最佳答案

月排行榜 / 总排行榜

zjdh

2016-12-6 08:44

Sub 去除选定区域公式保留数值()
    On Error Resume Next
    Dim rng As Range, rng1 As Range, address As String
    If TypeName(Selection) = "Range" Then address = Selection.address Else address = ""
    Set rng = Application.InputBox("请选择待转换的区域", "转换区域", address, , , , , 8)
    If rng Is Nothing Then Exit Sub
    For Each rng1 In rng
        If rng1.HasFormula Then rng1.Value = rng1.Value
    Next
    MsgBox "选定区域公式去除完毕，保留了数值！"
End Sub

跳转到最佳答案楼层

zjdh

Sub 去除选定区域公式保留数值()
    On Error Resume Next
    Dim rng As Range, rng1 As Range, address As String
    If TypeName(Selection) = "Range" Then address = Selection.address Else address = ""
    Set rng = Application.InputBox("请选择待转换的区域", "转换区域", address, , , , , 8)
    If rng Is Nothing Then Exit Sub
    For Each rng1 In rng
        If rng1.HasFormula Then rng1.Value = rng1.Value
    Next
    MsgBox "选定区域公式去除完毕，保留了数值！"
End Sub

苏子龙

Application.ScreenUpdating = False，这句表示屏幕刷新关闭了，那就不会提示选择框了啊

huchuanxing

zjdh 发表于 2016-12-6 08:44
Sub 去除选定区域公式保留数值()
    On Error Resume Next
    Dim rng As Range, rng1 As Range, addre ...

谢谢，very  much!

huchuanxing

苏子龙 发表于 2016-12-6 08:59
Application.ScreenUpdating = False，这句表示屏幕刷新关闭了，那就不会提示选择框了啊

谢谢您的解惑！