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.
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thank you GW. I haven't seen the Sub_Repeat type of programming, is that a label or some type of subroutine? I had thought that with VBA there were only Subroutines or Functions.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome.
is that a label or some type of subroutine?
It's a label in your procedure, so that you're able to influence the program flow within that procedure.
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am never "bothered" by anyone stepping in to a message I posted and adding content to what I posted. You are most welcome to do so whenever you like.
Thanks Rick, I'm not sure what part of the world you're in but I'll be stuck at home more often for the foreseeable future and I'm trying to upgrade my VBA skills. You and others on this forum have demonstrated a gracious attitude with the support that you've provided. It all makes staying at home a little more palatable.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,517
Members
417,149
Latest member
drbro

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