Solved

Excel 97: Add dialog box to macro

Posted on 2002-03-30
9
301 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
  • 7
  • 2
9 Comments
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
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
Comment Utility
Hi Borleymsgs,

Any luck yet?

:O)Bruintje
0
 

Author Comment

by:borleymsgs
Comment Utility
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
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
Comment Utility
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
---------------------------
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
Comment Utility
---------------------------
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
Comment Utility
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
Comment Utility
>>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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now