Inputbox cancel not working

[Tigerexcel]

This macro allows the user to select a range and apply some basic formatting. It will work whether the cursor is in the required range or in a nearby cell. It works ok except if the user tries to cancel at the inputbox stage. Upon the selection of cancel in the input box I get an error message (object required). How do I correct this?
Sub Choose_Range()

Dim Scell As Range
'clear any previous formatting
Cells.ClearFormats
'if cursor is on a blank cell
If ActiveCell = "" Then
Set Scell = Application.InputBox(prompt:="Select a Cell", Type:=8)
'in the event that user wishes to abort the process
If Scell is nothing Then Exit Sub
Scell.Select

Range(ActiveCell, ActiveCell.CurrentRegion).Select

Else
Range(ActiveCell, ActiveCell.CurrentRegion).Select
End If
With Selection
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Font.Color = vbBlue
End With

End Sub

It may be written more efficiently than my attempt and if that is the case, I am happy to take advice on that.

 

[dmt32]

Hi,
try catching the error

On Error Resume Next
Set Scell = Application.InputBox(prompt:="Select a Cell", Type:=8)
On Error GoTo 0

Dave

 

[Tigerexcel]

Hi Dave,

Tried the on error codes and inserted them in different places , still no success . What next?

 

[dmt32]

Tried the on error codes and inserted them in different places , still no success . What next?

If you placed the On Error code where shown, this will trap the error

Sub Choose_Range()
   
    Dim Scell As Range
'clear any previous formatting
    Cells.ClearFormats
'if cursor is on a blank cell
    If ActiveCell = "" Then
    On Error Resume Next
        Set Scell = Application.InputBox(prompt:="Select a Cell", Type:=8)
    On Error GoTo 0
'in the event that user wishes to abort the process
        If Scell Is Nothing Then Exit Sub
   
        Scell.Select
        
        Range(ActiveCell, ActiveCell.CurrentRegion).Select
        
    Else
        Range(ActiveCell, ActiveCell.CurrentRegion).Select
    End If
    With Selection
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
        .Font.Color = vbBlue
    End With
    
End Sub

If still having issues, publish the code with changes you have made

Dave

 

[Tigerexcel]

Thanks Dave that works fine. By the way and I’m not sure why but taking out the On error go to 0 doesn’t impact on the macro in any way. The two crucial lines are the On error resume Next and the if scell is nothing then exit sub to avoid the error when hitting Cancel in the dialog box.

 

[dmt32]

Hi,
On Error GoTo 0 disables error handling in the current procedure - by taking that line out any further errors in your routine will probably be masked by the On Error Resume Next statement which is not considered good practice & why you think your change has not impacted the rest of the code

Dave

 

[GWteB]

@dmt32 I agree; for better understanding a remark ...

On Error GoTo 0 disables error handling in the current procedure ....

... should be read as: "On Error GoTo 0 disables any enabled (ie selfmade) error handler in the current procedure" so VBA's built-in error handler takes over.

 

[Rick Rothstein]

@Tigerexcel,

The following (completely different) code does exactly what your posted code does except it handles the Cancel button correctly...

Sub Choose_Range()
  Dim Scell As Range
  ' Clear any previous formatting
  Cells.ClearFormats
  ' Trap the error if user clicks Cancel button
  On Error GoTo OhOh
  ' Get the Current Region (if any) surrounding the user
  ' selected cell and apply the indicated formatting
  With Application.InputBox(prompt:="Select a Cell", Type:=8).CurrentRegion
    .HorizontalAlignment = xlCenter
    .Font.Bold = True
    .Font.Color = vbBlue
  End With
' Where the code goes if there was an error
OhOh:
End Sub

Note: Your original code (so then the code above) will format a lone empty cell if that is what the user selects in response to the InputBox meaning any text entered into that empty cell will become formated upen being entered.

 

[Tigerexcel]

@Tigerexcel,

The following (completely different) code does exactly what your posted code does except it handles the Cancel button correctly...

Sub Choose_Range()
  Dim Scell As Range
  ' Clear any previous formatting
  Cells.ClearFormats
  ' Trap the error if user clicks Cancel button
  On Error GoTo OhOh
  ' Get the Current Region (if any) surrounding the user
  ' selected cell and apply the indicated formatting
  With Application.InputBox(prompt:="Select a Cell", Type:=8).CurrentRegion
    .HorizontalAlignment = xlCenter
    .Font.Bold = True
    .Font.Color = vbBlue
  End With
' Where the code goes if there was an error
OhOh:
End Sub

Note: Your original code (so then the code above) will format a lone empty cell if that is what the user selects in response to the InputBox meaning any text entered into that empty cell will become formated upen being entered.

Thank you Rick, I love the economy of your coding, in other words the ability to perform a number of steps with a minimum of coding! One thing that does not seem intuitive to me though is why does the on error goto message appear before the user has the opportunity to Cancel? I would have assumed that the error command comes after the user has pressed Cancel.
Furthermore the line OhOh: I don't see the word OhOh appear once the Cancel button is pressed so am assuming that it simply differentiates the subprocedure from the With statement.
If I wanted put a Do you really wish to Cancel dialog box or something similar what code would I need?

 

[GWteB]

Presuming I don't bother Rick me stepping in this thread a second time, @Tigerexcel, the "OhOh" is a label within your code. Whenever a run-time error occurs in this procedure VBA will skip all lines of code after the line of code which resulted in the error and will resume execution at the point in your code just right after the label "OhOh", in this particular situation the end of the procedure. As Rick noted, whenever a user clicks "Cancel" on the input dialog, the line of code that puts the dialog on screen results in an error, so the procedure ends due to the "jump" to label "OhOh".

Upon the selection of cancel in the input box I get an error message (object required). How do I correct this?

You can't but you can trap the error and handle it yourself, for example:

Sub Choose_Range()
  Dim Scell As Range
  ' Clear any previous formatting
  Cells.ClearFormats
  ' Trap the error if user clicks Cancel button
SUB_REPEAT:
  On Error GoTo OhOh
  ' Get the Current Region (if any) surrounding the user
  ' selected cell and apply the indicated formatting
  With Application.InputBox(prompt:="Select a Cell", Type:=8).CurrentRegion
    .HorizontalAlignment = xlCenter
    .Font.Bold = True
    .Font.Color = vbBlue
  End With
  Exit Sub
' Where the code goes if there was an error
OhOh:
    Err.Clear
    MsgBox "You did not select a cell but cancelled, you need to select a cell ...", vbExclamation
    goto SUB_REPEAT
End Sub