Macro freeze up

  • Thread starter Thread starter RHall
  • Start date Start date
R

RHall

I can run a macro from the VBA Editor but when I run it from the Ctrl key
set for it, it stops after selecting from a file that it has opened. This
happens when I run macros on two different workbooks and different macros.
The sheet is open and on the screen. I can't step through the macro since it
runs fine when in the editor.
I''ve tried it with the sheet protection on and off. I'm stuck. I can't
turn the system over to the user if they can't use the CTRL+key to run it.
Any suggestions would be appreciated.
P.S. My HPageBreak doesn't work either. I used the code from this site.
The page break also doen't work from Excel-Insert-Page Break either.
I'm running Excel 2002 with no upgrades or fixes.
 
If you have Application.screenupdating = false in your macro, try removing
it. One of the subtle differences between stepping through a macro and
running it is that screenupdating is always on when you step through it. If
the macro is doing charting work, I have found that updating must be on or
else some charting commands will not work.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Workbooks.Open Filename:="Jobs Data.xls"
This line executes but the next line which selects a sheet doesn't
Screen updating is True
 
Without being able to see the code, anything offered is strictly a guess. If
you want real assistance, please post the code that gives the problem.
Simply copy it and paste it into the newsgroup reply pane.
 
Here is the code, this is called from another macro. This same problem is
happening in more than just this macro.

Sub getjobs()
'
' Macro recorded 1/7/2008 by R.Hall: Max. 99 Job names from Job Data file
'
' Active Workbook is Time Sheet
Dim r As Integer
' Clear Jobs on Time Sheet
Windows("Time Sheet Input").Activate
Sheets("totalweek").Select
ActiveSheet.Unprotect
Range("F3:CZ3").Select
Selection.Clear
' Get Jobs from Jobs Data Workbook
Workbooks.Open Filename:="Jobs Data.xls"
Sheets("ScratchSheet").Select
Cells.Select
Selection.Clear
'
Sheets("Projects_Info").Select
ActiveSheet.Unprotect
' MsgBox "Just unprotected Jobs Data"
' Highlight Jobs With either 5509 or 9999 W/C Code in Col M
' Put in coding here
Range("A3").Select
Do While ActiveCell.Value <> Empty
'
If ActiveCell.Offset(0, 12) = 9999 Or _
ActiveCell.Offset(0, 12) = 5509 Then
Selection.Interior.ColorIndex = 43
'**************** Selection.Border.Outline
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
'
'*****************************************
Sheets("Projects_Info").Select ' THIS EXECUTES AND THEN IT STOPS
HERE *
Range("A3:A101").Select ' When executed with Ctrl key
*
Selection.Copy
'*****************************************

Sheets("ScratchSheet").Select
Range("A3:A101").PasteSpecial Paste:=xlPasteAll
Range("C3:C101").PasteSpecial Paste:=xlPasteFormats

Range("A3").Select
Jcount = Range(ActiveCell, ActiveCell.End(xlDown)).count
Sheets("Projects_Info").Select
Application.CutCopyMode = False
Range("C3:C101").Select
Selection.Copy
Sheets("ScratchSheet").Select
Range("B3:B101").PasteSpecial Paste:=xlPasteAll

' Put Formula in Col.C to join First word in
' Contractor Name and Project Name
Range("D3").Select

ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(FIND("" "",RC[-2])),RC[-2],LEFT(RC[-2],FIND(""
"",RC[-2])-1))"
Range("D3").Select
Range(Cells(3, 4), Cells(Jcount + 2, 4)).Select
Selection.FillDown
Range("A3:A101").Copy
Range("D3:D101").PasteSpecial Paste:=xlPasteFormats
Range(Cells(3, 4), Cells(Jcount + 2, 4)).Select

Range("C3").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[+1],""^"",RC[-2])"

Range(Cells(3, 3), Cells(Jcount + 2, 3)).Select
Selection.FillDown
Range("A3:A101").Copy
Range("C3:C101").PasteSpecial Paste:=xlPasteFormats
Range(Cells(3, 3), Cells(Jcount + 2, 3)).Select
Selection.Copy

' Put jobs on time sheet
Windows("Time Sheet Input").Activate
Sheets("totalweek").Select

Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Selection.PasteSpecial Paste:=xlPasteFormats, Transpose:=True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = -90
.ReadingOrder = xlContext
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
End With

' Unlock cells so Jobs can be added
Sheets("totalweek").Select
Range("e3").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Locked = True
r = Selection.count + 1
Range(Cells(3, r + 4), Cells(3, r + 23)).Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("D5").Select

Windows("Jobs Data.xls").Activate
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False
' Put Job Heading on all Daily Time Sheets
Sheets("totalweek").Select
Range("F3:CZ3").Select
Selection.Copy
For Sheet = 1 To 14
Sheets(Sheet).Select
Range("F3:cz3").Select
Selection.PasteSpecial Paste:=xlPasteAll
Range("D5").Select
Next Sheet
'
End Sub
 
Back
Top