VBA IF Statement Problem

nicolavision

New Member
Joined
Oct 21, 2004
Messages
31
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!

Code:
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 :oops:
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Te4t0n

New Member
Joined
Jan 23, 2005
Messages
21
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

New Member
Joined
Oct 21, 2004
Messages
31
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

New Member
Joined
Jan 23, 2005
Messages
21
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

New Member
Joined
Oct 21, 2004
Messages
31

ADVERTISEMENT

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

Well-known Member
Joined
Jan 18, 2005
Messages
848
nicolavision said:
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 :oops:

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

New Member
Joined
Oct 21, 2004
Messages
31
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 :rolleyes:
 

Forum statistics

Threads
1,147,668
Messages
5,742,514
Members
423,734
Latest member
123hmMission

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top