PDA

View Full Version : Input Box not working



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!

rory
12-07-2007, 09:12 AM
NewName will be the Filename, therefore this will never be true:
If NewName = InputBox("Enter Month for filename", "Archiving Records", vbOKCancel) = vbOK Then

Use:
NewName = InputBox("Enter Month for filename", "Archiving Records", vbOKCancel)
If NewName <> "" Then

Bob Phillips
12-07-2007, 11:47 AM
InputBox does not return vbYes, vbNo, vbOK, or vb anything, that is MsgBox.

InputBox returns the value that you input, or a null string if you cancel.