vba code for saving file

[Michael]

Hi,

I want to create a button that save (saveas) the workbook where the filename
should be the string thats in cell B2
Anyone?

txs

 

[J.E. McGimpsey]

One way:

Public Sub CommandButton1_Click()
Const ERRSTR As String = "File not saved." & _
vbNewLine & vbNewLine
Dim fName As String
On Error GoTo Handler
With ActiveWorkbook
fName = .Sheets("Sheet1").Range("B2").Text
If Len(Trim(fName)) = 0 Then _
Err.Raise 32769
If Mid(fName, Len(fName) - 3, 1) <> "." Then _
fName = fName & ".xls"
.SaveAs FileName:=fName
End With
Exit Sub
Handler:
If Err.Number = 32769 Then
MsgBox ERRSTR & "Sheet1!B2 is empty"
Else
MsgBox ERRSTR & "Check Sheet1!B2 for valid filename."
End If
End Sub

You could add more error checking, of course. If you don't want to
see the dialog asking if you want to overwrite an existing file,
wrap the .SaveAs line with

Application.DisplayAlerts = False
.SaveAs FileName:=fName
Application.DisplayAlerts = True

 

[Michael]

Txs m8,

You put me on the right track here, much appriciated!


Michael