Input box cancel to not change the cell value even if its blank, but vbnullstring returns something?

[Lil2606]

Hi all, following basic example:

Sub testing2()


Dim THG As Variant


THG = InputBox("Try")


If THG = vbNullString Then THG = "Blank"


Sheet4.Range("C30") = THG


End Sub

I would like to input "Blank" in the field if the user hits OK, but if the user hits cancel I would like the original cell value which can be nothing, to stay the same? I understand the vbnullstring returning the "Blank" is the problem.. but the idea would be that my macro bumps into an empty cell, the inputbox pops up and asks if you want to change this cell, and if the user hits ok without entering anything then, its should change the cell value to "Blank" if the user hits cancel, then don't change the empty cell, just exit the macro.

Possible?

Thank you!

 

[Fluff]

How about

Sub testing2()
   Dim THG As Variant
   
   THG = Application.InputBox("Try")
   If Not THG = False Then
      If THG = vbNullString Then THG = "Blank"
      Sheet14.Range("b5") = THG
   End If
End Sub

 

[Lil2606]

Yeah couldn't make it work from the simple example, so here is my full fixit:

Fixit:

If RRLUV = vbNullString Then
    Correction = MsgBox("Something", vbYesNo)
Else
    Correction = MsgBox(RRLUV & " Somthing else", vbYesNo)
End If
If Correction = vbYes Then
    If RRLUV = vbNullString Then
        New_To = Application.InputBox("What do you want to change " & RRLUV & " into?" & vbCrLf & vbCrLf & "If you click ok without typing a name, the value will be changed to ""BLANK""")
    Else
        New_To = Application.InputBox("What do you want to change the empty cell into?" & vbCrLf & vbCrLf & "If you click ok without typing a name, the value will be changed to ""BLANK""")
    End If
    If Not New_To = False Then
    If New_To = vbNullString Then New_To = "BLANK"
    Sheet2.ListObjects("Table1").ListRows(m).Range(1, Sheet2.ListObjects("Table1").ListColumns("To").Range.Column).Value = New_To
    Err.Clear
    On Error GoTo -1
    GoTo Resume_run
Else
    Exit Sub
End If
End If

and it returns a type mismatch

 

[Rick Rothstein]

Perhaps you can adapt this generalized InputBox code which distinguishes between a user hitting the Cancel button as opposed to the user hitting the Enter button with no text in the text field... simply replace the individual MessageBoxes with the appropriate code for that user selection.

[table="width: 500"]
[tr]
	[td]Dim Answer As String
'....
'....
Answer = InputBox("Tell me something")
If StrPtr(Answer) = 0 Then
  MsgBox "The user clicked Cancel, so we will take the normal route and exit the subroutine now."
  Exit Sub
ElseIf Len(Answer) = 0 Then
  MsgBox "The user clicked OK without entering anything in the InputBox!"
Else
  MsgBox "The user entered the following..." & vbLf & vbLf & Answer
End If[/td]
[/tr]
[/table]

 

[Lil2606]

My macro around looks up the value in a lookup table.. and this would be the error handling if the marco bumps into an empty cell. When I use that generalized inputbox when I hit cancel it inputs "FALSE" in my cell, instead of just leaving it empty, and if I hit ok without entering anything it doesn't input "Blank".. code below:

Fixit:

If RRLUV = vbNullString Then
    Correction = MsgBox("Something", vbYesNo, "Empty cells are not allowed!")
Else
    Correction = MsgBox("Something else", vbYesNo, "Is this a typo?")
End If


    If Correction = vbYes Then


        If RRLUV = vbNullString Then
        New_To = Application.InputBox("What do you want to change " & RRLUV & " into?" & vbCrLf & vbCrLf & "If you click ok without typing a name, the value will be changed to ""BLANK""")
        Else
        New_To = Application.InputBox("What do you want to change the empty cell into?" & vbCrLf & vbCrLf & "If you click ok without typing a name, the value will be changed to ""BLANK""")
        End If
    
        If StrPtr(New_To) = 0 Then
        Exit Sub
    
        ElseIf Len(New_To) = 0 Then
        New_To = "BLANK"
    
        Else
        Sheet2.ListObjects("Table1").ListRows(m).Range(1, Sheet2.ListObjects("Table1").ListColumns("To").Range.Column).Value = New_To
        Err.Clear
    
        End If


    On Error GoTo -1
    GoTo Resume_run


Else
Exit Sub


End If

Did I write it wrong somewhere?

 

[Lil2606]

Or if the cancel button would not be there that would do the trick too...

 

[Lil2606]

I have managed to write a working code, but I ran into another issue. I will post a new question with that one. This is completely resolved