Macro Stops Running The Macro Completely And Hangs In The Middle
Jul 3, 2007
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
View 9 Replies
ADVERTISEMENT
Dec 1, 2006
I am trying to create a menu that calls macros based on your choice using a drop down combobox (from the control toolbox). Everything works except: When i try to reset the drop down to "Main Menu" using the Linked cell $B$1 excel hangs and suts down evertime. What i would like it to do is reset the drop down to "Main Menu" each time one of the other drop downs are selected. I have really enjoyed this forum and it has been a great resource for me in the past I hope someone can help. I have attaching the file for review.
View 3 Replies
View Related
Jun 10, 2008
I want to make the worksheet protected, however, when I protect the worksheet the macro will not run.
View 14 Replies
View Related
Oct 20, 2006
Can't run vba macro. I have the following problem with a macro in excel. i wrote it, the macro runs very good from excel /tool/macro/macros/run, i also put a button on toolbar -it works too, i want to assign a keyboard shortcut from tool/macro/macros/options / shortcut and THE MACRO RUN INCOMPLETELY (stops running ).
Actually the macro opens all files in a specific folder, and delete their codes as follows:
Sub delete()
Const strNotFound As String = "There is NO Excel files..."
Dim ffTmp As FoundFiles
Dim wb As Workbook
Dim objVbc As Object
Dim objFile As Variant
Dim lngRet As Long
Application.DisplayAlerts = True
'Search Excel files
With Application.FileSearch
.NewSearch
.LookIn = "C:Corectii\_CS"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks...............
View 7 Replies
View Related
Oct 18, 2008
I have a Macro that changes the background colour cells dependant on the value in another cell, all seems ok but when it runs it scrolls the page as it shades the cells and then exits leaving the sheet where it scrolled to.
Is there a way to get it to return to where it started or not to scroll?
View 14 Replies
View Related
Jan 28, 2009
I have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems.
1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops.
2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice.
My specific question is about no. 2 and the following are the individual macros and the macro that runs them.
View 2 Replies
View Related
Dec 12, 2013
I am using Excel2010.
My sheet displays values collected from a database via an AddIn. The values are updated by pressing CTRL+G (AddIn function).
I have an OnTime macro running SendKeys "^G" to update the values every minute. When the values exceed a certain limit, a sound is played from a custom Alarm function.
This all works as long as the excel window is active.
But if excel is minimized or another window is opened, the values stop updating until Excel is active again.
When the values dont update the sound is not played and that defeats the purpose of the sheet.
View 6 Replies
View Related
May 24, 2013
I have an issue with the excel freeze in our organisation.
Our organisation works on Citrix and the office applications have been upgraded to 2010 very recently.We have both 2003 excel users and 2010 excel users.There is a user who has a problem working on excel 2010 the excel freezes.Excel 2010 works fine for some time and all at a sudden hangs,once its hung it freezes all the other applications.
The same file runs properly on the other 2010 systems.The Excel file which hangs are also not of huge in size.
I am unable to get to the rootcause of the issue why the excel freezes or hangs in the middle of the work getting to the root cause of the issue and an answer for the excel hanging.
View 3 Replies
View Related
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
Sep 15, 2006
I've managed to use some code I found to add a new row below the selected row, and duplicate all the forumlas of the source row. It worked fine dozens of times yesterday, but today it's decided not to work. It gets as far as creating the new row(s), but then just hangs & excel crashes before duplicating the formulas. I've even tried reverting to an earlier version, which also worked fine, but this crashes also!
Sub Add_New_Row()
' Unlock Worksheet
Worksheets("Sheet1").Unprotect Password:="*****"
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
Dim sht As Worksheet, shts() As String, i As Integer
Redim shts(1 To Worksheets.Application. ActiveWorkbook. _
Windows(1).SelectedSheets.Count)..................................
View 7 Replies
View Related
Oct 29, 2009
We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file.
Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally. If I delete the first file before running the macro, it then hangs on the second file instead.
Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached. THINGS I'VE TRIED:.......
View 2 Replies
View Related
Feb 25, 2009
The macro stops in the last row - it isn't able to select a cell in another sheet than "Menu" (I tested it different variations).
View 9 Replies
View Related
Apr 13, 2014
i ran some tests and it was working good only doing folders with ~100 files. i tried running the macro on all the files (~70,000), but I ran into problems with the computer going into standby while I was away. I tried running a batch of 3000 files and it completed, but only got up to around ~400 out of ~3000 records. Maybe, it stops cause of code related inefficiencies?
Here's the code:
macro_forexcelforum.txt
View 1 Replies
View Related
Dec 2, 2011
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
View 5 Replies
View Related
Dec 10, 2013
I have a macro stored a module at Personel workbook and i am using personel workbook as a hidden one. when i assign a shortcut below macro it is not working but whrn i am press F5 at VBA Editor it is working.
Here is the code:
Code:
Sub Transferdata()
On Error GoTo err1:
Application.DisplayAlerts = False
[Code]....
View 7 Replies
View Related
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
Apr 7, 2008
Dim varUserInput As Variant
varUserInput = InputBox("Enter 3 Digit Number:", _
"Project Number", "")
If varUserInput "" Then Exit Sub
View 9 Replies
View Related
Jun 9, 2014
I have this code which adds a blank row above the name "EV01_" where it appears. ...which works fine, but only works for half the rows.
[Code] ....
View 6 Replies
View Related
Jul 18, 2014
I usually just use macros to clean up Data from non-excel sources. So I wrote a macro to do this, but the process requires a date to be added, so attempted to do this via an input box. The input box works, but the code doesn't, clicking the button to which the macro is bound spits out an error 91 (object out of bounds?) when it hits the search function right after the inputbox code. Both bits work as intended separately, so I guess it's just some moronic formatting error on my behalf.
[Code] ......
View 6 Replies
View Related
Apr 11, 2013
Problem with the attached Range Overrun.xlsm.
Form opens with set number of rows. End User then enters figure into C3 of how many row are to be added.
VB:
Option Explicit
Dim c As Range
Dim j As Integer
[Code]....
Macro works correctly by adding the number of rows listed in C3. It SHOULD then "name " various cells in the original and new rows.
BUT the Macro goes ON to name cells in rows BELOW the ones that are added.
So on the attached worksheet the original rows were 6 - 9. Rows 10 - 12 were added, but the macro names the cells from Rows 6 - 14.
View 2 Replies
View Related
Feb 6, 2008
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies
View Related
Sep 28, 2011
I've had a macro running for ages, and just found it bugs out when there are spaces in a sheet name.
The user selects a destination cell as part of a form, which passes the variable to the following steps:
Code:
looperx = 0
Do
looperx = looperx + 1
Loop Until Mid(celldestinationstring, looperx, 1) = "!"
Sheets(Left(celldestinationstring, looperx - 1)).Select
The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9
This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.
View 8 Replies
View Related
Oct 2, 2008
Here is the find / replace vba I am using.
Sub Replace()
Cells.Replace What:=Chr(19), Replacement:="-", LookAt:=xlPart
Cells.Replace What:=Chr(24), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(145), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(146), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub
It makes the replacements up to line 37, then stops. If you then delete the first 37 rows and re-run the macro, it again performs replacements, but only on the next 37 rows.
I would like the macro to do the whole sheet...
View 9 Replies
View Related
Jul 10, 2006
I am wirking on a macro which opens a 'sourcefile' and then do some filtering and vlookuping to distribute the data to several files. When i run the macro from the VBA editor, everything runs fine. But if i run it from .xls file, the macro stops after opening the 'sourcefile'
Workbooks.Open Filename:=sourcefile
For i = 0 To 13
'do something...
Next
View 9 Replies
View Related
May 16, 2007
In the last week the following code has ceased to work, and I do not know why:
If TradeShow = True Then
ReportFile = " ANALYSIS PRINT (CS+T) MASTER.xls"
Else
ReportFile = "ANALYSIS PRINT (CS ONLY) MASTER.xls"
End If
Workbooks.Open Filename:=ReportFile, ReadOnly:=True
Worksheets("TRANSFER DATA").Select
PriceFile = "PRICE COMPARISON.xls"
The Workbooks.Open command executes, and the screen shows the cells of the new spreadsheet. Nothing else happens, and the cursor is sensitive to operator control, showing that Macro control has been lost. I have inserted a test 'MsgBox' after the .Open command, and not even that is executed - so I am certain that the .Open is in some way wiping out the Macro.
View 9 Replies
View Related
Sep 3, 2007
I am a novice at excel macro programming. My Excel 2000 macro inexplicably stops after the line: Workbooks.Open Filename:=stAddrFullname, ReadOnly:=True
1) I know that the remainder of the code is not executed because a breakpoint on the following line is never reached.
2) I know that this line is executed because the stAddrFullname workbook is clearly opened and is displayed when the code stops prematurely.
3) No error message is displayed and I have no 'OnError' instructions in the code.
4) (This is the bit I really don't understand). If I put a Breakpoint in the macro anywhere in the function preceding the "Workbooks.Open" statement, the macro reaches the Breakpoint and then runs through to completion with no problem. When I remove the breakpoint, it reverts to stopping as soon as it has opened the Workbook.
5) The stAddrFullname workbook has no macros in it.
View 5 Replies
View Related
Aug 26, 2007
I have a textbox from the drawing toolbar. When someone changes a cell then clicks in the textbox, Worksheet_Change does not run. If they double click in the cell, that's OK I can capture that event and protect the sheet, stopping them clicking in it. But if they just start typing in the cell, I can't capture that. I have seen some API code which captures keypresses, but it is not practical to use as it loops repeatedly. I could lock the textbox and have the user do something to unlock it, but this is a last resort.
View 7 Replies
View Related
Jan 7, 2009
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
View 9 Replies
View Related
Oct 15, 2008
I have a macro that that stops every time I’m trying to delete the sheet. I get this message. “Data may exist in the sheet(s) selected for deletion. To permanently delete the data, prese Delete.” and I have to manually click “delete” to continue running the macro. How can I avoid this stop so that macro runs thru it without stops?
View 2 Replies
View Related
May 10, 2006
I have built a small userform with 3 fields. The macro ran OK first few times. Now, when user enters data in the form and clicks OK nothing happens. I found that repeated clicking on OK or Cancel button on form had no effect. I then observed that VBA editor was open and the yellow cursor was displayed on the first code line under cmdOK_click procedure. The code line was also highlighted in yellow. There are no errors to debug and no break points etc. When I clicked the Run (or Continue) icon from the VBA toolbar, the macro completed OK. Question: Why is the macro pausing on the first line and how can I make it run without pausing for no apparent reason.
View 4 Replies
View Related
