Selected Answer
The code below will let you to make the process as smooth as Excel permits. It has three new features.
- 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.
- 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)
- The InputBox has a default value when it's first shown. Of course, that is the address of the userSelection default value just explained.
- 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.