every time i choose Action X, i have a series of warning messageboxes appear. i want to run some code so that default option for each message box is selected every time a message box appears. this way i don't need to press Enter every time for each message box.
View 4 RepliesI have an excel document which automatically runs a macro when it opens. The macro itself opens various excel documents, copies, Pastes and Deletes work sheets then saves the updated files in a new location. I simply used the Application. ScreenUpdating = False at the start of the macro and Application.ScreenUpdating = True at the end of the macro to hide all the various opening and closing and formatting of workbooks.
My aim is for the user to be able to open the document, wait a few moments while the macro runs, then be presented with the finished article when the macro ends without any need for interaction. The problem I am getting is as the macro runs, various windows pop up with messages such as "The file C:... already exists do you wish to replace the file" with a yes or no wchoice and "Data may exist in the sheet(s) selected for deletion. with a delete or cancel choice To Permanently delete the data press Delete". What I want to be able to do is stop these windows popping up and for the macro to go ahead with replacing the file or deleting the sheets without being manually told each time.
I have put a macro in my spreadsheet to clear all hyperlinks it works great but now I keep getting the privacy warning message.
I have set the macro settings to accept all and put the spreadsheet in the trusted locations......but the warnings still keep coming
I do not want to turn off all messages just this one.
Is there a way to have a reminder/warning message pop up before a user leaves a specific sheet?
I need users to sort a tool list before they leave the sheet and move on to a different sheet?
The name of the sheet is "Unit Tools". The command button is named "Sort Tool List"
I have a an Excel workbook (wb1) with 4 worksheets.
I also have a personal.xls that contains 4 worksheets.
wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc..
When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it.
I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work.
I know that the code will need to be copied to any existing workbooks. I have the code to do that.
That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( )
Me.Saved = True
End Sub
Private Sub Workbook_Open()
ThisWorkbook.Saved = True
End Sub
I have set up some cells with formulas to be worked out in the future. But the #VALUE! error message appears because there is nothing to work out yet! I've attached the sheet
View 5 Replies View RelatedI've been setting the above property to TRUE after making some changes in the Workbook_open event in order to stop annoying messages if you haven't physically made changes.
Unfortunately, it doesn't seem to copy through to the workbook_beforeclose event and I'm being asked if I want to save changes even though the macro made the changes and then set the property.
Here's my code for the open event :-
Private Sub Workbook_Open()
Application.ScreenUpdating = False
On Error Resume Next
For counter = Worksheets.Count To 2 Step -1
Sheets(counter).Visible = xlVeryHidden
Next
Sheets("Desktop").Visible = True
Sheets(1).Visible = xlVeryHidden.....
I have a macro here that turns exported text into delimited text, but it keeps prompting the user "Do you want to replace the cells"...can I somehow automatically bypass this prompt and always say yes?
View 2 Replies View RelatedIs there a way (via Menues and or VBA) to cancel the pop-up of the Alert Window when the user deletes a sheet(s) ?
(I mean sheets that contain Data)
I would like a macro that prompts a user to title the first row of a worksheet. My coworker told me to use a message box with a defined input variable, but I have no idea how to do that.
View 14 Replies View Relatedhow do u add error message, so that if you do not put the correct type of value in a input box and a error message should come for you to re do it again
error message should not take letters, signs etc onli numbers and if number 0 is entered it should stopped.
if it is at all possible (and if so, how) to CREATE your own error message in Microsoft Excel.
Basicaly, I am wanting to create an error message if two cells' figures do not match up.
As said a box appears with information guiding the user to what should be entered.
I inherited this spreadsheet and would like to edit the contents of this box and also learn how to make them myself.
Also I'd like to note that the box does not respond to right clicking
I have an appoinment calendar that is one month per worksheet
- Column headings 09:00 - 20:00
- Row headings 'days and dates of the month' (split into two merged cells of two)
****************See attachment 'Calendar Format.jpg'****************
I have then created a UserForm which has two dropdown combo boxes
****************See attachment 'UserForm.jpg'****************
What I want to do is have a way of using the dropdown boxes to select a column and row reference to enter the other data into the four lines in the outlined box area (see Calendar Format.jpg)
I want to be able to select a date - and to have only the dates from the sheet showing would be a bonus! - and have the script use that as its row reference, followed by selecting the time and having it add the column reference, and so selecting the correct cell to be able to then add all the other pieces of data.
I would just set it up so that it all worked by selecting the first cell, then adding, but I'm worried about other users not doing that and so causing things to be misplaced or overwritten!
I'm more than willing to change most things, but I do need to have those four pieces of info, and I only need to have a simple calendar.
If anything is not clear, please ask and I'll try to clarify further!
Forgot to add - In the pics I'm using 2007, but I want to be able to run this on 2000/2003 as that's what's in the offices and it's going to be shared on the server.
I need a quick code to where if a user attempts to open my workbook and they have any other workbooks open at the time that it prompts them with a yes no msgbox asking them to save before continuing. Really i just need the code to tell if there is another workbook open in excel.
View 3 Replies View RelatedI am not new to excel - but I have never required the use of advanced excel features before today - so I apologize in advance. I am a health professional student (graduate level) assisting my preceptor in developing patient management mechanisms.
I am building an excel spreadsheet to manage current medications for several patients.
I have built the spreadsheet with the first 'sheet' being a directory and subsequent sheets being patient data files(one sheet per patient).
I am now in the process of creating a macro to build new patient data files (a new sheet for a new patient). I recorded a macro to build the new sheet with the proper formatting of information. What I am trying to do now is automate the naming and linking of the new sheet.
What I am really after is how can you create a "prompt" such that after activating the macro button it asks:
Patient last name:
Patient first name:
Then uses this data to:
1. Rename the newly made sheet to 'last, first'
2. Title the newly made sheet with 'last, first'
3. Create an entry in the directory in the 'last, first' format
4. Finally, build the hyperlink from the directory to the newly created sheet
I have a workbook with extensive code of wich the VBE project is locked for viewing. Since i have distributed the workbook i had some users that gets prompt for a password when trying to run some code trough a button on a sheet. I have tried recreating this error by increasing the macro security level and unticking "trust access to vbe project" since i thought that is where the problem lies.
But i had no luck in recreating the error. I came accross some posts that had a similar error when you exit/close the workbook, wich was a problem with google desktop search being installed on the machine. Any ideas on why the user is being asked for a password ?, the code that should run does NOT try to alter/change anything in the code/project itself, so that shouldnt be the problem, but i can't think of anything else.
Workbook 1 has references to workbooks 2,3,4,5,6. When I click the top cross and choose Save All with Workbook 1 active, the deactivate event in workbook 6 runs to the end as it tries to close, but it can't close because workbook 1 references it. So nothing further happens until I click the top cross again. Then everything closes without any more prompts. Not good. If I could ensure workbook 1 closes first that should solve the problem. There may be another way of course.
I have attached 2 blank files to show the problem. test1 references test2. Open test1. It will open test2. Make a change to both files, activate test 1 and click the top cross, choose save all. Nothing happens usually.
Is there a way to disable all error pop up messages from coming up due to an invalid entry in a cell that has data validation? So when a user types in a value, it doesn't pop up saying "retry, cancel, etc"? Just to save time and make it more user friendly, it would be nice to just dis-allow the entry, so when someone types an incorrect entry it just erases it or stays on the cell and keeps it highlighted but doesn't let you click away...something like that. This would be applied to the whole workbook and is for XL07.
View 10 Replies View RelatedI have a Macro that opens numerous excel files.
What code do I require to cancel the startup message?
ie. The message that displays "Disable / Enable Macros" (security setting cannot be changed, so message is always displayed).
Plus I then have some startup message boxes which I manually click OK to continue, but how do I cancel this messages using VBA Code?
Is there a way to turn off those annoying error message boxes that pop-up when a line of code is incorrect? I pretty frequently go to another area of code to grab some previous code to copy and paste, and the error message box pops-up, so I have to hit OK to close it. It would be nice if it still highlighted the problem red, but I'd even give that up for those boxes to go away.
View 5 Replies View RelatedI'm trying to create a more user friendly (and easily updated) gantt chart for marketing campaigns running throughout the year. The difficulty is that we have approximately 100 products and each product can have anything up to 20 campaigns per year (never overlap, though, fortunately).
Campaign data is on one row per product in columns B:CC (each campaign consists of start date, end date, name and volume) but these are hidden from the user. The conditionally formatted chart runs from CD2:QD108 and is predominantly based on the start and end dates.
When any coloured cell is selected I want the relevant campaign name to be displayed, like a data validation message.
I have a total box which should add up to 720 when other cells have data entered.
Is there a way that I could put up an error message if the total is not 720 in that cell when someone tries to save the worksheet?
Or maybe for eg, cell A1 does not add up to 720, if they then try and move on to cell A50, the message would appear then?
I'm guessing this is not (easily) possible, but I thought I might just ask in case: Can we change the messages in VBA run-time errors displayed by Excel. Better yet, I would like to avoid the Excel generated error box, and just have a text message written into a particular cell.
View 5 Replies View RelatedIs it possible to have a message box with a variable amount of messages in?
In the following code I want to create a message box where if the flag is true it displays a message in the box.
Because of this the box could have 1 line or 4 lines or anything inbetween. Is this Possibe?
Dim CompanyNameFlag As Boolean
Dim OnsiteContactFlag As Boolean
Dim ContactNumberFlag As Boolean
Dim AlternativeContactFlag As Boolean
I have set up a macro that opens a set of about 20 excel files, one at a time,and then copies select sheets, and then pastes into a different main spreadsheet. After each sheet is opened and copied/pasted, I close it out and open the next one. My problem is I keep having to click through messages while the macro is running. I encounter the following messages:
1. Do you want to save (file that I opened and copied from)?
2. The formula or sheet that you want to copy contains the name "asdf.." which already exists on the destination worksheet. Do you want to use this version of the name?
3. A box comes up that says update values from this file:
4. There is a large amount of information on the clipboard. Do you want to be able to post this information into the clipboard later?
I would rather ignore all of these message boxes, but my answers to them are as follows:
1. No i do not want to save
2. Yes I want to use that version of the name
3. I want to select cancel out of this
4. No i do not want to be able to post that information.
Is there any way I can automate this?
In wrote a workbook in Excel 2003 that is causing me a problem I can not seem to figure out. The workbook consists of multiple worksheets that work using both VBA code or cell formulas. The worksheets are protected (though w/o a password) to prevent users from accidently altering a cell formula. When the code needs to write to a cell, it first unprotects the sheet, writes to the cell(s), and then reprotects the sheet.
Everything seems to work fine, often for several months. Then somehow something goes wrong. Specifically, after double-clicking on the ListBox in the worksheet “CRI Calculator” to pick a drug name, it will improperly present a dialog box (see attached screen capture 'Unprotect Sheet DialogBox.JPG') prompting the user for a password to unprotect the worksheet. Again, no password has ever been set; I have tried entering nothing, “password”, and "anonymous". Regardless of how you respond, an error code “Run-time error ‘1004’: The password you supplied is not correct…” occurs. If I press Debug, it takes me (ironically) to the following line of code
I would like to prompt the user of my Excel Macro, with a particular message which would appear while the code is running in the background, and then dissapear once the code if finished executing.
For instance:
I prompt the user "Do you want to run a new Query", Yes, No, or Cancel.
If they select yes, I would like a message appear that would advise them that "this may take some time, please be patient" and possibly give them a button that would allow them to cancel the process, all the while this process is running. Once the query is complete, the message would dissapear, or if they click on the cance button.
I have added a toolbar to an excel application that the company uses. I need this toolbar only to be accessed to this application and not any time that you open excel. I have used the following
Private Sub Workbook_Open()
Application.CommandBars("Rithin-Toolbar2006").Enabled = True
Application.CommandBars("Rithin-Toolbar2006").Visible = True
go_cover
End Sub
Private Sub Workbook_close()
Application.CommandBars("Rithin-Toolbar2006").Delete
Application.CommandBars("Rithin-Toolbar2006").Enabled = False
Application.CommandBars("Rithin-Toolbar2006").Visible = False
End Sub
This works but only If I accept yes when it prompts me to save changes to the workbook. Each time I open the workbook and close it, it prompts me to save changes to the workbook. I dont want to force a save on the workbook, meaning have code that will save the workbook before closing. I want it such that if the user opens the workbook, and makes no changes, he/she should just close it with no questions asked.
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?