Handling blank entries in an Inputbox with input type range

[sparky2205]

Hi folks,
I am having a problem with an inputbox.
Specifically handling the situation where a user doesn't enter any information and presses OK.
I have dealt with this before, successfully, when the input to the inputbox is numbers or text.
However, my input is a range.
I have trawled through what I can find online but I haven't been successful in putting it all together.
This is what I have so far:

Sub MergeCells()
Dim r As Range
Dim c As New Collection
'   On Error Resume Next
    
'   Set r = Application.InputBox("select range using your mouse", , , , , , , 8)
    c.Add Application.InputBox("select range using your mouse", , , , , , , 8)
    If TypeOf c(1) Is Range Then Set r = c(1)
        Set c = New Collection
    
'   If Err.Number = 424 Then
'       Exit Sub
'   End If
    If r Is Nothing Then
        Exit Sub
        ElseIf r = "" Then
        Exit Sub
    End If
    r.Select
    r.Merge
    
End Sub

You will notice some commented code. I left it there for reference to show other things I had been trying, unsuccessfully.

The problems I'm having:
My range, r, is not being set, even when I select a range, so always evaluates to nothing.
When I leave the inputbox blank and press OK I get a message telling me there is a problem with my formula.

 

[NdNoviceHlp]

I think this is where this code comes in handy...

If Not C is Nothing then

HTH. Dave

 

[sparky2205]

I have modified my code to add the different merge types.
Using an error handler and a Type:= text for my initial input box I have been able to overcome my problem with the error regarding a problem with my formula.
But this issue also presents itself with a Type:= range for my second input box, which I need.
I have posted my code below. I think it's close but I'm unable to handle the error with the second input box.

Sub MergeCells()
Dim r As Range
Dim t As Integer
Dim c As New Collection
   On Error GoTo ErrHandler
    
         
    t = Application.InputBox("Enter merge type from the list:" & vbCrLf _
    & "1 = Merge" & vbCrLf & "2 = Merge and Centre" & vbCrLf & "3 = Merge Across", , , , , , , 2)

    If t = False Then
        Exit Sub
    End If
    
    c.Add Application.InputBox("select range using your mouse", , , , , , , 8)
       If TypeOf c(1) Is Range Then Set r = c(1)
           Set c = New Collection
      
    If t = "1" Then
        r.Merge
        ElseIf t = "2" Then
            With r
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Merge
            End With
            ElseIf t = "3" Then
                r.Merge across:=True
    End If
    Exit Sub
ErrHandler:
    MsgBox "Either a Merge Type or Range is missing. Please retry"
    
End Sub

 

[MickG]

Can you use this??
If you don't enter a range and click "OK" nothing happens, you need to Exit the Input box to proceed.

Private Sub CommandButton1_Click()
Dim Rng As Range
Application.DisplayAlerts = False
On Error Resume Next
    Set Rng = Application.InputBox(prompt:="Please Select ", Title:="Get Range", Type:=8)
     MsgBox "Typename = " & TypeName(Rng)
      If Rng Is Nothing Then Exit Sub
MsgBox "Continuing Code !!!"
Application.DisplayAlerts = True
End Sub

 

[sparky2205]

Thanks MickG, I can work with that. I've used some of your logic in my modified code below.
I presume what you've done here is prevent the error message being displayed? (Application.DisplayAlerts = False)
Just for my own knowledge is there any way to trap this error? Or is this just an Alert?
With my first input box I am able to exit if the user cancels or doesn't enter a value.
I'd like to be able to repeat this for my second input box.
But if the user doesn't select an entry and presses OK in my second input box it just waits for an entry.
Not a big deal. It still forces the user to make an entry or quit without throwing an error.
I'd just like to know if it is possible to deal with these Alerts programmatically.

Sub MergeCells()

Dim r As Range
Dim t As Integer
    On Error Resume Next
         
    t = Application.InputBox("Enter merge type from the list:" & vbCrLf _
    & "1 = Merge" & vbCrLf & "2 = Merge and Centre" & vbCrLf & "3 = Merge Across", , , , , , , 2)
    If t = False Then
        Exit Sub
    End If
    Application.DisplayAlerts = False
    Set r = Application.InputBox("select range using your mouse", , , , , , , 8)
    MsgBox r
    If r Is Nothing Then Exit Sub
    If t = "1" Then
        r.Merge
        ElseIf t = "2" Then
            With r
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Merge
            End With
            ElseIf t = "3" Then
                r.Merge across:=True
    End If
    Exit Sub
   
End Sub

 

[MickG]

I don't think there's any way round it. The "DisplayAlerts= false" just gets rid of an unhelpful message.

 

[sparky2205]

I can live with that MickG. Thanks again for your help.