Inputbox cancel not working

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
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.
 
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.
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top