Range input box

  • Thread starter Thread starter Klemen25
  • Start date Start date
K

Klemen25

Hello
How to make my basic macro to exit inputbox on 'cancel'?

Sub Fill_blanks()
Dim myRange As Range

Set myRange = Application.InputBox(Prompt:="x.", Title:="y", Type:=8)
myRange.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

Exit Sub
End Sub


I tried with:


If Err.Number = 424 Then
Exit Sub
End If



If myRange = False
Exit Sub
End If


Thank you as allways!
 
One way:

Option Explicit

Sub Fill_blanks()
Dim myRange As Range
Dim myBlanks As Range

Set myRange = Nothing
On Error Resume Next
Set myRange = Application.InputBox(Prompt:="x.", Title:="y", Type:=8)
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "Try later"
Else
Set myBlanks = Nothing
On Error Resume Next
Set myBlanks = Intersect(myRange, _
myRange.SpecialCells(xlCellTypeBlanks))
On Error GoTo 0

If myBlanks Is Nothing Then
MsgBox "no blanks in selection"
Else
myBlanks.FormulaR1C1 = "=R[-1]C"
End If
End If

End Sub

Debra Dalgleish has similar code here:
http://contextures.com/xlDataEntry02.html
Hello
How to make my basic macro to exit inputbox on 'cancel'?

Sub Fill_blanks()
Dim myRange As Range

Set myRange = Application.InputBox(Prompt:="x.", Title:="y", Type:=8)
myRange.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

Exit Sub
End Sub

I tried with:

If Err.Number = 424 Then
Exit Sub
End If

If myRange = False
Exit Sub
End If

Thank you as allways!
Click to expand...
 
I got the process from her, but I didn't notice the code- perhaps it
was added later.
Still- thank you very much!
 
Back
Top