thomas.szwed
12-07-2007, 09:06 AM
Hi Guys.....again!
This is my code for when a user goes to archive some records from the "For archive" sheet. Unfotunately i cant get it to work correctly! When the user gets to the second message which is the input box and enters in a file name of their choice it doesnt seem to do the event...and instead backs out and does the "vbCancel" event instead....Can ne1 help me???
Sub CreateArchive()
Sheets("For Archive").Activate
Dim msgResponse As String 'confirm delete
Application.ScreenUpdating = False
'get user confirmation
msgResponse = MsgBox("This will archive records from the 'For Archive' Sheet. Continue?", _
vbCritical + vbYesNo, "Archive Records")
Select Case msgResponse 'action dependent on response
Case vbYes
'Input box to name new file
If NewName = InputBox("Enter Month for filename", "Archiving Records", vbOKCancel) = vbOK Then
'Save it with the NewName and in the same directory as original
Worksheets("For Archive").Copy
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False
Range("A2:AC65000").ClearContents
Else
Exit Sub
End If
Case vbNo
Exit Sub
End Select
End Sub
Many Thanks!
This is my code for when a user goes to archive some records from the "For archive" sheet. Unfotunately i cant get it to work correctly! When the user gets to the second message which is the input box and enters in a file name of their choice it doesnt seem to do the event...and instead backs out and does the "vbCancel" event instead....Can ne1 help me???
Sub CreateArchive()
Sheets("For Archive").Activate
Dim msgResponse As String 'confirm delete
Application.ScreenUpdating = False
'get user confirmation
msgResponse = MsgBox("This will archive records from the 'For Archive' Sheet. Continue?", _
vbCritical + vbYesNo, "Archive Records")
Select Case msgResponse 'action dependent on response
Case vbYes
'Input box to name new file
If NewName = InputBox("Enter Month for filename", "Archiving Records", vbOKCancel) = vbOK Then
'Save it with the NewName and in the same directory as original
Worksheets("For Archive").Copy
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False
Range("A2:AC65000").ClearContents
Else
Exit Sub
End If
Case vbNo
Exit Sub
End Select
End Sub
Many Thanks!