Why Wont This Work? VBA Help Needed!

[Celtic_Avenger]

Why Does This Not Work Properly?

The Sub CommandButton2_Click() code ensures that specific data has bee
entered before allowing the user to continue,
If not the Sub Exits, If they have entered the needed data then I wan
to run the
SaveLock Sub.
However it will work up to the copy sheet to a new workbook part bu
then stops.
Why?

Should I put something different to

Call Savelock at the Else part of the first code?




Private Sub CommandButton2_Click()
Dim rng As Range

Set rng = Range("E6,E16")

If Application.CountA(rng) < 2 Then
MsgBox "Please Complete At Least The Following" & Chr(13) & Chr(13)
"Last Collection Call Date." & Chr(13) & "Lockout Prepared By."
vbInformation, "IMPORTANT!"
Exit Sub

Else
Call SaveLock

End If

End Sub




Private Sub SaveLock()
Dim LockOut

LockOut = MsgBox("Are You Sure You Wish To Create Your Lockout Reques
At This Time?", vbYesNo, "DAILY DRAT")
If LockOut = vbNo Then End

Sheets("Lockout").Select
Sheets("Lockout").Copy
ActiveWindow.DisplayHeadings = False

Dim fn As Variant
fn
Application.GetSaveAsFilename(InitialFileName:=Worksheets("Lockout").Range("J2").Value
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Save This Lockout Request Into Your Lockouts Folder! Do No
Change The File Name Shown!")
If TypeName(fn) = "Boolean" Then
ActiveWorkbook.Close SaveChanges:=False
Sheets("Lock6").Select
MsgBox "Your Lockout Request WAS NOT Saved", vbOKOnly, "SAV
CANCELLED!"
Exit Sub
End If
ActiveWorkbook.SaveAs fn
ActiveWorkbook.Close SaveChanges:=False
ActiveWorkbook.Sheets("Dispatch").Select
MsgBox "Your Lockout Request Has Been Saved And Closed." & Chr(13)
"Please Email To Your District Manager For Processing!"
End Sub


I hope someone can help me with this.

Thanks

Celtic_Avenger
:confused

 

[Bob Kilmer]

However it will work up to the copy sheet to a new workbook part but

then stops.

The code stops executing or the expected result is not seen in Excel?

Arrange windows so you can see your code and the Excel UI (the workbooks) at
the same time. Put a breakpoint at the line Sheets("Lockout").Copy (put your
cursor on the line and press F9). Now run the code. When code execution
stops, press F8. In the Excel UI, see if a new workbook was created with the
copied sheet in it. Continue to step thru (F8) or run the code, checking to
see that what you expect is happening. or if not why not.