File Save Macro "End If" Problems . . .

[Guest]

I am trying to modify a macro to enable my user to save a file locally on
their hard drive with a name and location that they specify. I keep getting
an "End if without Block If" compile error. Being very new to VBA code, I
can't seem to get the macro to work.

It is as follows:

Private Sub SaveMe_Click()
If NewName <> False Then
If Dir(NewName) <> "" Then Select Case MsgBox("File Exists. Overwrite
?", vbYesNoCancel + vbQuestion)

Case vbYes
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True

Case vbNo
Do
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\" & NameAk,
FileFilter:="Excel Workbooks (*.xls), *.xls")
If NewName = False Then Exit Sub
Loop Until Dir(NewName) = ""
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
Case Else
Exit Sub
End Select
Else
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
End If
End If
End Sub

I know it is probably very simple, but I just can't seem to find the problem
.. . .

WillRn

 

[ManualMan]

Yes I can,

If Dir(NewName) <> "" Then Select Case MsgBox("File Exists.

Overwrite ?", vbYesNoCancel + vbQuestion)

Using a Block If You should always put the text directly behind "Then "
on the next line, or vba will consider the first line as a complete
if-statement, so:

If Dir(NewName) <> "" Then
Select Case MsgBox("File Exists. Overwrite ?", vbYesNoCancel +
vbQuestion)

would do the trick. Took me long to find that one out, too!
Regards,
ManualMan

 

[Guest]

Well the good news is that the error code went away. The bad news is that
nothing happens. No messages or choices at all.

help!

 

[ManualMan]

Ah! Now that is strange.

Tried to sim your problem. The code runs fine, but

If NewName = "" or Null Then no messages etc
If NewName = 24 (some integer) Type Mismatch
If NewName = "dkdkd" (some string) it just works great!!!
So check your input!

Regards,
ManualMan

 

[Dave Peterson]

I suggested this for a similar post:

Option Explicit
Sub testme01()
Dim myFileName As Variant
Dim OkToSave As Boolean
Dim resp As Long

Do
myFileName = Application.GetSaveAsFilename _
(filefilter:="Excel files, *.xls")
If myFileName = False Then
Exit Sub
End If

OkToSave = True
If Dir(myFileName) = "" Then
'do nothing special
Else
resp = MsgBox(prompt:="Overwrite Existing file?", _
Buttons:=vbYesNoCancel)
Select Case resp
Case Is = vbCancel
MsgBox "Try again later"
Exit Sub
Case Is = vbNo
OkToSave = False
End Select
End If

If OkToSave Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myFileName, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Exit Do
End If
Loop

End Sub

 

[Guest]

Dave,

Thanks for the code it works like a charm. I do have an additional question
however.

Is there a way to get it to save the file under a different name and then
return to the original workbook.

For example, Save it as "PFSNov_Copy.xls" but return to "PFSNov.xls" as the
active workbook?

 

[Tom Ogilvy]

See answer in this newsgroup to your later posting of this question.