Excel VBA cancelling input box does not return false/exit sub

Question

I have a command button on my Excel sheet which opens an application.inputbox, preloaded with the currently selected range, and appends the contents of the cells in that range to a comment on those cells.

I am using an if/else statement to check if the cancel button is clicked, but it is not exiting the sub; the code runs whether I click OK or Cancel. I think either the cancel button is not returning 'false' or my if statement is broken.

Here is the code:

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

Siddharth Rout · Accepted Answer · 2015-08-03 20:05:58Z

3

WorkRng has been declared as range.

Change

If WorkRng = False Then

to

If WorkRng is nothing Then

Change your code to

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

edited Aug 3, 2015 at 20:05

answered Aug 3, 2015 at 18:33

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MichaelV Over a year ago

I tried this and it did not work - nothing was autocorrected to Nothing and the code behaved in the same way as before. Could it be because the inputbox is automatically loaded with a range? I fixed my issue by using a message box with vbYesNo and checking for yes, but still would like to know why my first code didn't work

Siddharth Rout Over a year ago

Check the updated code. You may have to refresh the page.

Don Jewett · Accepted Answer · 2015-08-03 19:57:13Z

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

edited Aug 3, 2015 at 19:57

answered Aug 3, 2015 at 18:39

Don Jewett

2,00715 silver badges29 bronze badges

Collectives™ on Stack Overflow

Excel VBA cancelling input box does not return false/exit sub

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related