Change Manual Execution To Automatic Execution

Jun 1, 2007

I have this codes which will only trigger if I manually execute it. What do I need to do to trigger it automatically whenever the worksheet change.

Below is the codes:

Sub Risk_Color()

Dim c As Range, myFontCol As Integer, myCol As Integer

For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34
Case Is = 4, 5, 10, 20: myCol = 43
Case Is = 30, 40, 50: myCol = 6
Case Is = 70, 100, 140, 150
myCol = 5
myFontCol = 2

View 9 Replies


VB Code Execution On Worksheet Change

Sep 16, 2009

What is the key word sequence to cause VB code to execute on a worksheet change?

View 9 Replies View Related

Code Execution Has Been Interrupted (application Setting Change?)

Mar 11, 2009

I wrote a very simple macro in an Excel file a few months ago. Worked great. Worked on my computer, and worked on a dozen other people's computers perfectly. Now, months later, ONE person is getting the error message 'Code execution has been interrupted' every time she runs the macro. I've tested it on my computer with Excel 2007, and it worked. Tested it on another computer with Excel 2003 (offending computer has Excel 2003), and it worked again. So I tested a third computer on Excel 2003, and it worked again. I'm 100% positive the macro code isn't causing this error. This makes me think that it's related to an application setting that was changed somehow.

View 5 Replies View Related

Hide Message Box After Execution

Nov 14, 2012

I have an inputbox that asks a question. If the user enters an incorrect response, a messagebox appears telling them to enter the correct word and returns to the inputbox. The code works fine, however the messagebox doesn't disappear after they click OK. Is there a way hide or get rid of the messagebox once they click OK or Cancel? My code is below:

VorD = Application.InputBox("Is this for Voice or Data?", "Voice or Data", Type:=2)
Application.DisplayAlerts = False
Select Case VorD


View 2 Replies View Related

Asynchronous Execution Of Macro Row By Row

Mar 7, 2013

I am trying to fix a problem that I am having with Bloomberg add-in for Excel. Each day I populate 31 days of historical price data for 500+ stocks.

$AL$3 = "=BDH(AK3,"PX_LAST",$D$1,$C$1,"DTS=h","dir=h")"
$C$1 = "=WORKDAY(TODAY(),0)"
$D$1 = "=BAddPeriods(C1,"NumberOfPeriods=-31","Per=wd","CDR=#A")"

What is supposed to happen is that in $AL$3:$BQ$3 there will be prices for 31 days.

I leave the workbook open as the Bloomberg DDE session remains open for 3 days even if the Bloomberg executable is not running.

Sometimes there is no problem and when I come into work the following day, the data is shifted forward by one day. Other times, much of the data is missing and I am forced to select and refresh each $AL$_ field. The problem with this is that for some reason the refresh is happening synchronously and I think this is causing havoc on the DDE connection and/or Excel. Resulting in slow/no updates. Also, if I refresh the worksheet some formulas will be overwritten with static data!

What I would like to do is create a macro that will:

1) clear all data in $AL$_:$BQ_$ & $BS$_:$CX_$
2) set the value of $AL$_ = "=BDH(AK$_,"PX_LAST",$D$1,$C$1,"DTS=h","dir=h")"
3) wait until the data is populated into $AL$_:$BQ$_ before going to $AL$(+1):$BQ$(+1); if $AM$_ has a value then most likely the data has populated in that row.
4) then when $AL$_:$BQ$_ is finished, do the same for $BS$_:$CX$_

I have been waiting for Bloomberg support to fulfill this request for over one month now with no results.

I have a very minute amount of code below:

PHP Code:

Sub PopulateData() PopulateData Macro Range("AL2:BQ2").Select    
Selection.ClearContentsRange("AL2").SelectActiveCell.FormulaR1C1 = _ "=BDH(RC[-1],
""PX_LAST"",R1C4,R1C3,""DTS=h"",""dir=h"")"Range("AL2").SelectEnd Sub 

View 9 Replies View Related

Slow Code Execution

May 9, 2007

I have the following piece of
Sub Summarise_Variations()

Dim myRange As Range
Dim Write_Cell As Long

With Application

.Calculation = xlCalculationManual
.StatusBar = "Please wait - Summarising variations..."

End With

It happily goes through a list and sums multiple records. It might not be the most efficient or best way to do it but it works and when I run it on a couple of stand alone sheets it takes a couple of seconds to process if that.

Unfortunately when I run it as part of the overall application that I've developed it takes ages to run, i.e. more like ten minutes.

The spreadsheet has a few graphs and about 250 sumproduct and array formula live in it but all other formula on other sheets are created and then paste valued as part of other VBA routines. As you can see I've also turned calculation off as the procedure runs so don't understand why it is suddenly taking so long.

View 9 Replies View Related

Pause Within A Loop Execution

Jun 10, 2007

I want to establish a loop in VB and within the loop I would like to pause execution of the loop and wait for input from a cell then resume execution of the loop once input has been entered.

View 9 Replies View Related

Form Freezes At Execution

Oct 8, 2007

I have a multipage form that I'm using to create a wizard that accepts data from the active workbook via a refedit box, however, when I try to execute the entire form freezes at runtime. The editor will bring up the workbook but the entire workbook freezes.

I cannot click any of the buttons on the form, exit the form, input the range into the refedit box, or even click on any cells in the worksheet. Everything was working perfectly fine until I started adding command buttons to other pages of the multipage form, but no code has been added.

I don't even have code to link the pages together, all I did was add command buttons and change the names on them. I'm having a hard time believing this is what's causing excel to freeze up, but I haven't done anything else. I've checked my loops and they are fine.

I've also gone back to another copy and deleted everything on the other pages and left only page 1, but this is still failing.

View 3 Replies View Related

Macro Execution Stops

Mar 12, 2009

I have a problem with the following code

Dim strProcLine As String
With ActiveWorkbook.VBProject.VBComponents(ActiveChart.CodeName).CodeModule
' MsgBox ok

' adaugat procedura goala
.CreateEventProc "Calculate", "Chart"
'MsgBox ok
strProcLine = "Format_Chart"
' MsgBox ok
.InsertLines .ProcBodyLine("Chart_Calculate", 0) + 1, strProcLine
' MsgBox ok
End With
' MsgBox ok

This is part of a larger macro, wich makes 2 pivottables and for each PT a chart, and for each chart i create an even procedure (Chart_Activate) wich calls a procedure to format the chart. Tha macro is alocated to the click even on a button in the sheet where i get the information from.

If i run the macro(click the button) with Microsoft Visual Basic Editor opened all goes ok. But if i close Microsoft Visual Basic Editor and then run the macro it stops right before .CreateEventProc "Calculate", "Chart" of the first chart , and i can't understand why. No error mesages delivered, nothing.

View 9 Replies View Related

Slow Execution Before Interruption

Jun 21, 2006

I have a macro that opens various files (via a loop) and then goes to each worksheet (via another loop) and looks through each row of data (a third loop).

Each worksheet can have over a thousand rows of data. Once I hit the button for the macro to run, it runs without any error messages, but it seems to take forever.

When I hit escape twice to interrupt the macro and then press the 'play' button, it zips through and finished running the macro really quickly. The interruption point is generally somewhere within the third loop.

It's almost as though it falls asleep and I have to wake it up.

View 9 Replies View Related

Macro Hangs After Execution

Jul 11, 2006

I have written a macro .When i execute the macro the result is displayed but my excel file hangs after that.

View 3 Replies View Related

Pause Macro Execution Without Using Stop?

Jul 3, 2014

I have macros that often pause for the user to take some action -- for instance, to confirm that the correct cells are selected, or to delete something. Usually I use the "Stop" command, which opens the VBA editor; when I finish doing the manual action, I just resume the macro.

When writing macros for actual users, I'd like to do the same thing without their having to deal with the VBA window. Is there a way to pause and resume action and remain in the Excel window?

View 3 Replies View Related

Entry Based Execution Of Macro

Dec 19, 2008

I have to make eighty to ninety entries daily in Column B. Then transfer these entries to another column N,(by copy/paste) after that a maco "text to column" is executed. Now I have linked Column B to N like =B2, =B3, etc. etc. thus instead of copy/paste, the data when entered in Column B, is automatically going to Column N. I wish that the macro (text to entry) may be executed by each entry made in Column B. Is it possible with a macro?

View 7 Replies View Related

Time Limit On Code Execution

Jul 8, 2009

I have a function which will allow me to search for combination of numbers which will sum to zero. However, because the range could be as long as 300 rows or even the possibility of not finding the combination that will sum to zero, the macro will take ages to complete or even causes the whole excel to hang. So What i want is to instruct macro to stop doing the search if it cannot find the results after say 20 minutes of searching.

Is that possible?

View 14 Replies View Related

Assigning Shortcut Key For Macro Execution

Dec 18, 2012

I have a macro where I am assigning a variable and the value of the variable is a path where the data is stored. i am trying to assign a shortcut key for macro, i tried to run the macro by going to the vba mode and then click on the F5 function key and the macro run absolutely fine. but when i use the shortcut key the file just opens and just exits the macro.

Option ExplicitDim a, b, c As Long
Dim myFolder As String
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As Variant
Sub Macro1()
Application.DisplayAlerts = False

[Code] ....

View 4 Replies View Related

Error / Code Execution Has Been Interrupted

May 20, 2013

I have created two userforms which allows people to log-in to the worksheet.

The code works great on the first go, sometimes even twice, but after a while I get taken to the VBE and shown the message "Code execution has been interrupted". And highlights a certain part of my code (highlighted in red below).

I will copy all of the code relating to the userform below.

UserForm Code

Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ClearButton_Click()
TextUsername.Value = ""
TextPassword.Value = ""
End Sub


View 1 Replies View Related

Stop Macro Execution From UserForm

Jun 7, 2013

On a worksheet I have a command button that displays a ConfigureCatalog form.

Public StopRequested as Boolean

'Worksheet Command Button will display the Configure Catalog Form
Private Sub CmdConfigure_Click()
CatalogConfigForm.Show vbModeless
End Sub

The ConfigureCatalog userform has two Command Buttons: cmdRebuildCatalog, and cmdStop. RebuildDSWCatalog is a macro that reads and analyzes just over 35,000 records and updates other worksheets. As it runs, it repaints the CatalogConfigForm with a progress message every 100 records. It can run for about 3 minutes. I want to provide a "STOP" button that will allow the user to interrupt the RebuildDSWCatalog macro and return control to the user.

Private Sub cmdRebuildCatalog_Click()
StopRequested = False
Call RebuildDSWCatalog
End Sub

Private Sub cmdStop_Click()
StopRequested = True
End Sub

While processing the 35,000 records, I want the RebuildDSWCatalog macro to check whether StopRequested has been set to TRUE prior to processing the record. If StopRequested = TRUE, I want to drop out of the macro.

Public Sub RebuildDSWCatalog()
StopRequested = FALSE
For DSWRecord = DSWStart To DSWCount
If StopRequested = True Then

[Code] ....

Problem is that once the RebuildDSWCatalog macro starts running, the STOP command button never seems to get the focus back. All other input is inhibited until the macro ends.

It just ignores that I'm hitting that STOP button. In fact, the button doesn't visibly respond at all until RebuildDSWCatalog finishes processing all 35,000 records.

View 4 Replies View Related

Error Is Encountered During The Execution Of A Macro

Jan 18, 2007

What code do I use so that if an error is encountered during the execution of a macro, the macro ends, rather than an error message popping up?

View 9 Replies View Related

Code Execution Has Been Interrupted At Random

Jan 5, 2004

There is definite problem, as the "Code execution has been interrupted" error message appears seemingly at random (although if the workbook doesnt change it is the same points), and for no reason.

If I click the continue button the macro runs exactly as planned, but still there seems to be no rhyme or reason as to why this happens!

It even invokes on the workboon_open function.

View 9 Replies View Related

Web Queries Don't Refresh During Code Execution

Sep 9, 2006

I am writing a routine to retrieve multiple stock quotes by looping through a bunch of sheets and refreshing a bunch of querytables that access the web. My data source (Livecharts) is often clogged up and queries will fail or get lost in space so after initiating the queries I wait for a while using a waitable and then check to see if each query has completed or is still refreshing. If they are still refreshing I cancel them and refresh again. Or at least that was the plan. What I have discovered is that as long as the vba code is running the results from the queries do not come back into excel. As soon as the code is exited or I go into break mode in the debugger the queries complete.

Is there some way to get the queries to refresh during code execution? Here is a code snippet that shows what I am trying. I have defined a Class Module defining an "EventfulQTable" and it's associated methods and properties so I can have a QueryTable with events. The sub Wait uses the windows API SetWaiTable command to pause for a specified period of time. It includes a DoEvents command which I thought would allow the returning queries back into Excel but no such luck.

Sub Refresh_Queries()
Dim SheetNum As Integer, ListSheet As Integer
Dim eqtQT As New EventfulQTable
Dim QueriesDone As Boolean

SheetNum = 1
ListSheet = 12
QueriesDone = False

On Error Goto QueryError

View 4 Replies View Related

Code Execution Has Been Interrupted On Running Macro

Jun 9, 2009

Now..when I run any of my macros, I get the following message.. "Code Execution has been interrupted".

I'm not sure why I'm getting this message but it happens everytime I run ANY macro. Note that if I hit "Continue" every time it gives me the option, I am able to successfully run the macro, but obviously, I shouldn't have to do this.

View 5 Replies View Related

Eliminate Display Alerts During Macro Execution

Jul 15, 2009

I just finished a great macro and right at the end of deleting a sheet, I got a message box saying

"Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete"

How can I eliminate this from popping up in the middle of code?

View 2 Replies View Related

Writing The VBA Code For The Objects Created During Execution.

Apr 27, 2006

See the attached Workbook, which explains the problem easier.

View 9 Replies View Related

Pause Execution Of Command Lines For Set Times

Sep 12, 2006

Is there anyway to lap the execution of the 3 lines in the below code (sample) say by 10 seconds each ?

Sub Macro1()
ActiveCell.FormulaR1C1 = "AAAAAA"
ActiveCell.FormulaR1C1 = "BBBBBB"
ActiveCell.FormulaR1C1 = "CCCCCC"

End Sub

View 3 Replies View Related

Sheets Copy Method Fails On Second Execution

Dec 26, 2006

When I call the following code from my Visual Basic 6 program to an excel spreadsheet, it runs great the first time, but then fails everytime after that unless I close vb6 and reopen it.

Private Sub lblGroundScheduleExcel_Click()

'return day of week
'MYHEADERDAY = Format(Now, "dddd")

'get day
''MyDay = Format(Now, "dd")
MyDay = Format(DTPicker1.Value, "dd")
'get month
''MyMonth = Format(Now, "mm")
MyMonth = Format(DTPicker1.Value, "mm")
'get year
''MyYear = Format(Now, "yyyy")
MyYear = Format(DTPicker1.Value, "yyyy")

View 3 Replies View Related

Progress Indicator Userform Position Jumps During Execution?

Aug 4, 2014

I have a macro that calls 3 other procedures during its run. I have the positon set in the UpdateIndicator sub

With ProgressIndicator
.Top = Application.Top + 300
.Left = Application.Left + 400
end with

and the Userform StartUpPosition property set to 0 - Manual (though I did experiment with the other settings as well.

The issue is that when the series of subs are running, the Indicator jumps and shifts (by 10 or so pixels down and right) and sometimes blinks off completely as each new sub is being called and subsequently returns to the initial macro.

It is accurate and does what it's supposed to do, I just find it annoying and that it probably looks a bit unprofessional (and unstable) to the eyes of some coworkers who use the macro as well.

View 2 Replies View Related

Excel Changing Format From General To Text During Macro Execution?

Jul 25, 2013

I have a procedure that allows me to view and make changes to data in a table. I list the current values for the item in one column and use simple formula to copy that value to another column where if there are changes that need to be made, the formula is simply overwritten. The Macro is then selected using a command button and the formulas are all overwritten using copy/paste values to keep from writing out the formula to the data table. These values are then all written back to the data table, current values are overwritten with whatever is in the update column, new data or old data.

I have one cell out of 48 that has decided to march to the tune of a different drummer. The format changes from General to Text and the formula written from the macro is what shows up in the cell instead of the value of the formula. Never a big disciplinarian, I have to wonder if I have been too lenient on the cell and this defiance is the price I have to pay.

The sheet is protected only allowing entry into the cells available for update.

Here is the bit of code that affects this cell (starting from a format of General:

Sheets("Product Data").Cells(ItemRow, 3).Value = Sheets("Update").Cells(6, 8)
Sheets("Update").Cells(6, 8).FormulaR1C1 = "=(RC[-6])"

I just don't see anything that would change the format, and these are the only two lines that even reference cells(6,8).

I tried to set the format for the cell from within the Macro, but with the sheet being protected, it just dumps me out to my error message.

View 1 Replies View Related

Runtime Error 1004 When Opening Excel From Access Upon Second Execution

Jul 16, 2014

The following script runs fine the first time I click the command button on an Access form but after I close the Excel workbook and click the command button again to edit a new spreadsheet, I get the follow error message:

Run-time error '1004': Method "Columns' of object'_Global failed.

It fails here:


The script is being run from Access 2007 and is opening a spreadsheet in Excel 2007 (but using xls extension)

Private Sub IdahotoExcel_Click()
Dim dlg As FileDialog
Dim idahofile As String


View 5 Replies View Related

Creating A Timer To Measure Macro Code Execution Times

Sep 7, 2006

I am looking for a way to measure how long it takes to complete a sub routine in VBA. I have code which posts data to matlab and then calls it back. What I need to know is how long does it take to complete each subroutine.

create a code which will measure this? I need to measure the time taken in 100ths of a second.

Is there any simple code to complete this? I am pasting a sample of my code which is using Matlab as a COM server.

Dim Matlab As Object
Dim MReal(10, 0) As Double
Dim i As Integer
Dim j As Integer
Dim MImag() As Double
Dim value As Double
Dim RealValue As Double

I am sure I woould need to declare the timer but I do not know where and I am not sure of the syntax.

View 9 Replies View Related

Show Progress Indicator If Macro Execution Take Some Considerable Time

Jun 7, 2007

is it possible to show progress indicator if macro execution take some considerable time say more than 10 seconds?

View 2 Replies View Related

Copyrights 2005-15, All rights reserved