Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help with Application.InputBox Macro

0

I have created the macro listed below.  It works, but I want to have a message box pop up if the user does not select a cell and clicks OK.  The If section only happens when the user clicks Cancel.  How would I get that to work?

Sub Create_Node()

Dim userSelection As Range

On Error Resume Next

Set userSelection = Application.InputBox(Prompt:="Select a cell and then edit the command.", Title:="Create Node", Type:=8)

If userSelection Is Nothing Then

 MsgBox "No cell selected."

 Exit Sub

End If

userSelection.Value = "Node created."

Resume Next

End Sub

Answer
Discuss

Discussion

Hi KAD and welcome to the Forum

Doesn't Application.Inbox (with a Range result specified by Type 8) already prevent a user from not selecting a cell (or a range of cells or recognised Name)?
John_Ru (rep: 6092) Mar 8, '21 at 4:33 pm
When the input box pops up, the user needs to select a cell, which when they do, the cell range is entered in the pop up box.  But, I want to be able to show the user a message box when they don't select a cell and just click OK.
KAD Mar 8, '21 at 4:37 pm
KAD, please put CODE tags around your code so it is easier to read. And have you tried doing what you are saying? Your code won't allow the user to hit OK and submit nothing, it forces the user to select a cell and shows a default validation message. This message you cannot customize.
don (rep: 1989) Mar 8, '21 at 4:52 pm
With my current code, if the user clicks OK without slecting a cell, the message below is displayed.

"There's a problem with this formula.
  Not trying to type a formula?
  When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a    formula:"

If they click Cancel, the message box with "No cell selected." appears.
KAD Mar 8, '21 at 5:00 pm
That is exactly how it works. You can't change that first message box.
don (rep: 1989) Mar 8, '21 at 5:17 pm
Agreed Don, there's no danger that the user doesn't select one or more cells (unless they press cancel). 
John_Ru (rep: 6092) Mar 8, '21 at 5:28 pm
Add to Discussion

Answers

1

The code below will let you to make the process as smooth as Excel permits. It has three new features.

  1. The user should select a cell before the code is run. Actually, this is no big deal because that's an Excel default. The novelty in my code is that the selection assigned to the code's userSelection variable.
    1. Note that I use the Selection object. If you want a single cell change this to ActiveCell or even a articular cell, like Cells(1, 1)
  2. The InputBox has a default value when it's first shown. Of course, that is the address of the userSelection default value just explained.
  3. Error notifications are suppressed whle the In putBox is shown. Your user will not be confused by the complicated and inapplicable error alert.
Sub Create_Node()
    ' 190

    Dim Prompt              As String
    Dim userSelection       As Range

    Set userSelection = Selection
    Application.DisplayAlerts = False
    Prompt = "Select a cell and then edit the command."
    On Error Resume Next
    Set userSelection = Application.InputBox(Prompt:=Prompt, _
                                             Title:="Create Node", _
                                             Default:=userSelection.Address(0, 0), _
                                             Type:=8)
    Application.DisplayAlerts = True
    If userSelection Is Nothing Then
        MsgBox "No cell selected."
        Exit Sub
    Else
        If Err = 424 Then
            MsgBox "User pressed Cancel"
        Else
            userSelection.Value = "Node created."
        End If
    End If
End Sub

Unfortunately, there is no way to disable the loop that insists on an entry being made. I also failed replacing the default alert with a customized one like you suggested. Suppression is the best solution. The user is forced to either select a range, enter a range address or press Cancel.

BTW, the reason why I added the Prompt variable is just to have a neat list of properties set for the InputBox. As far as syntax is concerned, it's perfectly OK to enter the prompt text directly agains the property assignment as you did. It's just that the line will become too long. So I found a way to make it shorter.

Discuss

Discussion

That is a very clever usage of Application.DisplayAlerts!!! Very nice!
don (rep: 1989) Mar 9, '21 at 11:29 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login