PDA

View Full Version : [SOLVED] User Form Help Again



austenr
11-12-2004, 11:36 AM
I want to put this code in the UserForm to ask the user to save the file as: and then pick a name. If the file already exists delete it. Will this work? I have attached my zip file for your consideration. Thanks in advance:


Dim fName As String, FileNm As String
'GET THE NEW FILENAME FROM THE CURRENT SHEET
FileNm = ActiveSheet.Range("H1").Value & ActiveSheet.Range("I1").Value
fName = ActiveWorkbook.Path & "\" & FileNm & ".xls"
Debug.Print fName
'IF THE FILE ALREADY EXISTS THEN DELETE IT ---------------------------------*
If Dir(fName) <> "" Then Kill fName
'COPY THE WORKSHEET
ThisWorkbook.ActiveSheet.Copy
'SAVE THE NEW WORKBOOK
ActiveWorkbook.SaveAs Filename:=fName
ActiveWorkbook.Close

mdmackillop
11-12-2004, 12:50 PM
Hi Austen,
Your code seems OK. Give it a name eg Sub DoCopy() and call that from your existing code


If OptionButton1 = True Then
ActiveWorkbook.Save
DoCopy
Unload Me
End If

You might want to include a Check item in your code, it depends how this data is entered. Try changing the FileNm line to:


FileNm = InputBox("New File Name", "Check name", ActiveSheet.Range("H1").Value & ActiveSheet.Range("I1").Value)
If FileNm = "" Then Exit Sub

austenr
11-12-2004, 03:53 PM
Hey...Thanks I will give it a try....

Jacob Hilderbrand
11-12-2004, 03:59 PM
You don't need to delete the file if it exists, just save over it. Use

Application.DisplayAlerts =False
to stop the warning message from popping up.