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

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

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" wrote:

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

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

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


WillRn wrote:

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

--

Dave Peterson

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?

"Dave Peterson" wrote:

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


WillRn wrote:

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

--

Dave Peterson

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

--
Regards,
Tom Ogilvy


"WillRn" wrote in message
...
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?

"Dave Peterson" wrote:

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


WillRn wrote:

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

--

Dave Peterson