VBA IF Statement Problem

[nicolavision]

Hi

I have a command button that has the following code to it - but it's failing due to the IF Statements.

What the code should do is check the first IF statement is that's OK then move to the next IF statement. if the first statement isn't OK then run the then statement and start checking again.

I have know idea where I've gone wrong please help!

Private Sub cmdOK_Click()
On Error GoTo Handler

If txtEmployeeNo.Value = "" And txtEmployeeNo.Visible = True Then
MsgBox "Please enter Employee Number", vbInformation, "Data Required"
txtEmployeeNo.SetFocus

Else

If txtFirstName.Value = "" And txtFirstName.Visible = True Then
MsgBox "Please enter First Name", vbInformation, "Data Required"
txtFirstName.SetFocus

Else

If txtLastName.Value = "" And txtLastName.Visible = True Then
MsgBox "Please enter Last Name", vbInformation, "Data Required"
txtLastName.SetFocus

Else


ActiveWorkbook.Sheets("User Information").Activate
Range("A1:B15").Select
Selection.Copy
Workbooks.Add
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.DisplayZeros = False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Application.CutCopyMode = False
ChDir "H:\"
   ActiveWorkbook.SaveAs Filename:="H:\UserInformation.xls", FileFormat:= _
   xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End If
End If

Handler:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Unload Me
Application.DisplayAlerts = False
Sheet1.Visible = xlSheetHidden
ActiveWorkbook.Sheets("Standard Expense Claim").Activate
Range("A5").Select

End Sub

Thanks

 

[Te4t0n]

Private Sub cmdOK_Click()
On Error GoTo Handler

if txtEmployeeNo.Value <> "" And txtEmployeeNo.Visible = True then
if txtFirstName.Value <> "" And txtFirstName.Visible = True then
if txtLastName.Value <> "" And txtLastName.Visible = True then
ActiveWorkbook.Sheets("User Information").Activate
Range("A1:B15").Select
Selection.Copy
Workbooks.Add
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.DisplayZeros = False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Application.CutCopyMode = False
ChDir "H:\"
ActiveWorkbook.SaveAs Filename:="H:\UserInformation.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
end if
MsgBox "Please enter Employee Number", vbInformation, "Data
Required"
txtEmployeeNo.SetFocus
end if
MsgBox "Please enter First Name", vbInformation, "Data Required"
txtFirstName.SetFocus
end if
MsgBox "Please enter Last Name", vbInformation, "Data Required"
txtLastName.SetFocus

Handler:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Unload Me
Application.DisplayAlerts = False
Sheet1.Visible = xlSheetHidden
ActiveWorkbook.Sheets("Standard Expense Claim").Activate
Range("A5").Select

End Sub

i think this should work, if not tell me the error you get

 

[nicolavision]

Hi

It asks for last name even if the name is completed and then runs the handler error code and closes the spreadsheet

Thanks for your help
[/code]

 

[Te4t0n]

Private Sub cmdOK_Click()

if txtEmployeeNo.Value <> "" And txtEmployeeNo.Visible = True then
if txtFirstName.Value <> "" And txtFirstName.Visible = True then
if txtLastName.Value <> "" And txtLastName.Visible = True then
ActiveWorkbook.Sheets("User Information").Activate
Range("A1:B15").Select
Selection.Copy
Workbooks.Add
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.DisplayZeros = False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Application.CutCopyMode = False
ChDir "H:\"
ActiveWorkbook.SaveAs Filename:="H:\UserInformation.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
end if
MsgBox "Please enter Employee Number", vbInformation, "Data
Required"
txtEmployeeNo.SetFocus
end if
MsgBox "Please enter First Name", vbInformation, "Data Required"
txtFirstName.SetFocus
end if
if txtLastName.Value = "" And txtLastName.Visible = True then
MsgBox "Please enter Last Name", vbInformation, "Data Required"
txtLastName.SetFocus
end if

End Sub

1) that will fix the if statment
2) sorry for taking so long
3) i dunno much about vba error handling but i dont like that onerror stuff
4) hope this helps

 

[nicolavision]

Hi
That didn't seem to work either.

All I need it to do is Check each control and if it doesn't meet the IF statement then cancel the OK routine and go to the error

Thnaks

 

[chiello]

Private Sub cmdOK_Click()
On Error GoTo Handler

If ... Then
...
Else
If ... Then
...
Else
If ... Then
...
Else
...
End If
End If

Handler:
...
End Sub[/code]

Thanks

Hi nicolavision,
It seems that in your code there are 3 if statements versus 2 end if. This is surely a mistake... is it enough to solve your problem??

 

[nicolavision]

Thanks but I think it's more to do with the actually code.

It only asks for the LastName to be completed even is it's completed

Thanks