Inputbox | Cancel | Undo event

[hansgrandia]

Hello,

Below standing code pops-up after worksheet-change event. I'm looking for a piece of code that ends after pressing "cancel" (and revert to previous value) or ends when no name is entered (and also revert to last entered value of the cell). Name in the offset function should only change when person enters his/her name. When user pressed enter of no name, the name should be the same.

The code puts "name" (naam) on false in after pressing cancel and does not react when no value is entered.

Any help is appreciated! Regards,
Hans
Netherlands


Sub GoToName()
Dim Naam As String
Naam = Application.InputBox("What is your name?", Type:=2)
If Naam = "" Then
Exit Sub
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Forgot to enter name", vbExclamation
End If
If Naam = vbNullString Then
Exit Sub
End If
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True

ActiveCell.Offset(0, 5).Value = Naam
End Sub

 

[daverunt]

Hi

Not sure this is fixed because I don't have the 'event' details
However there are a couple of points.

Exit Sub is in wrong place if you want to see the message box for an empty entry.
Application.Inputbox returns the string "False" when you cancel so check for that.

If you put a break point in the code and do whatever triggers the event then you can step through the code using the F8 button to see what's happening.

Sub GoToName()
Dim Naam As String
Naam = Application.InputBox("What is your name?", Type:=2)
If Naam = "" Then
 
 Application.EnableEvents = False
 Application.Undo
 Application.EnableEvents = True
 MsgBox "Forgot to enter name", vbExclamation
 Exit Sub
 End If
If Naam = "False" Then
 Exit Sub
 End If
 Application.EnableEvents = False
 Application.Undo
 Application.EnableEvents = True

 ActiveCell.Offset(0, 5).Value = Naam
End Sub

 

[hansgrandia]

Hi,

Thanks for the reply. This bring me much closer to the situation I was seeking for! The only gap that's left is that in a situation where the user cancels the inputbox, the value of "ActiveCell.Offset(0, 5).Value = Naam" gets "false". Probably because it did not remember the name from the event code. I currently considering the use of a variable that assignes and retrieves it in the sub GoToName. Something like: Name = ActiveCell.Offset(0, 5).Value

Some VBA brainpower would be appreciated!

Regards,
Hans (NL)

Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B4:B10000")) Is Nothing Then
Name =
Call GoToName
End If
End Sub

 

[MARK858]

What happens with the code below?

Sub GoToName()

    Dim Naam As Variant
    Naam = Application.InputBox("What is your name?", Type:=2)
    If Naam = "" Then

        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "Forgot to enter name", vbExclamation
        Exit Sub
    End If
    If Not (Naam) Then Exit Sub

    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True

    ActiveCell.Offset(0, 5).Value = Naam
End Sub

 

[hansgrandia]

Thanks Mark858. This code does the job, however: it accepts the new value in the B range instead of reverting to the previous value. The inputbox appears when an other option has been chosen from drill down list. It should keep it's value when users cancels the inputbox instead of accepting the new value.

Should I add a line of code in the event code to remember the original value?

Thanks again!
Hans NL

 

[MARK858]

Should I add a line of code in the event code to remember the original value?

As Dave has already stated we don't know what is in your Events and so can't really comment but it seems like an OK idea, I will leave you to experiment.

 

[hansgrandia]

Hello,

After a couple of hours experimenting based on this link, I think I'm pretty close to the target condition. The only flaw is that it still does not show the old value. Below the event I'm struggling with... Where does the code goes wrong? Help is appreciated! Regards, Hans

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
Newv = Range("cellChanged").Value
Application.EnableEvents = False
Application.Undo
oldv = Range("cellChanged").Value
Range("cellChanged").Value = Newv
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim oval
Dim oldv As String
Dim Newv As String
Dim Naam As Variant
For Each cell In Target
    If Not (Intersect(cell, Range("B4:B10000")) Is Nothing) Then
        Newv = cell.Value
        oldv = oval
    End If
    Call GoToName
Next cell
    
End Sub
 
Sub GoToName()
Dim Naam As String
Dim oldv As String
Naam = Application.InputBox("What is your name?", Type:=2)
    If Naam = "" Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "Forgot to enter name", vbExclamation
        Exit Sub
    End If
    If Naam = "False" Then
    ActiveCell.Value = oldv
    ActiveCell.Offset(0, 5).Value = Naam
    End If
End Sub

 

[daverunt]

Hi,


The Worksheet change event gets the cell value after edit, not before so you would need another process if you had to go down that route.
Excel VBA get value before Worksheet_Change event

I think you can probably use the application.undo when the input is false also.

Sub GoToName()
Dim Naam As String
Naam = Application.InputBox("What is your name?", Type:=2)
If Naam = "" Then
 
 Application.EnableEvents = False
 Application.Undo
 Application.EnableEvents = True
 MsgBox "Forgot to enter name", vbExclamation
 Exit Sub
 End If
If Naam = "False" Then
 Application.EnableEvents = False
 Application.Undo
 Application.EnableEvents = True
 Exit Sub
 End If
 Application.EnableEvents = False
 Application.Undo
 Application.EnableEvents = True

 ActiveCell.Offset(0, 5).Value = Naam
 
End Sub

 

[hansgrandia]

Hi,

Thanks for your help! The code that worked out:

Private Sub Worksheet_Change(ByVal Target As Range)
  
Dim OldValue As String
Dim Naam As String
  
If Not Intersect(Target, Range("B4:B10000")) Is Nothing Then
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Application.Undo
Application.EnableEvents = True
If OldValue <> Target.Value Then
Naam = InputBox("What is your name?")
End If
End If
       
'cancel button
If StrPtr(Naam) = 0 Then
On Error Resume Next
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
'no name entered
If Naam = "" Then
On Error Resume Next
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Vergeten naam in te vullen", vbExclamation
Exit Sub
End If
        
'name person who revises value
ActiveCell.Offset(0, 5).Value = Naam
        
End Sub