how to run recorded macros withour showing processing excel sheets

  • Thread starter Thread starter christine
  • Start date Start date
C

christine

Can someone help me plzzzzzzz.
I have recorded some macros that whenever i run them i
have to run them through an excel sheet. And it display
all the excel sheets. About 10 separate sheets.I would
like to
know how to run the macros without showing all of the
processing excel sheets. Without user viewing the actual
processes.
Thanks in advance

Please this macro does not work:

at the beginning:

Application.ScreenUpdating = False


at the end

application.ScreenUpdating = True
 
christine,

Sometimes Activating/Selecting sheets negates the
Application.ScreenUpdating setting.

Without seeing the code, it's hard to say what may be
resetting that setting.

Recorded macros almost always use Select and Activate
in the recorded code but it's rarely necessary to do that
with an optimized macro.

Why not post the code and maybe someone can help with
a better way to do it (and keep those sheets from flashing
in front of you).

John
 
You posted somewhere else as well, and got answers there.
I presume it was excel.programming which is the correct
newsgroup for macros. You might also take a look a my
slowresp.htm webpage. You can refer to another worksheet
without selecting the worksheet.
 
John,

Can you post some sample code where selecting or activating disables the
screenupdating setting. This is a new one on me and I would definitely be
interested in the particulars.

Thanks.
 
Tom,
This is a new one on me
Click to expand...
Given your expertise in Excel and the fact that you doubt me
leads me to believe that I've made a wrong assumption.
(Wouldn't be the first time for me).

I do know that setting ScreenUpdating to False doesn't
always "stick". Exactly why, I don't know (and maybe I shouldn't
have made the assumption that I did).

Judging from this OP's post and what I can find in Google,
the ScreenUpdating setting not "sticking" is a common problem.
Can you post some sample code
Click to expand...
At the moment, no. When I get back to work on Monday, I can
send you some workbooks where I can't stop the sheets from
flashing in front of me as my macros are running.
I would definitely be interested in the particulars.
Click to expand...
I would definitely be interested in knowing what I'm doing
wrong as it would definitely speed up my applications if
I didn't have to flash worksheets while my macro(s) were running.

Thanx,
John
 
I didn't say it doesn't happen. I have seen the posts claiming problems. I
said I have never had a problem with it. I believe calling procedures from
the analysis toolpak can cause screenupdating to get reset. I would suspect
that interacting with other addins could have a similar effect.

Christine has posted sample code in programming which she says is
problematic. Ran like a charm for me. No flashing and she does a lot of
selecting, activating, filtering and formatting. Macro Runs, sceen doesn't
change, final product appears.
I tested in xl97, so unless it is a version thing, I can't reproduce a
problem. I had to change two arguments to the opentext method which were
not supported in xl97 and I saved the file as CSV rather than xlHtml which
again isn't supported in xl97. I would be suprised if those would cause the
problem, but I could be wrong.
 
The answer from tom does not work. How do i select or
modify the recorded code?
-----Original Message-----
You posted somewhere else as well, and got answers there.
I presume it was excel.programming which is the correct
newsgroup for macros. You might also take a look a my
slowresp.htm webpage. You can refer to another worksheet
without selecting the worksheet.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


Can someone help me plzzzzzzz.
I have recorded some macros that whenever i run them i
have to run them through an excel sheet. And it display
all the excel sheets. About 10 separate sheets.I would
like to
know how to run the macros without showing all of the
processing excel sheets. Without user viewing the actual
processes.
Thanks in advance

Please this macro does not work:

at the beginning:

Application.ScreenUpdating = False


at the end

application.ScreenUpdating = True
Click to expand...


.
Click to expand...
 
I think there is something in thise code that i have to
modify.
Sub test()
Application.ScreenUpdating = False
ChDir "C:\Documents and Settings\Desktop\Marco"
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Hector.tvr", Origin _
:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 9), _
Array(4, 1), Array(14, 1), Array(36, 1), Array
(58, 1)), TrailingMinusNumbers:=True
Rows("1:13").Select
Selection.Delete Shift:=xlUp
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("A:D").Select
Range("A57:B378").Select
Selection.ClearContents
Columns("A:D").Select
Selection.AutoFilter Field:=1, Criteria1:="Selected"
Selection.ClearContents
Columns("A:D").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=*rank*",
Operator:=xlAnd
Range("A3:B324").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("C3:C324").Select
Selection.ClearContents
Selection.AutoFilter Field:=1
Columns("A:D").Select
ActiveSheet.ShowAllData
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Date"
Range("A4:D325").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Selection.AutoFilter Field:=1, Criteria1:="="
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.AutoFilter Field:=3, Criteria1:="="
Selection.AutoFilter Field:=4, Criteria1:="="
Rows("5:376").Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Columns("A:D").Select
Selection.AutoFilter
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Schedules"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Exception "
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("A1:D1").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A1:D256").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("B10").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Hector.htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False,
CreateBackup:=False
Selection.Delete Shift:=xlToLeft
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Ken.tvr", Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 9), _
Array(4, 1), Array(14, 1), Array(36, 1), Array
(57, 1), Array(64, 1)), _
TrailingMinusNumbers:=True
Rows("1:13").Select
Selection.Delete Shift:=xlUp
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("A:E").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="}"
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="="
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.AutoFilter Field:=3, Criteria1:="="
Selection.AutoFilter Field:=4, Criteria1:="="
Selection.AutoFilter Field:=5, Criteria1:="="
Rows("2:460").Select
Range("A460").Activate
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("B406").Select
ActiveWindow.LargeScroll Down:=-10
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Columns("A:E").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Date"
Range("A3:E303").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Selection.AutoFilter Field:=3
ActiveSheet.ShowAllData
Columns("A:E").Select
Selection.AutoFilter Field:=3, Criteria1:="=*rank*",
Operator:=xlAnd
Range("A2:B302").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("C2:C302").Select
Selection.ClearContents
Columns("A:E").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter
Columns("C:C").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""vacation"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 1
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""personal day off"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(2).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""e-time"""
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(3).Interior.ColorIndex = 10
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Schedules"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Exception"
Range("D1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""vacation"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 1
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""personal day off"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(2).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""e-time"""
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(3).Interior.ColorIndex = 10
ActiveCell.FormulaR1C1 = "Start"
Range("E1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""vacation"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 1
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""personal day off"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(2).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""e-time"""
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(3).Interior.ColorIndex = 10
ActiveCell.FormulaR1C1 = "Stop"
Range("A1:E1").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A1:E324").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G19").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Ken.htm", FileFormat _
:=xlHtml, ReadOnlyRecommended:=False,
CreateBackup:=False
Application.ScreenUpdating = True
End Sub
 
are you "christine" <[email protected]> (the original poster)
whom Tom said has posted sample code in programming
http://groups.google.com/[email protected]

or are you someone else. It is very difficult to follow a conversation
that is in multiple groups and in multiple threads within a group
and with possibly multiple persons presenting a problem in a thread.


The answer from tom does not work. How do i select or
modify the recorded code?
-----Original Message-----
You posted somewhere else as well, and got answers there.
I presume it was excel.programming which is the correct
newsgroup for macros. You might also take a look a my
slowresp.htm webpage. You can refer to another worksheet
without selecting the worksheet.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


Can someone help me plzzzzzzz.
I have recorded some macros that whenever i run them i
have to run them through an excel sheet. And it display
all the excel sheets. About 10 separate sheets.I would
like to
know how to run the macros without showing all of the
processing excel sheets. Without user viewing the actual
processes.
Thanks in advance

Please this macro does not work:

at the beginning:

Application.ScreenUpdating = False


at the end

application.ScreenUpdating = True
Click to expand...


.
Click to expand...
Click to expand...
 
David,
It is very difficult to follow a conversation
that is in multiple groups and in multiple threads within a group
and with possibly multiple persons presenting a problem in a thread.
Click to expand...

Agreed. I just vented much the same frustration to christine's
latest post in .programming.

John

David McRitchie said:
are you "christine" <[email protected]> (the original poster)
whom Tom said has posted sample code in programming
http://groups.google.com/[email protected]

or are you someone else. It is very difficult to follow a conversation
that is in multiple groups and in multiple threads within a group
and with possibly multiple persons presenting a problem in a thread.


The answer from tom does not work. How do i select or
modify the recorded code?
-----Original Message-----
You posted somewhere else as well, and got answers there.
I presume it was excel.programming which is the correct
newsgroup for macros. You might also take a look a my
slowresp.htm webpage. You can refer to another worksheet
without selecting the worksheet.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm



Can someone help me plzzzzzzz.
I have recorded some macros that whenever i run them i
have to run them through an excel sheet. And it display
all the excel sheets. About 10 separate sheets.I would
like to
know how to run the macros without showing all of the
processing excel sheets. Without user viewing the actual
processes.
Thanks in advance

Please this macro does not work:

at the beginning:

Application.ScreenUpdating = False


at the end

application.ScreenUpdating = True




.
Click to expand...
Click to expand...
Click to expand...
 
Tom,

Checked a few of my workbooks where I was having problems
with the Application.ScreenUpdating setting not "sticking".

Seems that I had a lot of places where "just to make sure",
I would turn it off at the beginning of a sub and back on
at the end. Since I didn't do it in "All" the subs, it left
quite a few "gaps".

Taking from your example, I cleaned everything out except
for the first "False" and the last "True" and it worked like a charm.

Thanks,
John
 
Back
Top