Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox validation | Excel Discussion (Misc queries) | |||
textbox validation | Excel Programming | |||
textbox value validation | Excel Programming | |||
Textbox validation | Excel Programming | |||
textbox validation | Excel Programming |