If cancel list box don't enter FALSE in list

[Howard]

This works okay, except if you cancel out of entering something in the list, it enters FALSE. Don't want that.

Thanks,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

Dim strName As String

If Target = "Not on list" Then
strName = Application.InputBox("Add To List", _
"Add to list here", "Enter Whatever", , , , , 2)
Range("F1").End(xlDown).Offset(1, 0).EntireRow.Insert
Range("F1").End(xlDown).Offset(1, 0) = strName
End If

End Sub

 

[Claus Busch]

Hi Howard,

Am Wed, 28 Aug 2013 07:45:46 -0700 (PDT) schrieb Howard:

This works okay, except if you cancel out of entering something in the list, it enters FALSE. Don't want that.

try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub

Dim strName As String

If Target = "Not on list" Then
strName = Application.InputBox("Add To List", _
"Add to list here", "Enter Whatever", , , , , 2)
If strName = "" Or strName = "False" Then Exit Sub
Range("F1").End(xlDown).Offset(1, 0).EntireRow.Insert
Range("F1").End(xlDown).Offset(1, 0) = strName
End If

End Sub


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Wed, 28 Aug 2013 07:45:46 -0700 (PDT) schrieb Howard:






try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$B$1" Then Exit Sub



Dim strName As String



If Target = "Not on list" Then

strName = Application.InputBox("Add To List", _

"Add to list here", "Enter Whatever", , , , , 2)

If strName = "" Or strName = "False" Then Exit Sub

Range("F1").End(xlDown).Offset(1, 0).EntireRow.Insert

Range("F1").End(xlDown).Offset(1, 0) = strName

End If



End Sub





Regards

Claus B.

Nice little one line fix.
Thanks, Claus