Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving a workbook (Excel 2000)

    I have a form that I want the user, upon opening it, to enter a name for it in a cell on the first sheet. I want to designate the path for saving it and pick up the user's entered name as the file name. Is this possible? Is it done with VBA?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a workbook (Excel 2000)

    Yes, it can be done, and yes it is done with VBA. The code below, placed in the workbook open event routine will display an inputbox asking for a filename if cell A1 on sheet Sheet1 is empty. It will then put the name entered into cell A1 and save the file using the entered name in the directory C:MyPath.

    <pre>Dim strFName As String
    If Worksheets("Sheet1").Range("A1").Value = "" Then
    Do While Trim(strFName) = ""
    strFName = InputBox("Please enter a file name")
    Loop
    Worksheets("Sheet1").Range("A1").Value = strFName
    ActiveWorkbook.SaveAs Filename:="C:MyPath" & strFName, FileFormat:=xlWorkbookNormal
    End If
    </pre>


    To put the code in the workbook open routine, use Alt+F11 to open the VBA editor. In the project explorer on the left of the screen, find the workbook and double click on the ThisWorkbook object. In the VBA code window drop down the drop down list at the top left and select workbook. Then drop down the right drop down list and select Open from the list. You should now have a dummy workbook open event routine in the code window that looks like this:

    <pre>Private Sub Workbook_Open()

    End Sub
    </pre>


    Put the code above between those two lines.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •