• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Excel 97: Add dialog box to macro

I very much of a novice at VB and writing macros.  However, I have successfully written a macro to prepare a weekly workbook for a new week.  It deletes the old week and adds a new week.  This new workbook should be saved under a new name and I want to make sure that the user does this.  Hence I want to add a dialog box that reminds the user to save the workbook under a new name. Or, even better, a prompt would appear asking the user what the new name of the file should be and then saving it under that name in the current folder.

Hope someone can lead me through this.  Thanks.
0
borleymsgs
Asked:
borleymsgs
  • 7
  • 2
1 Solution
 
bruintjeCommented:
Hi borleymsgs,

What you can do is this, since you succesfully created a working piece of code already i provide you with an addition to the current setup

This sub will save the current workbook under the name the user enters, there are a few checks for empyt input etc...but you can play around with that

You can use this sub by
-put it under a custom keyboard shortcut
-or calling from code like

sub yoursub
.....dosomething
  call SaveUnderNewFileName
...endsomething
end sub

Sub SaveUnderNewFileName()
Dim strFileName

  Rem now get user input
  strFileName = InputBox("Enter new file name", "New Filename")
  Rem check to see if the user left the box empty
  If strFileName <> "" Then
    Rem check to see if the user used the same name
    If strFileName & ".xls" <> ActiveWorkbook.Name Then
      Rem save the active workbook in the current directory under the new name
      ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & strFileName & ".xls"
    Else
      MsgBox "Same name entered!"
    End If
  Else
    Rem the new name was empty so give the user a second chance
    strFileName = InputBox("Try it another time to enter a new file name", "New Filename")
    If strFileName <> "" And strFileName & ".xls" <> ActiveWorkbook.Name Then
      Rem save the active workbook in the current directory under the new name
      ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & strFileName & ".xls"
    Else
      MsgBox "Same or no name entered!"
    End If
  End If
 
End Sub

HTH:O)Bruintje
0
 
bruintjeCommented:
Hi Borleymsgs,

Any luck yet?

:O)Bruintje
0
 
borleymsgsAuthor Commented:
I work part time here so it is easy to put projects on the back burner--sorry for the delay

You gave me a great start, but I am having trouble with the error checking aspects.  If I use a proper file name, the code works fine.  

However, if I do not change the filename, a Windows message comes up asking if I want to overwrite the existing file.  That is OK, but if I say "No", then a VB error message comes up, giving me the option to debug.  The error message is VB run-time error '1004' -- Method 'Save As' of object '_Workbook' failed.  Any ideas?

However, after playing around with this, I've decided that maybe the best way of approaching my desires is for the VB code to bring up the Windows "Save As" dialog box with the current file name showing in the text box.  Then the user would merely modify the file name and save the file.  That way, the odds of saving the file with exactly the right name increases, plus the same error checking for identical file names would be run.  Could you suggest code for this?

Thanks for your help.  This is helping me break the ice for writing VB code. Do you have any suggestions for good reference sources for Excel VBA code writing?

Thanks.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bruintjeCommented:
Hi borleymsgs,

-found a piece of code
-opening a file (harcoded for the sample)
-asking for user input
-and saving it in the current directory under the name the user gives that can also be the same file
-read the comments in the code
-if you don't want to save at all cost then remove the display alerts then a do you want to overwrite will appear

Private Sub SaveAs_Macro()
Dim File_To_Open_Name As String
Dim File_To_SaveAs_Name As String
'For Clarity Assigns Name of file to open
'to File_To_Open_Name string variable
File_To_Open_Name = "C:\temp\error.ini"
'opens workbook
Workbooks.Open FileName:=File_To_Open_Name
'Application.GetSaveAsFilename open a typical windows
'saveAs dialog and gets a string from the user's choice
'including the entire path, but does not actually save the file
File_To_SaveAs_Name = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.cvs), *.cvs")
'turns off the "do you want to save changes" dialog
'you obviously want the user to save the new file. Right?
'If not, delete the next and last line
Application.DisplayAlerts = False
'from your original code with File_To_SaveAs_Name string
'collected from user with Application.GetSaveAsFilename
ActiveWorkbook.SaveAs FileName:=File_To_SaveAs_Name, FileFormat:= _
xlCSV, CreateBackup:=False
'your code
ActiveWindow.Close
Application.DisplayAlerts = True
'for more help do a help search for "GetSaveAsFilename" in VB
End Sub

:O)Bruintje

PS i'll post a list of sources later on
0
 
bruintjeCommented:
---------------------------
Hi borleymsgs,

Any luck yet to try the suggestions in this thread?
Please provide some feedback to the experts willing to spend their time and try to solve your problem.

---------------------------
Thanks
:O)Bruintje
---------------------------
0
 
bruintjeCommented:
---------------------------
Hi borleymsgs,
 
Any update on this question?

in an effort to clean up old open questions

+----------------------------------------------------+
Your options are:

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the
participants why you wish to do this, and allow for Expert response.  This choice will include a refund
to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information
outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
 --> Post comments for expert of your intention to delete and why
 --> You cannot delete a question with comments, special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below, include the question

QID/link. http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:
Click you Member Profile to view your question history and keep them all current with updates as the
collaboration effort continues. http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
+----------------------------------------------------+


+----------------------------------------------------+
IMPORTANT #1
+----------------------------------------------------+
EXPERTS:    Please leave any comments regarding this question here on closing recommendations
if this item remains inactive another seven (7) days.
+----------------------------------------------------+


+----------------------------------------------------+
IMPORTANT #2
+----------------------------------------------------+
Bruintjes recommendation
-PAQ
-Points to Bruintje
+----------------------------------------------------+


---------------------------
Thanks
:O)Bruintje
---------------------------
0
 
borleymsgsAuthor Commented:
Sorry for the delay--as mentioned before, I work part-time here, so the project had to take a back seat to others, plus I have been on vacation.  I trust that the code will work and appreciate the help.  Also, thanks for the references.  When I get back to the project, I may have questions.  In the meantime, I will help in clearing up old business and award the points.  Thank you for the reminder.
0
 
bruintjeCommented:
>>When I get back to the project, I may have questions

then concerning this you can ask them here

or better contact me by email since i don't get notif's on closed questions :(

mulbum@worldonline.nl
and mention the EE question or else it will end up in my deleted items bin :)
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now