Application.DisplayAlerts Not Working
Jan 14, 2010I am trying to suppress the message box displayed while executing the macro.
View 2 Replies
ADVERTISEMENT
Application.DisplayAlerts Freezes Application
Jul 14, 2006Im copying and pasting data from one workbook to another but when I want to close the source workbook, it comes up with this message that I have much data and if I want to keep this in a clipboard. I thought I could disable this with Application.DisplayAlerts = False but when I do this, Excel freezes. Im I doing something wrong. How can I supress this window?
Public path As String
Sub Get_data()
path = "\Nlchoosa.nlOPS_Processes$OPS_ProcessesReports Sector performance"
Workbooks.Open Filename:=path & "ReportsSector Performance Reporting week.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Windows("Sector Performance report Week.xls").Activate
ActiveSheet.Paste
Application.DisplayClipboardWindow = False
Windows("Sector Performance Reporting week.xls").Activate
Application.DisplayAlerts = False
End Sub
Application.displayalerts = False Does Not Work
Dec 28, 2008i use this to refresh webdata and copy cells:
Sub refreshAllQueries()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error Resume Next
Dim qryTable As ClsModQt
For Each qryTable In QueryTableListeners
If Not qryTable.qtQueryTable.Refreshing Then
qryTable.forceRefresh
DoEvents
Dim a As Long
i thought the 'application.displayalerts = false' would stop the pop up box that says something like ' this webquery returned no data,blah, blah' but it does not. trouble is it freezes the sheet until i click ok, but i want to be able to leave unattended. i have posted this before, but not with this code.
Application.SendKeys Stopped Working
Aug 17, 2007I have a simple macro that from Outlook Express sends emails, with the last code line to mimic ALT+S on the keyboard to send the message
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Yesterday I needed to install some new software that required at the end reinstalling of Microsoft Redistributable 2005. Not sure if for that reason but now the last line (the SendKeys) is not sending the email automatically anymore. What happens is:
-the email message windows pops up but is not being sent unless i do it manually (if i get rid of the two seconds pause ahead of that)
-have a VB error message that takes me before the SendKeys line (if I keep the two secs wait ahead of that)
It looks to me that either:
-the pc is not active anymore on the email message therefore the SendKeys doesnt work
-somehow the instruction finds the "door" shut by something else
-worse of all, the software has messed up with those send keys commands (tried to reinstall it but to no avail)
Excel For Mac, Application.FileSearch Not Working
Nov 3, 2009This relates to Excel for Mac 2004
I'm trying to use Application.FileSearch in a procedure to open files in a folder one by one and then do some stuff. It works fine when I developed it on my Windows machine but won't work on the Mac platform. The culprit instruction seems to be the
Application.FileSearch instruction, and I've also tried what I think may be a Mac equivalent Application.FileFind, but I get the 'Object doesn't support this action' error.
So I wrote a simple procedure below just to test the .FileSearch instruction but this fails in the same way.
Any ideas anyone? I've commented out lines which relate to Windows syntax.
Change To An Existing And Working Application
Nov 4, 2007I am trying to make a change to an existing and working application. Right now, I read in a list of clients from my spreadsheet to my Userform. When the user (that's me as well) clicks on a name, (or names) the software takes over and does stuff with their biweekly payments. What I want to do is this: Not only show the names, but also the biweekly payments next to the names. The reason for this is some clients send in more than the assumed biweekly payment. As a first start, I changed my Initial function to read in two columns from my spreadsheet and not just one. The range name listbox_rwosource is d100:e112.
This is the only change I made. However, when I run the program - I still only see the names?
Public Sub InitMyUF()
Dim I As Integer
'UserForm1.ListBox1.RowSource = "current_clients"
Application.ScreenUpdating = False Not Working
Jan 22, 2009For some reason Application.ScreenUpdating = False has stopped working for me.
For testing purposes, I have freshly opened Excel (2003 SP3) and into the brand new workbook, added the following macro:
*****************
Sub Test()
Application.ScreenUpdating = False
For i = 1 To 40
ActiveCell = i
ActiveCell.Offset(1, 0).Select
Next i
End Sub
*****************
When I run this macro, I see the individual values being written on the screen.
Stepping through the code and using Watch, I can see that Screenupdating is set to False by the first line, but as soon as the Activecell is changed, Screenupdating is reset to True.
If I turn events off by adding Application.EnableEvents = false to the start of the macro, all works as expected, however I do not want to use this "workaround" as I use event driven macros.
Application.ScreenUpdating = False Not Working??
Jul 18, 2006I've got a listbox which populates a second listbox from cells on a hidden worksheet which is done through macros. Every time I select a value in the listbox, the screen " flickers" to the hidden worksheet. I want to be able to do the whole process without that "flickering". I tried using Application.ScreenUpdating = False, but it still flickers. Have I overlooked something?
Sub GetStaffteamQuery()
Application.Calculation = xlCalculationManual
Dim sConnParams, sSQL As String
Dim dbFile, dbPath, dbName As String
ThisWorkbook.Activate
Sheet3.Select
dbFile = "CounsellorData.mdb"
dbName = "CounsellorData"
dbPath = Sheet3.Range("databaselocation").Value
selectedgroup = Sheet3.ListBox1.Value
If VBA.Right(dbPath, 1) <> "" Then
' if last char in dbPath has no slash.................
Application.filesearch Not Working In Excel 2007
Dec 20, 2009I wrote a macro to list all the excel files from a directory and its sub-directories to an excel sheet. It is working in excel 2003 but shows error (object does not support this action) in 2007. Actually I have copied almost this entire macro from excel 2007 help only. Can somebody modify this to use in both the versions of excel?
View 6 Replies View RelatedExcel 2010 :: Application Visible And Display Alerts Not Working
Nov 23, 2011I am trying to hide the excel 2010 application, open a csv file on a network drive, show the excel application.
The problem is a "Downloading" progress indicator appears, during the workbook open event I assume.
Code:
Sub Macro1 ()
Application.Visible = False
Application.DisplayAlerts = False
Workbooks.Open Filename:="S:LOGT5.CSV"
Application.Visible = True
Application.DisplayAlerts = True
I have seen the similar progress indicator during the "Save" event too. How to hide this?
DisplayAlerts
Jun 20, 2008I have this Excel form (actually worksheet designed as a form, selectable from our Intranet) which will be filled in by the first user and then approved by two more people downstream. I need to preserve the data throughout, including the approvals, so that the end form reaches the executing operative fully completed (initial data and both approvals).
I elected to Save the form using a SaveAs statement and change filename, so that the initial empty template remains empty for anyone else starting this process. That saving process issues a message about "filename already exists. Do you wish to replace...etc..etc." every time the form is e-mailed.
To eliminate the alerts, I bracketed the SaveAs with disabling and re-enabling the DisplayAlerts. Unfortunately, they resulted in a runtime 400 error. Eliminating the DisplayAlerts lines (bolded below) makes the routine run as intended, but it restores the need of responding to a message every time.
Is there something I could to to achieve no alerts and no runtime error?
The code of the e-mailing routine starts as follows:
Sub EMailer()
Dim OutApp As Object
Dim OutMail As Object
Dim OutMsg As String
Dim Copyto As String
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "My_POReq.xls"
Application.DisplayAlerts = True
Macros That Were Working In Excel 2003 Are Not Working In 2007
Jan 25, 2010I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
Application Run
Mar 14, 2007I want to make a macro where it will use the current filename of the workbook I have open (where is says New Quote Sheet 2.xls below) Is there something I can put where it will use the current filename when the filename is changed?
New Quote Sheet is a read-only template, and when a new quote is started, it is renamed.
Eg.
Application.Run "'New Quote Sheet2.xls'!Part8"
Sheets("8-Part").Select
Sheets("8-Part").Copy After:=Sheets(25)
Sheets("8-Pack").Select
Sheets("8-Pack").Copy After:=Sheets(26)
Working Day Function: (Day + Next 1st Working Day)
Aug 27, 2009I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
Application.Run Error
Aug 5, 2009I have the following
View 2 Replies View RelatedApplication.IgnoreRemoteRequests
Dec 11, 2006With the help of Professional Excel Developement by Bullen Bovey and Green, I am building a Dictator Application. All-in-all, it is coming very well, EXCEPT that I can't seem to get the IgnoreRemoteRequests setting to get written to the registry properly on ShutDown.
On Open, I change this setting to True. In the BeforeClose event, I reset it to False, along with all of the Settings that I hosed on Open. All of the other settings get properly saved on exit, however, the IgnoreRemoteRequests is still set to True the next time Excel gets re-opened.
VBA To Open Another Application
May 22, 2007understand the procedure for opening another application from Excel using VBA?
First check if open, if so, activate, if not, open and set to active?
Any help would be great! -even links to other posts or otherwise
Application.VLookup In VBA
Jun 4, 2008I've probably spent the last 4 hours reading posts from a bunch of different boards as well as this one, and I can't seem to pinpoint my problem. I'm trying to access data from a closed workbook via the following:
'Dim x As Variant
'x = Application.VLookup(Cells(1, 1).Value, Workbooks("C:Documents and SettingsmeMy DocumentsmyOptionsProgramsLiveUpdate.xls").Sheets("LIVE UPDATE").Range("$A$1:$C$5"), 3, False)
Cells(1, 2).Value = x
It doesn't seem to be working as I get the following error:
Run-time error -9
Subscript out of range
Open New Application
May 29, 2009I need a code to open a new Excel Application (Not just a workbook).
eg: say I already have book1.xls opened. Now I want a new excel application (say book2.xls) to be opened. Now, when I close any of the books (by using the cross on top right corner), the other shouldn't be closed.
Usage In Application
Jun 26, 2009done some VBScript spreadsheet reporting involving basically inserting data and some formatting.
I need some advice on what approach to take with Excel on my current project. The basic goal is to scrape project requirements off a word doc and load them into Quality Center (a test management tool from HP, henceforth referred to as QC). I have an hta/vbs process that offers the user choices gleaned from the QC API, scrapes the word doc, and creates a spreadsheet with a row for each requirement and a column for each property a "requirement" has in QC. These values are a combination of data from the word doc and values selected by the user in the preceeding hta interface.
The reason for this Excel step in the process is that all the values chosen up to this point are generic for the entire project. But there are a few properties that will be requirement specific. So all the info is collected in Excel and the user is to then go through each row and choose the "target cycle" (which I pulled off QC earlier in the process and display in Excel as a dropdown list). The "Platform Folder" and the "Module Folder".
Ok, so with that backround, here is my quandry... In the hta interface the user selected all the "Platform Folders" the project will have requirements in. These are then presented in a dropdown in the Excel, so the user can choose which of these Platform Folders this specific requirement should be loaded into. What I need to do is then have the "Module Folders" to be a dropdown that is dependant on the "Platform folder" chosen.
This is difficult enough for me, creating a dynamic dependant dropdown that will be replicated on each row. I don't know exactly how to do this but feel I can probably figure it out with all the help offered on these MVP sites.
What I can't figure out is where to do this. Should I set up the lists, formulas, and control code dynamically? Can these dependant list derive their values from comma delimited lists (like I did the non-dependent lists) or must they be in a worksheet? The only way I see to make the dependent dropdown work correctly is to have it run off a worksheet event. Can worksheet event code be applied dynamically or do I need to have a "template" type workbook containing the code in the application's folder and use that instead of creating a new workbook at exe time? I still have to write the upload into QC code, and that will probably be a macro that the user will have to install into their personal.xls. Can the dependent dropdown code be installed in the same way even though it needs to be event driven? Another aspect is getting the data for the dropdown. I can either get the lists earlier in the process when I already have a connection to QC and pass it along, or I can connect to QC from Excel and get the values dynamically. I am assuming passing them in will be more efficient.
Database Application
Feb 5, 2010I am very new with Excel and VBA, what i need to do is:
develop an initial log in to open an excel workbook...
and then open the workbook if username and password
1. ask for user name (field should be 50 characters)
2. Ask for a password (password alphanumeric with special characters field 25 characters)
3.Validate user to a salesman data base
if log in attempt fails after 3 attempts, purge buffer, clear log in screen and lock keyboard for 60 seconds)
I have done the whole excel application all i need is this thing to pop up when double click in my direct access to it..
Also save document with the invoice number that in in a cell
and How can I make a cell auto increase the invoice number every time open workbook?
Using Application Events With An Add-In
May 23, 2006I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).
I have created a class module with the following code (exactly as the Excel help):
Public WithEvents App As Application
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
SortE1Output 'This is my procedure that determines whether the
'spreadsheet is of the correct format and then reformats it
End Sub
"After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."
When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".
Hide A Application
Oct 20, 2006If you run a macro like this: Application.Visible = False. How do you get back to the application
View 3 Replies View RelatedMinimize Application
Feb 13, 2007i want to minimize the excel application and show a userform on startup.
View 2 Replies View RelatedStart A New Application
Mar 22, 2007I have an excel file which automatically loads an user form once the file is opened and when the user form is closed, the file also closes. When the file is open i am not able to switch to anyother excel file because this userform is active. Is there a way, when i open the file with userform, a New excel application starts. So that i can use the other excel files opened by the previous excel application.
View 4 Replies View RelatedVlookup From Another Application
May 4, 2007The problem is I need to do a vlookup from another Office application. I need VBAcode that:
opens up an excel spreadsheet,
performs the vlookup
and then retrieves the associated value.
Application.filesearch
Jun 27, 2007I have created a macro that must extract data from other workbook. It opens the workbook, with an exist, and isopened control, and then open it. Itīs running well, but i found while making another macro, that if the file direction is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", then i cant run the exist control. Next you have the code im using. In the first version, i had .LookIn = ThisWorkbook.Path. Now Iīve tried:
1. Dont put .LookIn, and fName is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls"
2. .LookIn = "http://www.xxx.com:8080/"
.FileName = "Challange%20Cost%20Proposal%20Sheet.xls"
Both are failing, it says, the file doesnt exist. If i try directly WorkBooks.Open "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", it opens correctly, but i would have a control, because the original file name could change, and the macro would crash if does.
Function OpenWorkBook(fName) As Integer
If Exists(fName) Then
If (IsOpen(fName) = False) Then
Workbooks.Open (fName)
OpenWorkBook = True
Else
MsgBox "The WorkBook " & originWorkBookName & " is already open."
OpenWorkBook = -1
End If
Else.........................
Mouse Over In Other Non VBA Application
Mar 15, 2008Is it possible to get mouseover information from outside of Excel using VBA? I'm trying to automate a non-microsoft package but I need my mouseclicks to wait for a message to appear within the other application before my macro continues. I've already asked something similar before but I've put a better title on this thread so apologies for the repeat.
View 2 Replies View RelatedControlling Another Application Where Hotkey Is Not Available?
Jan 29, 2014I am working on an Excel macro which uses the Shell function to open another application, the AppActivate statement to change focus to that application, and then a series of SendKeys statements to perform tasks for which keystrokes (hotkeys) are available. However, there is one step in the process which does not have a hotkey available, but requires clicking on a drop-down with the mouse. Is there a way to have an Excel macro "click" on something for which a hotkey is not available?
View 1 Replies View RelatedMacro To Quit Application
Jun 16, 2014I am trying to add the following to my macro to close excel when the macro is completed:
[Code] ......
This will save the workbook before exiting. How do I direct it to not save any open workbooks and exit the app?