Tempermental Code seems protection related

[pcollins]

I have some code that is supposed to save the active workbook as a comma separated text file. The code seemed to work fine until I went under vba project properties and made my code protected so other users could not view it then it quit working. Is there something here that is bad coding practice?

[VBA]Sub csv_save()
YesNo = MsgBox("Do you wish to Save as a CSV File?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.csv), *.csv")
If filesavename <> False Then
MsgBox "Save as " & filesavename
ActiveWorkbook.SaveAs Filename:=filesavename, FileFormat:=xlCSVMSDOS, Password:="", _
writerespassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End If
Case vbNo
End Select
'This will immediately close workbook without prompting to save
'ActiveWorkbook.Close savechanges:=False
End Sub
[/VBA]

[Bob Phillips]

Looks fine to me.

Ahev you removed porotection and tried it?

[pcollins]

Yes

With the protection on it seems to work about half the time, with it off it seems to work every time.

The exact error I am getting is:

Run-time error '1004'
Method 'SaveAs' of object '_Workbook' failed

Does the underscore in front of workbook mean anything?

[Paul_Hossler]

When ever I've had to write an XLS as a CSV, I've always found it better to Copy the sheet I want to a new WB first and save that WB,

Since I'm then dealing with 2 WBs, I set variables to the original and the copy so that I don't need to remember which is Active.

Putting some additional lines into yours to Set the WBs and to copy the active sheet from the original WB seems to work with protection

Maybe some of the more experienced members can explain why that message comes up, but this might work as a workaround for you.


Paul

[VBA]
Option Explicit

Sub csv_save()
Dim YesNo As Long
Dim filesavename As Variant
Dim wb1 As Workbook, wb2 As Workbook

YesNo = MsgBox("Do you wish to Save as a CSV File?", _
vbYesNo + vbCritical, "Caution")

Select Case YesNo
Case vbYes
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.csv), *.csv")

If filesavename <> False Then

MsgBox "Save as " & filesavename

'remember this workbook
Set wb1 = ThisWorkbook

Application.ScreenUpdating = False

'copy active sheet into new WB
ActiveSheet.Copy
Set wb2 = ActiveWorkbook

Application.DisplayAlerts = False

wb2.SaveAs Filename:=filesavename, _
FileFormat:=xlCSVMSDOS, _
Password:="", _
writerespassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
wb2.Close savechanges:=False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If

Case vbNo

End Select

'This will immediately close workbook without prompting to save
wb1.Close savechanges:=False
End Sub

[/VBA]

[pcollins]

Thanks that seems to work, not sure if I should mark this solved though since I still want to know why my original code generates an error.

[unmarkedhelicopter]

Post your workbook and we'll take a look