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

Lil2606

Board Regular
Joined
Jan 29, 2019
Messages
76
Hi all, following basic example:

Code:
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!
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

Board Regular
Joined
Jan 29, 2019
Messages
76
Yeah couldn't make it work from the simple example, so here is my full fixit:

Code:
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 :(
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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.
Code:
[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]
 
Last edited:

Lil2606

Board Regular
Joined
Jan 29, 2019
Messages
76

ADVERTISEMENT

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:

Code:
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?
 
Last edited:

Lil2606

Board Regular
Joined
Jan 29, 2019
Messages
76
Or if the cancel button would not be there that would do the trick too...
 

Lil2606

Board Regular
Joined
Jan 29, 2019
Messages
76
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,428
Messages
5,528,698
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top