VBA Validation - textbox must not be blank
 

Hi,

bascially i've got one list box and next to that i have a tt reaso
box.
I've got vba to do the following:
If listbox = TT then ttreasonbox = inputbox("Please enter you
reason")

Is there anyway to get this, so that the TT reason box cannot be blank
So if TT is selected, there must be a reason in tt textbox?

Cheer

--
harpscardif
-----------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=47878

VBA Validation - textbox must not be blank
 

This code written for controls embedded on a worksheet, so modify it if
needed for a userform:

Private Sub ListBox1_Change() ' Listbox1 is the list of choices including "TT"

ActiveSheet.TTReason.Value = "" ' assumes you need to erase the reason if
you select anything else
If ListBox1.Value = "TT" Then
While ActiveSheet.TTReason.Value = ""
' Get input, and also use Trim() to avoid user putting in only a
space to get past prompt:
ActiveSheet.TTReason.Value = Trim(InputBox("Please enter your reason
(required):"))
If ActiveSheet.TTReason.Value = "" Then _
MsgBox "You must enter a reason - blank not allowed",
vbExclamation, "INVALID REASON:"
Wend
End If

End Sub

--
- K Dales


"harpscardiff" wrote:


Hi,

bascially i've got one list box and next to that i have a tt reason
box.
I've got vba to do the following:
If listbox = TT then ttreasonbox = inputbox("Please enter your
reason")

Is there anyway to get this, so that the TT reason box cannot be blank.
So if TT is selected, there must be a reason in tt textbox?

Cheers


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=478782

VBA Validation - textbox must not be blank
 

Hi, Thank for your reply.

I'm its not recognising the wend, even though there a while present?

here the code I coverted slightly:


Code
-------------------

Private Sub txtTTReason_Change() ' Listbox1 is the list of choices including "TT"

Me.txtTTReason.Value = "" ' assumes you need to erase the reason if you select anything else
If cboPaymentMethod.Value = "TT" Then
While Me.txtTTReason.Value = ""
' Get input, and also use Trim() to avoid user putting in only a space to get past prompt:
Me.txtTTReason.Value = Trim(InputBox("Please enter your reason(required):"))
If ActiveSheet.txtTTReason.Value = "" Then
txtTTReason.Value = MsgBox("You must enter a reason - blanks not allowed", vbExclamation, "INVALID REASON:") = vbOK
Wend

End If
End If


End Sub


Error i'm getting is compile error, Wend without Whil

--
harpscardif
-----------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=47878

VBA Validation - textbox must not be blank
 

The error is because you have an IF in between the While and Wend that is not
"closed out" with an End If. They can't overlap like that, an IF that starts
in a While loop has to end inside the loop. It would work if you made the IF
statement all in one line, but if you make it a multiple line statement you
need the End If inside the loop.

I am also not sure why you would need this line:
txtTTReason.Value = MsgBox("You must enter a reason - blanks not allowed",
vbExclamation, "INVALID REASON:") = vbOK

The MsgBox is needed to let the user know why they can't move on, but I
don't think you want to set txtTTReason equal to the answer (which comes out
as True here the way you have it: MsgBox("...") = vbOK will be True when the
person clicks the OK button, so you are really saying txtTTReason.Value =
(did the person click OK)?
But you don't want txtTTReason to be equal to "True"; you want to leave it
as "". So just use the MsgBox on its own.

Here is the correction - note the line continuation symbol _ at the end of
the If line so it will turn it into a single-line statement; and also note
how I have the MsgBox statement:

Me.txtTTReason.Value = "" ' assumes you need to erase the reason if you
select anything else
If cboPaymentMethod.Value = "TT" Then
While Me.txtTTReason.Value = ""
' Get input, and also use Trim() to avoid user putting in only a space to
get past prompt:
Me.txtTTReason.Value = Trim(InputBox("Please enter your reason(required):"))
If ActiveSheet.txtTTReason.Value = "" Then _
MsgBox "You must enter a reason - blanks not allowed", vbExclamation,
"INVALID REASON:"
Wend
End If

--
- K Dales


"harpscardiff" wrote:


Hi, Thank for your reply.

I'm its not recognising the wend, even though there a while present?

here the code I coverted slightly:


Code:
--------------------

Private Sub txtTTReason_Change() ' Listbox1 is the list of choices including "TT"

Me.txtTTReason.Value = "" ' assumes you need to erase the reason if you select anything else
If cboPaymentMethod.Value = "TT" Then
While Me.txtTTReason.Value = ""
' Get input, and also use Trim() to avoid user putting in only a space to get past prompt:
Me.txtTTReason.Value = Trim(InputBox("Please enter your reason(required):"))
If ActiveSheet.txtTTReason.Value = "" Then
txtTTReason.Value = MsgBox("You must enter a reason - blanks not allowed", vbExclamation, "INVALID REASON:") = vbOK
Wend

End If
End If


End Sub


Error i'm getting is compile error, Wend without While


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=478782