Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 97: Add dialog box to macro

Posted on 2002-03-30
9
Medium Priority
?
311 Views
Last Modified: 2012-05-04
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
Comment
Question by:borleymsgs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
9 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6907741
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6922928
Hi Borleymsgs,

Any luck yet?

:O)Bruintje
0
 

Author Comment

by:borleymsgs
ID: 6938881
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 44

Expert Comment

by:bruintje
ID: 6941258
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
 
LVL 44

Accepted Solution

by:
bruintje earned 400 total points
ID: 6958461
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7004646
---------------------------
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
 
LVL 44

Expert Comment

by:bruintje
ID: 7019529
---------------------------
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
 

Author Comment

by:borleymsgs
ID: 7025039
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
 
LVL 44

Expert Comment

by:bruintje
ID: 7026217
>>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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question