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
