首页 文章

Excel VBA取消输入框不返回false / exit子

提问于
浏览 次
2

我的Excel工作表上有一个命令按钮，它打开一个application.inputbox，预装了当前选定的范围，并将该范围内的单元格内容附加到这些单元格的注释中 .

我正在使用if / else语句来检查是否单击了取消按钮，但是它没有退出sub;无论我单击确定还是取消，代码都会运行 . 我认为取消按钮不会返回'false'或我的if语句被破坏 .

这是代码：

Private Sub CommentLogButton_Click()
'This Sub Appends Cell Contents to Cell Comment
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Range to Log"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
If WorkRng = False Then
    Exit Sub
Else
    For Each rng In WorkRng
        rng.NoteText Text:=rng.NoteText & rng.Value & ", "
        rng.Value = ""
    Next
End If
End Sub
excel vba excel-vba inputbox

2 回答

  • 3

    WorkRng 已被声明为范围 .

    更改

    If WorkRng = False Then

    至

    If WorkRng is nothing Then

    将您的代码更改为

    Private Sub CommentLogButton_Click()
    'This Sub Appends Cell Contents to Cell Comment
    Dim rng As Range, WorkRng As Range
    Dim rngAddress As String

    xTitleId = "Range to Log"

    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If

    rngAddress = Application.Selection.Address

    On Error Resume Next
    Set WorkRng = Application.InputBox("Range", xTitleId, rngAddress, Type:=8)
    On Error GoTo 0

    If WorkRng Is Nothing Then
        Exit Sub
    Else
        For Each rng In WorkRng
            rng.NoteText Text:=rng.NoteText & rng.Value & ", "
            rng.Value = ""
        Next
    End If
End Sub
    回复于
  • 1
    Dim sRange As String

sRange = Application.InputBox("Range", xTitleId, Application.Selection.Address)
Is sRange = "" Then
    Exit Sub
Else
    Set WorkRng = Range(sRange)
End If
    回复于

相关问题