Prompt user to enter cell value to save file name (with cell value)

Hi,
I'm new to VBA and couldn't find the exact answer to my problem in the postings. I spent 2 hours looking and trying different code examples. Can you please help?

Upon opening file "test," prompt user to "Enter cell value in A1" (e.g. name=Joe) to save file name as "test Joe.xls"

Thanks!

Hi stama100, I made a simple script for you to refine, that might do the trick.

Private Sub Workbook_Open()
    On Error GoTo Fin
    Dim strName As String, FileName As String
    Dim wbFileFormat As String

    Select Case ThisWorkbook.FileFormat
    Case 56
        wbFileFormat = ".xls"
    Case 51
        wbFileFormat = ".xlsx"
    Case 52
        wbFileFormat = ".xlsm"
    End Select

    FileName = Replace(ThisWorkbook.Name, wbFileFormat, "")

    strName = InputBox(Prompt:="Enter cell value (e.g. Joe)", Title:="Save name", Default:="Name")
    If strName = vbNullString Then Exit Sub

    'Save in the worksheet you want, although not needed here
    Worksheet("Sheet1").Range("A1").Value = strName

    ThisWorkbook.SaveAs (FileName & " " & strName & wbFileFormat)
Fin:
End Sub

Sorry that didn't work. When I compiled, I got nothing.

As you want the procedure to be executed when the workbook is opened, you need to store the routine in the "ThisWorkbook" module.

The code I wrote was untested, please change the following (as well as the Sheet name you want to store the name in, e.g. Sheet1)

Worksheet("Sheet1").Range("A1").Value = strName

to

Worksheets("Sheet1").Range("A1").Value = strName

Hopefully it should be working!