Modify VBA Code To Do Nothing When Clicking Cancel

[legalhustler]

The following code auto numbers a selected range that has merged cells. An input dialog pops that asks for to select/provide the range to auto number with the option to click "OK" or "Cancel", when I click "Cancel" it puts a "1" in a pre-selected cell. I would like to modify the code so that when I click cancel it should do nothing (not put a "1"). Can someone help. Thanks!

Sub NumberCellsAndMergedCells()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Str As String
    On Error Resume Next
    xTitleId = "Auto Numbering"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select Range:", xTitleId, WorkRng.Address, Type:=8)
    Set WorkRng = WorkRng.Columns(1)
    xIndex = 1
    Set Rng = WorkRng.Range("A1")
    Do While Not Intersect(Rng, WorkRng) Is Nothing
        Rng.Value = xIndex
        xIndex = xIndex + 1
        Set Rng = Rng.MergeArea.Offset(1)
    Loop
   End Sub

 

[smozgur]

Check if WorkRng is Nothing, and quit procedure (or do something else you need) if it is Nothing. If Cancel is clicked then it means object variable has not been set.
Sample:

...
Set WorkRng = Application.InputBox("Select Range:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then
   Exit Sub
End If
...

 

[legalhustler]

Check if WorkRng is Nothing, and quit procedure (or do something else you need) if it is Nothing. If Cancel is clicked then it means object variable has not been set.
Sample:

...
Set WorkRng = Application.InputBox("Select Range:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then
   Exit Sub
End If
...

I only have rudimentary VBA experience. I'm not sure where in my code that needs to go. Please provide entire code.

 

[Joe4]

I only have rudimentary VBA experience. I'm not sure where in my code that needs to go. Please provide entire code.

If you look at his code, the first row matches EXACTLY one of your lines of code.
Just place the three new lines of code he shows under that one, keeping everything else exactly as it is (you are just inserting three new lines of code).

 

[legalhustler]

If you look at his code, the first row matches EXACTLY one of your lines of code.
Just place the three new lines of code he shows under that one, keeping everything else exactly as it is (you are just inserting three new lines of code).

Here is the entire code with the new lines, however when I click the Cancel button it still puts a "1" in a cell.

Sub NumberCellsAndMergedCells()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Str As String
    On Error Resume Next
    xTitleId = "Auto Numbering"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select Range:", xTitleId, WorkRng.Address, Type:=8)
    [B]If WorkRng Is Nothing Then
    Exit Sub
    End If[/B]
    Set WorkRng = WorkRng.Columns(1)
    xIndex = 1
    Set Rng = WorkRng.Range("A1")
    Do While Not Intersect(Rng, WorkRng) Is Nothing
        Rng.Value = xIndex
        xIndex = xIndex + 1
        Set Rng = Rng.MergeArea.Offset(1)
    Loop
End Sub

 

[smozgur]

Remove following line. It is presetting WorkRng object variable, and verification fails.

 Set WorkRng = Application.Selection

 

[smozgur]

And you also have to remove the WorkRng reference from the InputBox. Final code should be:

Sub NumberCellsAndMergedCells()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Str As String
    On Error Resume Next
    xTitleId = "Auto Numbering"
    Set WorkRng = Application.InputBox("Select Range:", xTitleId, Type:=8)
    If WorkRng Is Nothing Then
        Exit Sub
    End If
    Set WorkRng = WorkRng.Columns(1)
    xIndex = 1
    Set Rng = WorkRng.Range("A1")
    Do While Not Intersect(Rng, WorkRng) Is Nothing
        Rng.Value = xIndex
        xIndex = xIndex + 1
        Set Rng = Rng.MergeArea.Offset(1)
    Loop
End Sub

 

[legalhustler]

And you also have to remove the WorkRng reference from the InputBox. Final code should be:

Sub NumberCellsAndMergedCells()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Str As String
    On Error Resume Next
    xTitleId = "Auto Numbering"
    Set WorkRng = Application.InputBox("Select Range:", xTitleId, Type:=8)
    If WorkRng Is Nothing Then
        Exit Sub
    End If
    Set WorkRng = WorkRng.Columns(1)
    xIndex = 1
    Set Rng = WorkRng.Range("A1")
    Do While Not Intersect(Rng, WorkRng) Is Nothing
        Rng.Value = xIndex
        xIndex = xIndex + 1
        Set Rng = Rng.MergeArea.Offset(1)
    Loop
End Sub

Bingo! It appears to work now. Thank you!