VBA & Save File name HELP!

[Rusty10]

Hi Everyone!

Firstly what a great resource this site is! Now for my first post!

I have just started using the wonderful VB within Excel and my knowledge is zero at the moment so was hoing somebody may be able to help?

I currently have the following code and I would like to change it from defaulting to the C:\Documents & Setting folder to a specific folder within a shared network drive?

Sub SaveSheet()
'error trap
On Error GoTo Etrap

Dim MyCell
MyCell = ActiveCell.Value

'ask user to save
If MsgBox("Save new workbook as " & CurDir & "\" & MyCell & ".xls?", vbYesNo) = vbNo Then
Exit Sub
End If

'check value of activecell
If MyCell = "" Then
MsgBox "Please check the Cell Value", vbInformation
Exit Sub
End If

'save activeworkbook as new workbook
ActiveWorkbook.SaveAs Filename:=MyCell & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Etrap:

Beep
Exit Sub

End Sub

Any idea which part of the code I need to change? Am guessing it has something to do with the CurDir?

Many thanks!

[Bob Phillips]

Look at GetSaveasFilename within VBA help. It will allow you to brows for a directory and file and then save to there.

[Rusty10]

Hi Thanks but I don't understand I'm afraid?

Just looking for the line in VBA which will enable me to default to destination say:

T:\Main Documents\etc.\etc.

Thanks again!

[Bob Phillips]

Then that would be



ActiveWorkbook.SaveAs Filename:="T:\Main Documents\etc.\etc." & MyCell & ".xls"

[unmarkedhelicopter]

or better still :-
ActiveWorkbook.SaveAs Filename:="T:\Main Documents\etc.\etc.\" & MyCell & ".xls"

[mdmackillop]

Try to structure your code to avoid the multiple exit points. Also, check for "failure" first, before running lines which would be bound to fail.
Sub SaveSheet()
'error trap
On Error GoTo Etrap
Dim SaveName As String
'check value of activecell
If ActiveCell <> "" Then
SaveName = "T:\Main Documents\etc.\etc.\" & ActiveCell & ".xls"
If MsgBox("Save new workbook as " & SaveName & "?", vbYesNo) = vbYes Then
ActiveWorkbook.SaveAs SaveName
End If
Else
MsgBox "Please check the Cell Value", vbInformation
End If
Exit Sub
Etrap:
Beep
End Sub

[Rusty10]

Hi Guys

Just wanted to thank you for your help. Works like a charm!

Thanks a million!:hi: