PDA

View Full Version : Form If Statement



Emoncada
05-17-2007, 10:05 AM
I have a form that the user picks items from a cmbbox. How can I have an if statement in the next box so that when
cmbbox1 = "Desktop" Then TxtBox1 has to have at least 10 characters so if less than 10 characters give you a prompt "Please check Serial Number"
I would like to be able to edit this depending on the cmbbox item.

example

if cmbbox1:cmbbox10 = "Desktop" then TxtBox1:TxtBox10 should be >= 10 characters.
Else
MsgBox "Please Check Serial Number"
End If
if cmbbox1:cmbbox10 = "Lexmark Printer" then TxtBox1:TxtBox10 should be >= 6 characters.
Else
MsgBox "Please Check Serial Number"
End If

etc..
Then at the end

if cmbbox1:cmbbox10 = Anything else then leave as is.

Something like that hope that helps.

Bob Phillips
05-17-2007, 10:11 AM
Select Case cmbbox10.Value

Case "Desktop"
If Len(TxtBox1.Text) < 10 Then
MsgBox "Please Check Serial Number"
End If

Case "Lexmark Printer"
If Len(TxtBox1.Text) < 6 Then
MsgBox "Please Check Serial Number"
End If

'Case etc.

End Select

Emoncada
05-17-2007, 10:12 AM
Ok so I would need to place that in all the cmbbox's ?

Bob Phillips
05-17-2007, 10:41 AM
Same tricks we showed you before.

Emoncada
05-17-2007, 11:14 AM
Which one's.

Bob Phillips
05-17-2007, 11:22 AM
http://www.vbaexpress.com/forum/showthread.php?t=12820&page=2&highlight=cbo+As+MSForms.Combobox

post #27

Emoncada
05-18-2007, 07:12 AM
oK XLD thanks to your help i was able to make this work this is what I got.
Private Sub TxtSn1_AfterUpdate()
Select Case CmbBoxDesc1.Value

Case "HP DC7600"
If Len(TxtSN1.Text) < 10 Then
MsgBox "Please Check Serial Number"
End If

Case "HP DC 7700 SFF"
If Len(TxtSN1.Text) < 10 Then
MsgBox "Please Check Serial Number"
End If

Case "HP D530"
If Len(TxtSN1.Text) < 10 Then
MsgBox "Please Check Serial Number"
End If

Case "HP 17IN. TFT L1706"
If Len(TxtSN1.Text) < 10 Then
MsgBox "Please Check Serial Number"
End If

Case "HP LJ5550"
If Len(TxtSN1.Text) < 10 Then
MsgBox "Please Check Serial Number"
End If

Case "LEXMARK 9530"
If Len(TxtSN1.Text) < 7 Then
MsgBox "Please Check Serial Number"
End If

Case "LEXMARK 2490"
If Len(TxtSN1.Text) < 7 Then
MsgBox "Please Check Serial Number"
End If
End Select
End Sub

How can I have it set focus on TxtSn1 if you get the msgbox. I tried setfocus but i guess because there is a value in the txtbox it just tabs to the next txtbox.

Bob Phillips
05-18-2007, 08:10 AM
You could try a back tab



SendKeys "+{TAB}"

Emoncada
05-18-2007, 08:32 AM
Is there a simple way to loop it back to the msg if it's still wrong after this?

mdmackillop
05-18-2007, 01:43 PM
You could look at calling another sub to avoid repetition and simplify your code
Private Sub TxtSn1_AfterUpdate()
Select Case CmbBoxDesc1.Value
Case "HP DC7600"
Test 10
Case "HP DC 7700 SFF"
Test 10
'etc.
Case "LEXMARK 2490"
Test 7
End Select
End Sub

Sub Test(Ln As Long)
If Len(TxtSN1.Text) < Ln Then MsgBox "Please Check Serial Number"
End Sub

Bob Phillips
05-18-2007, 01:45 PM
which is exactly what

http://www.vbaexpress.com/forum/showthread.php?t=12820&page=2&highlight=cbo+As+MSForms.Combobox

post #27

says