Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How to run macros and make the workbooks invisible
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default How to run macros and make the workbooks invisible
Tom, it works with or without the sreenupdating
statement.I want the workbooks to be invisible. I think
all i have to do is delete or modify something. I purpose
of this recorded code is to import about 5 text files.
Clean each files up and save it a excel web pages. I will
be using this same process very week. Instead of going
through the same process, i want to be able to run the
recorded marco.BUT I WANT THE WORKBOOKS(KEN, HECTOR, ETC)
TO BE INVISIBLE TO THE USER. Thanks for all your help. I
think was not making myslef clear. see codes. I think on
the next import text i should use the same workbooks but
how?

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 550
Default How to run macros and make the workbooks invisible
christine,

You should stick with one thread and in one group.
Very confusing.
I don't believe that you can make a single workbook invisible
when you open it from a macro.
What you can do is make the entire application invisible until
your macro(s) are done.
Application.Visible = False.
Use Application.Visible = True at the end when
you've already closed the other workbooks.

John


"christine" wrote in message
...
Tom, it works with or without the sreenupdating
statement.I want the workbooks to be invisible. I think
all i have to do is delete or modify something. I purpose
of this recorded code is to import about 5 text files.
Clean each files up and save it a excel web pages. I will
be using this same process very week. Instead of going
through the same process, i want to be able to run the
recorded marco.BUT I WANT THE WORKBOOKS(KEN, HECTOR, ETC)
TO BE INVISIBLE TO THE USER. Thanks for all your help. I
think was not making myslef clear. see codes. I think on
the next import text i should use the same workbooks but
how?

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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default How to run macros and make the workbooks invisible
I don't believe that you can make a single workbook invisible
when you open it from a macro.

Sub OpenWorkbookInvisibly()
Dim wkbk as Workbook
Dim sh as Worksheet
Application.ScreenUpdating = False
set wkbk = Workbooks.Open("C:\Test.xls")
for each sh in wkbk.Worksheets
sh.Activate
for each cell in sh.Range("A1:G15")
cell.Select
next
Next
wkbk.close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Put a workbook named Test.xls in your root directory and run the above.
Ever see Test.xls?

--
Regards,
Tom Ogilvy




John Wilson wrote in message
...
christine,

You should stick with one thread and in one group.
Very confusing.
I don't believe that you can make a single workbook invisible
when you open it from a macro.
What you can do is make the entire application invisible until
your macro(s) are done.
Application.Visible = False.
Use Application.Visible = True at the end when
you've already closed the other workbooks.

John


"christine" wrote in message
...
Tom, it works with or without the sreenupdating
statement.I want the workbooks to be invisible. I think
all i have to do is delete or modify something. I purpose
of this recorded code is to import about 5 text files.
Clean each files up and save it a excel web pages. I will
be using this same process very week. Instead of going
through the same process, i want to be able to run the
recorded marco.BUT I WANT THE WORKBOOKS(KEN, HECTOR, ETC)
TO BE INVISIBLE TO THE USER. Thanks for all your help. I
think was not making myslef clear. see codes. I think on
the next import text i should use the same workbooks but
how?

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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 550
Default How to run macros and make the workbooks invisible
Tom,

So I learn something new every day. <g
This, of course, is dependent on the ScreenUpdating
setting "sticking" (mentioned in another thread).
Tried your code below and it worked perfectly.
I have a few workbooks where I've tried this and it doesn't
seem to work. I'll look at them tomorrow to see what else
might be resetting the ScreenUpdating.

Thanks,
John



"Tom Ogilvy" wrote in message
...
I don't believe that you can make a single workbook invisible
when you open it from a macro.

Sub OpenWorkbookInvisibly()
Dim wkbk as Workbook
Dim sh as Worksheet
Application.ScreenUpdating = False
set wkbk = Workbooks.Open("C:\Test.xls")
for each sh in wkbk.Worksheets
sh.Activate
for each cell in sh.Range("A1:G15")
cell.Select
next
Next
wkbk.close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Put a workbook named Test.xls in your root directory and run the above.
Ever see Test.xls?

--
Regards,
Tom Ogilvy


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 691
Default How to run macros and make the workbooks invisible
I don't know about the multiple workbooks part, but

It is generally not necessary to select something, and
you have a lot of selection going on. Example.

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

I think can be reduced to
Rows("1:13").Delete Shift:=xlUp
Columns("A:D").EntireColumn.AutoFit
Range("A57:B378").ClearContents

The have a lot of formatting going on in your recorded
macro. Check that you need all of it, frequently things
get included that aren't really being changed.

try using something like

With Range("A3:B324")
.font.ColorIndex = 2
.interior.colorindex = 3
.interior.pattern = xlSolid
.Font.Bold = True
End With

instead of `

Range("A3:B324").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"christine" wrote in message ...
Tom, it works with or without the sreenupdating


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default How to run macros and make the workbooks invisible
John does raise a good point

Try this:

Sub Test()
Application.Visible = False
' code without Application.ScreenUpdating


'
Application.Visible = True
End Sub

Perhaps that will be what you want.

--
Regards,
Tom Ogilvy


christine wrote in message
...
Tom, it works with or without the sreenupdating
statement.I want the workbooks to be invisible. I think
all i have to do is delete or modify something. I purpose
of this recorded code is to import about 5 text files.
Clean each files up and save it a excel web pages. I will
be using this same process very week. Instead of going
through the same process, i want to be able to run the
recorded marco.BUT I WANT THE WORKBOOKS(KEN, HECTOR, ETC)
TO BE INVISIBLE TO THE USER. Thanks for all your help. I
think was not making myslef clear. see codes. I think on
the next import text i should use the same workbooks but
how?

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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default TO TOM AND JOHN..
Thanks tom but john's statement is exactly what i was
looking for. thanks John.
-----Original Message-----
Tom,

So I learn something new every day. <g
This, of course, is dependent on the ScreenUpdating
setting "sticking" (mentioned in another thread).
Tried your code below and it worked perfectly.
I have a few workbooks where I've tried this and it
doesn't
seem to work. I'll look at them tomorrow to see what else
might be resetting the ScreenUpdating.

Thanks,
John



"Tom Ogilvy" wrote in message
...
I don't believe that you can make a single workbook
invisible
when you open it from a macro.

Sub OpenWorkbookInvisibly()
Dim wkbk as Workbook
Dim sh as Worksheet
Application.ScreenUpdating = False
set wkbk = Workbooks.Open("C:\Test.xls")
for each sh in wkbk.Worksheets
sh.Activate
for each cell in sh.Range("A1:G15")
cell.Select
next
Next
wkbk.close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Put a workbook named Test.xls in your root directory
and run the above.
Ever see Test.xls?

--
Regards,
Tom Ogilvy


.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make a checkbox on a chart invisible when it is clicked? Naum Charts and Charting in Excel 2 December 20th 07 08:10 PM
make button invisible if macros disabled Horatio J. Bilge, Jr. Excel Discussion (Misc queries) 0 November 29th 07 08:43 PM
How to make hidden sheet invisible LLee2 New Users to Excel 9 April 8th 07 06:58 AM
How to make the Toolbar Invisible through VBA Atif Akbar[_2_] Excel Programming 2 December 11th 03 06:55 AM
Make one column Invisible. Neeraja Excel Programming 5 September 29th 03 02:58 AM


All times are GMT +1. The time now is 07:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement