Check if Range Input Box "Cancel" has been clicked

[mrmmickle1]

I was hoping to see if the inputbox was canceled.


I have tried several things so far:

1. changing datatype to a variant and testing for False with no luck.
2. I have also tried testing for vbCancel with no luck.
3. I have also tried testing for VarType() and then handling things differently if Boolean....

Is it possible to collect this information. The issue is the input defaults to have whatever cell is selected in it initially. So when the user presses cancel it inserts a checkbox anyways. Even though the user has clicked cancel....

Any help would be appreciated:


I believe this source code originated from KuTools Website.

[COLOR=#0000ff][B]Sub [/B][/COLOR]InsertCheckBoxes()

 [COLOR=#0000ff][B]   Dim[/B][/COLOR] Rng[COLOR=#0000ff][B] As Range[/B][/COLOR]
   [COLOR=#0000ff][B] Dim[/B][/COLOR] WorkRng [COLOR=#0000ff][B]As Range[/B][/COLOR]
    [COLOR=#0000ff][B]Dim [/B][/COLOR]Ws [B][COLOR=#0000ff]As Worksheet
[/COLOR][/B]
[COLOR=#0000ff][B]    On Error Resume Next[/B][/COLOR]
    
  [B][COLOR=#0000ff]  Set [/COLOR][/B]WorkRng = Application.Selection
  [COLOR=#0000ff][B]  Set[/B][/COLOR] WorkRng = Application.InputBox("Range", "My Title", WorkRng.Address, Type:=8)
 [COLOR=#0000ff][B]   Set [/B][/COLOR]Ws = Application.ActiveSheet
    
    Application.ScreenUpdating = [COLOR=#0000ff][B]False[/B][/COLOR]
  [B][COLOR=#0000ff]  For Each[/COLOR][/B] Rng [COLOR=#0000ff][B]In[/B][/COLOR] WorkRng
     [B][COLOR=#0000ff]   With [/COLOR][/B]Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
            .Characters.Text = Rng.Value
    [B][COLOR=#0000ff]    End With
    Next[/COLOR][/B]
    WorkRng.ClearContents
    WorkRng.Select
    Application.ScreenUpdating = [COLOR=#0000ff][B]True[/B][/COLOR]

  [B][COLOR=#0000ff]  End Sub[/COLOR][/B]

 

[Andrew Poulsom]

WorkRng will be Nothing if Cancel is pressed.

 

[mrmmickle1]

I added this line after the initial lines and I'm still unable to get the expected result:

    [COLOR=#0000ff][B]Set[/B][/COLOR] WorkRng = Application.InputBox("Range", "My Title", WorkRng.Address, Type:=8)
[COLOR=#ff0000]    If WorkRng Is Nothing Then Exit Sub[/COLOR]

Not sure where I'm going wrong. If I Cancel a checkbox is inserted. If I click OK a checkbox is inserted.

Do I need to use a different type of Input Box or a Custom UserForm instead?

 

[Andrew Poulsom]

What was the expected result and what result did you actually get?

 

[mrmmickle1]

The expected Result when I click Cancel is to Exit Sub.

The Expected Result When I Click OK is to insert a Checkbox in the Selected Range.

The actual result was a checkbox in the range either way. Should, I remove the default argument to leave the initial box blank?

 

[Andrew Poulsom]

I don't think setting a Default should make any difference. But you have Set that variable earlier, so maybe it's sticking.

 

[mrmmickle1]

You were right when I comment out:

[B][COLOR=#008000]    'Set WorkRng = Application.Selection[/COLOR][/B]

and change the other code to this:

[COLOR=#0000ff][B]    Set[/B][/COLOR] WorkRng = Application.InputBox("Range", "My Title", , Type:=8)
   [COLOR=#0000ff] [B]If[/B] [/COLOR]WorkRng[B] [COLOR=#0000ff]Is Nothing Then Exit Sub[/COLOR][/B]

It works without issue.

 

[Andrew Poulsom]

You could set the default to Selection.Address.