Inputbox cancel not working

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try catching the error

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

Dave
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Dave,

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
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


Rich (BB code):
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

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

@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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,873
Office Version
  1. 2010
Platform
  1. Windows
@Tigerexcel,

The following (completely different) code does exactly what your posted code does except it handles the Cancel button correctly...
Rich (BB code):
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

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
@Tigerexcel,

The following (completely different) code does exactly what your posted code does except it handles the Cancel button correctly...
Rich (BB code):
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?
 
Last edited:

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,641
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top