Tigerexcel
Active Member
- Joined
- Mar 6, 2020
- Messages
- 493
- Office Version
- 365
- 2019
- Platform
- 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.
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.