Get Message That Says That Action (presumably Save) Will Interrupt Refresh?
Jan 11, 2012
I have the following code that works fine (although i'm sure you'll think it's a little clumsy), but when I add a save workbook line at the end I get a message that says that an action (presumably the save) will interupt a refresh. My problem is that I cant understand why there is a refresh when the code ends. The data that is being refreshing is big and a number of calculations take place, so that may be something to do with it.
Sub RUN()
Application.DisplayAlerts = False
Application.DisplayStatusBar = True[code].....
View 4 Replies
ADVERTISEMENT
Aug 13, 2009
If I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.
Can this code be modified so that the message box does the following...
1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.
2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.
I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.
3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this).
View 4 Replies
View Related
Aug 23, 2013
I am trying to develop a system to allow me select different parts from multiple sheets and then add them into a bill of material on a separate sheet.
[URL] ...........
I have written some VB script the best I can and it includes a 'commandbutton' to make it easy for the user of the workbook to use.
This is how it works:
Go to 'Step 1 - Manifold 8640' tab and highlight one of the yellow cells.Once you have selected a yellow cell, you then click the 'select' button above.The value (along with other values) are copied to the 'Smart Calc' sheet.Then you go to 'Step 2 - Gland Plate' tab, select a yellow cell, click the 'Select' button.Notice how the 'Step 2 - Gland Plate' value is entered into a different group in the 'Smart Calc' sheet - this is great................BUT
The issue I am having is:
If the user goes to 'Step 1 - Manifold 8640' tab, highlights the desired yellow cell then presses the 'Select' button twice, it will add two lines of data in the relevant 'Step 1 - Manifold 8640' section....This is ok.But when the user clicks a third time, the 'Step 1' data will overflow into the 'Step 2' section of the 'Smart Calc' tab...because the script tells the data to look for the next available line to write to. This is what I am trying to avoid!
Instead, i would like a message box to popup to indicate to the user that they cannot add anymore data into into the 'Step 1' or 'Step 2' sections of the 'Smart Calc' tab until they clear the data from within the relevant section in 'Smat Calc'.
I would like to set limits on where each 'Step 1' or 'Step 2' data is written to the 'Smart Calc' sheet (so it is within its relevant section). As you can see the script basically looks for the next available cell but this isn't really good when the data 'overflows'.
Note that you are able to highlight the yellow cells in the 'Smart Calc' tab and press the delete key, it will delete the data to clear the line. That is how i designed it to work so if the user makes a mistake they are able to just delete the relevant line and enter new data.
View 1 Replies
View Related
Jul 12, 2014
I have some excel files which should be saved in Excel 97-2003 Workbook format . I am trying to write vba code for that i am struggling at starting . problem is i have 6 excel files which should be saved as Excel 97-2003 Workbook format i don't know how i give 6 files path to code .
View 2 Replies
View Related
Feb 18, 2009
I have Excel XP and som spreadsheets that have external...I want the data is refreshed at the opening and this option Prompt the user Window to refresh data on opening or not.
How or where should I chage this in order this initial message is not showed but the book refresh the querys when opening.
View 9 Replies
View Related
Sep 24, 2007
I have a model which extracts data from an external data sournce using an ODBC query. This data is then used to generate a pivot table. I have incorporated code to update the query and the pivot table on startup but would like to disable the Enable Query Refresh message box which occurs at startup (it would also be nice to disable the security warning but I know that is a no no)
View 2 Replies
View Related
Dec 15, 2007
I've used the code below and it's worked to stop Update Links message, but i still need one tip to avoid continue message that shows up after Update Links massage:
Private Sub Workbook_Open()
Application.AskToUpdateLinks = False
Sheets("Feb").Activate
Range("A200:I400").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
End Sub
View 2 Replies
View Related
Mar 25, 2014
I am using Task scheduler to open a workbook which has some connections to an ODBC that are set to refresh on opening.
I need to automate:-
One of the sheets needs to recalculate for the sort by ascending code to run on the sheet.
Save and then close after say 2 minutes of opening (to give the ODBC connections enough time to finish).
View 2 Replies
View Related
Mar 13, 2007
This is still the continuing saga of the Toastmasters countdown speech timer. I got past the timer loop issue but now I need a way to detect (preferrably) the spacebar being pressed during the timer loop. I want that keypress (or any other key if spacebar isn't do-able) to end the timer loop and run the WriteLog routine.
I am coming to the conclusion that Onkey is not what I want.
View 9 Replies
View Related
Feb 10, 2014
Below is a code that works for this attachment Invoice.xlsm When you hit the code you will see how the 2 left most columns disrupt the conditional formatting resulting in one color for those columns as opposed to every second row with one color and the rows between them another color. modify this code so that the conditional formatting remains the same? (I highlighted the part in the code that needs attention).
[Code] ......
View 4 Replies
View Related
Apr 28, 2014
I have a spreadsheet saved. Its a report with formulas in. I have saved this as a master copy and when opening you can only open in read only or enter a password to open to edit. The report is produced monthly, so each moth the master is opened and the user has to save the ss with a new file name.
I want to put VBA code so that when a they open the ss in read only. a popup box appears asking to them to "Save the Report Now" with a save as button. This would then open the Save As window
View 2 Replies
View Related
Feb 26, 2008
I have a program that screen scrapes data fom a mainframe emulation program. There are many loops used in order to slow the program down checking for things to load before taking the information. I would like to build some kind of display showing the user what the program is doing and allow them to stop the program if they like. Right now while the program is running everything is frozen and unresponsive.
View 3 Replies
View Related
Oct 17, 2006
I have an extensive application which I wrote using Excel 2002 (XP - Version 10). However it will likely be used on anything ranging from 2003 - 1997. When I tested it using Excel 97 it gave me the traditional [annoying] message asking if I was sure I wanted to save it under an earlier version than which it was created.
I want to get rid of this message (a lot of my users aren't the most computer literate and the more annoying messages I can avoid the better!)
I tried using:
Application.DisplayAlerts = False
but it doesn't affect this particular message. I need to know how to keep this message from popping up. Please Help.
Under 97 (NOT on my 2002) it also gives a "Do you want to save the changes" message upon closing even though the "DisplayAlerts" is set to false. I'm not sure why this is still coming up.
View 9 Replies
View Related
Dec 13, 2007
Application starts with a button on an otherwise empty spreadsheet. Then opens a user form. After I close using a button on the form. My close routine closes the worksheets and workbooks. I then quit the application and I get a message asking if I want save the original spreadsheet.
View 6 Replies
View Related
Nov 4, 2012
I am using this code to display a message when a range is empty on workbook save. Case "A" gives an error when referring to a range, the second case works fine. How can I change it?
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Select Case Worksheets("Sheet1").Range("B6").ValueCase "A" If Worksheets("Sheet2").Range("D104:D109").Value = "" Then MsgBox "Cells cannot be empty!" Exit SubCase "B", "D", "F" If Worksheets("Sheet2").Range("D112").Value = "" Then MsgBox "Cells cannot be empty!"Case ""End SelectEnd Sub
View 4 Replies
View Related
Oct 3, 2013
I have several workbooks that use macros from my personal macro library (MyExcelAddIns.xlam).
Some of these, especially new ones, get an error message every time I save them: "Privacy warning: This document contains macros, ActiveX controls, ... These may contain information that cannot be removed by the Document Inspector."
Then after I save it once or twice, the message goes away. Why am I getting this message? Why only sometimes? What can I do to get rid of it entirely?
View 3 Replies
View Related
Jan 4, 2008
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
View 9 Replies
View Related
Jul 27, 2006
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
View 4 Replies
View Related
Jan 13, 2009
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?
View 2 Replies
View Related
Nov 27, 2007
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies
View Related
Apr 27, 2009
How to set a password before executing any action.
For example, I have "cancel" button in my userform. If i click "cancel" it unloads the form. Instead of that i need a password prompt. so when i click the "cancel" button ,it should ask for a password. only if i provide the correct password then only the form should be unloaded.
View 6 Replies
View Related
May 28, 2014
JSON import to Excel - Stack Overflow
I understand most of what this post is saying, but I don't understand how to put it together in an excel module or class module.
View 5 Replies
View Related
Jul 18, 2006
I have a database in excel which I want only selected users to have edit rights & others should have readonly rights. I have written the following code where a pop up would appear in selected cells warning user for editing the said cell. What I want is when a user clicks yes he should be able to edit it & when he clicks no the cell should get protected.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rTriggerCell As Range
If Not Intersect(Target, Range("D1:D100")) Is Nothing Then
Set rTriggerCell = Target
Application.EnableEvents = True
If MsgBox("Edit Cell?", vbYesNo)
End If
On Error Goto 0
Exit Sub
End If
View 6 Replies
View Related
Oct 21, 2008
How do you set the code so that, if a condition is met, then several cells are filled with values?
So far, my code is
HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If
View 11 Replies
View Related
Nov 19, 2008
I am trying to loop through an action within a macro and require some assistance.
I have the following
View 2 Replies
View Related
May 30, 2009
I have a spreadsheet that when I am running an specific macro and userforms, this one sheet is visible, when the macro is not running, then, the sheet is hidden.
Now, that happens automatically the way that I set up my macro; however, if the macro is aborted, the sheet doesn't hide.
View 7 Replies
View Related
Feb 1, 2012
I have created vba which I want to comment out. I totally stumped how to create this action on vba. My current vba code which I want to comment out using vba
Code:
Option Explicit
Sub YearlyInput()
End Sub
'Speeding Up VBA Code
With Application
[Code] .......
View 8 Replies
View Related
Aug 24, 2012
I have a for which consists of
2textbox
1 listbox
1 textbox.
While entering the data, the cursor does not stop in the list box and goes directly goes to the next textbox. Autotab is set to true.
View 4 Replies
View Related
Nov 23, 2007
I have a macro that is working fine. It calculates some values based on a SQL database and then copy the values and paste them into another spreadsheet.
However, I need to repeat the same procedure 1296 times.
Please see below the macro.
Sheets("QUERIES").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("D5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("C5:G5").Select
Selection.Copy
Sheets("AWB RANGE").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I need to increment the line #3 (ActiveCell.FormulaR1C1 = "2") that should start with 2 and end with 1296.
Also, I need to increment the line #11 (Range("K2").Select) that should start with K2 until K1296.
The other parts of the macro will not change.
Would you be able please to give me a hand on this explaining how to create a counter to start on 2 and stop with 1296 ?
View 9 Replies
View Related
Feb 25, 2008
Last friday my personal.xls file went missing. I cannot find it anywhere which means I am missing about 10 key macros. While it is not the upset of the century, my problem is that now when I go into Record Macro and try to "Store New Macro in:" -> "Personal Macro Workbook" and then click OK, Excel brings up a msg box that says:
Personal Macro Workbook in the startup folder must stay open for recording.
Click OK and Excel says:
Unable to Record
View 9 Replies
View Related
