Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Inconsistent Macro Behavior
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sarah K
 
Posts: n/a
Default Inconsistent Macro Behavior
I have a client who created a macro that applies different formatting to
columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
works fine. When she uses it on a spreadsheet created from one of our
proprietary applications (spreadsheet starts out html, not xls), instead of
highlighting just the one column, it highlights the entire spreadsheet and
applies the formatting to all the columns.

Here are some of the things I've tried:
1. Happens in both Excel XP and 2003
2. Saved file as .xls first, then ran macro (still happens)
3. Tried on different computers (still happens)
4. Copied content and pasted only values in new worksheet (macro works
fine - but there are other issues with doing that)

Any ideas on why the macro highlights all columns?
Reply With QuoteReply With Quote
  #2   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default Inconsistent Macro Behavior
Hard to guess without seeing the code.

Jerry

Sarah K wrote:

I have a client who created a macro that applies different formatting to
columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
works fine. When she uses it on a spreadsheet created from one of our
proprietary applications (spreadsheet starts out html, not xls), instead of
highlighting just the one column, it highlights the entire spreadsheet and
applies the formatting to all the columns.

Here are some of the things I've tried:
1. Happens in both Excel XP and 2003
2. Saved file as .xls first, then ran macro (still happens)
3. Tried on different computers (still happens)
4. Copied content and pasted only values in new worksheet (macro works
fine - but there are other issues with doing that)

Any ideas on why the macro highlights all columns?

Reply With QuoteReply With Quote
  #3   Report Post  
Sarah K
 
Posts: n/a
Default Inconsistent Macro Behavior
Here's the code:
Sub Construction()
'
' Construction Macro
' Macro recorded 10/25/2005 by jmcgoogan
'

'
Cells.Select
With Selection.Font
.Name = "Arial Narrow"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Arial Narrow"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("E5").Select
ActiveWindow.WindowState = xlMaximized
Range("A6:M93").Select
Range("A93").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.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("L100").Select
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial Narrow,Regular""&8Advance Mechanical Systems"
.CenterFooter = ""
.RightFooter = "&""Arial Narrow,Regular""&8Page &P"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.43
Rows("9:9").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").ColumnWidth = 17
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 5.43
Selection.ColumnWidth = 7
Columns("D:D").ColumnWidth = 8.71
Columns("E:E").ColumnWidth = 25
Columns("F:F").ColumnWidth = 9
Columns("F:F").ColumnWidth = 7.86
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
Selection.ColumnWidth = 6.57
Columns("H:I").Select
Selection.NumberFormat = "$#,##0"
Selection.ColumnWidth = 6.43
ActiveWindow.SmallScroll ToRight:=6
Selection.ColumnWidth = 9.14
Columns("H:H").Select
Selection.ColumnWidth = 6.29
Columns("J:J").Select
Selection.ColumnWidth = 7.14
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.29
Columns("K:K").ColumnWidth = 8
Columns("K:K").Select
Selection.NumberFormat = "$#,##0"
Columns("L:L").ColumnWidth = 7
Columns("M:M").ColumnWidth = 7.14
Columns("A:A").ColumnWidth = 7
Columns("A:A").ColumnWidth = 8
End Sub


"Jerry W. Lewis" wrote:

Hard to guess without seeing the code.

Jerry

Sarah K wrote:

I have a client who created a macro that applies different formatting to
columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
works fine. When she uses it on a spreadsheet created from one of our
proprietary applications (spreadsheet starts out html, not xls), instead of
highlighting just the one column, it highlights the entire spreadsheet and
applies the formatting to all the columns.

Here are some of the things I've tried:
1. Happens in both Excel XP and 2003
2. Saved file as .xls first, then ran macro (still happens)
3. Tried on different computers (still happens)
4. Copied content and pasted only values in new worksheet (macro works
fine - but there are other issues with doing that)

Any ideas on why the macro highlights all columns?


Reply With QuoteReply With Quote
  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default Inconsistent Macro Behavior
Just a guess...

Do you have any merged cells in that worksheet?

If I recall correctly, different versions of excel would handle merged cells
differently.

(This is subject to a failing memory...)

Say you have A6:M6 merged as a giant cell;
(Format|cells|alignment tab|check Merge cells)

I think if you selected (say) column C, then xl97 would select all of A:M.
Later versions of xl handled the merged cells better. (I use xl2003 and
selecting column C just selected column C.)



Sarah K wrote:

Here's the code:
Sub Construction()
'
' Construction Macro
' Macro recorded 10/25/2005 by jmcgoogan
'

'
Cells.Select
With Selection.Font
.Name = "Arial Narrow"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Arial Narrow"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("E5").Select
ActiveWindow.WindowState = xlMaximized
Range("A6:M93").Select
Range("A93").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.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("L100").Select
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial Narrow,Regular""&8Advance Mechanical Systems"
.CenterFooter = ""
.RightFooter = "&""Arial Narrow,Regular""&8Page &P"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.43
Rows("9:9").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").ColumnWidth = 17
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 5.43
Selection.ColumnWidth = 7
Columns("D:D").ColumnWidth = 8.71
Columns("E:E").ColumnWidth = 25
Columns("F:F").ColumnWidth = 9
Columns("F:F").ColumnWidth = 7.86
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
Selection.ColumnWidth = 6.57
Columns("H:I").Select
Selection.NumberFormat = "$#,##0"
Selection.ColumnWidth = 6.43
ActiveWindow.SmallScroll ToRight:=6
Selection.ColumnWidth = 9.14
Columns("H:H").Select
Selection.ColumnWidth = 6.29
Columns("J:J").Select
Selection.ColumnWidth = 7.14
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.29
Columns("K:K").ColumnWidth = 8
Columns("K:K").Select
Selection.NumberFormat = "$#,##0"
Columns("L:L").ColumnWidth = 7
Columns("M:M").ColumnWidth = 7.14
Columns("A:A").ColumnWidth = 7
Columns("A:A").ColumnWidth = 8
End Sub

"Jerry W. Lewis" wrote:

Hard to guess without seeing the code.

Jerry

Sarah K wrote:

I have a client who created a macro that applies different formatting to
columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
works fine. When she uses it on a spreadsheet created from one of our
proprietary applications (spreadsheet starts out html, not xls), instead of
highlighting just the one column, it highlights the entire spreadsheet and
applies the formatting to all the columns.

Here are some of the things I've tried:
1. Happens in both Excel XP and 2003
2. Saved file as .xls first, then ran macro (still happens)
3. Tried on different computers (still happens)
4. Copied content and pasted only values in new worksheet (macro works
fine - but there are other issues with doing that)

Any ideas on why the macro highlights all columns?



--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Sarah K
 
Posts: n/a
Default Inconsistent Macro Behavior
Dave,
Thank you. That's it. I didn't even THINK to look for merged cells.

Sarah

"Dave Peterson" wrote:

Just a guess...

Do you have any merged cells in that worksheet?

If I recall correctly, different versions of excel would handle merged cells
differently.

(This is subject to a failing memory...)

Say you have A6:M6 merged as a giant cell;
(Format|cells|alignment tab|check Merge cells)

I think if you selected (say) column C, then xl97 would select all of A:M.
Later versions of xl handled the merged cells better. (I use xl2003 and
selecting column C just selected column C.)



Sarah K wrote:

Here's the code:
Sub Construction()
'
' Construction Macro
' Macro recorded 10/25/2005 by jmcgoogan
'

'
Cells.Select
With Selection.Font
.Name = "Arial Narrow"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Arial Narrow"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("E5").Select
ActiveWindow.WindowState = xlMaximized
Range("A6:M93").Select
Range("A93").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.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("L100").Select
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial Narrow,Regular""&8Advance Mechanical Systems"
.CenterFooter = ""
.RightFooter = "&""Arial Narrow,Regular""&8Page &P"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.43
Rows("9:9").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").ColumnWidth = 17
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 5.43
Selection.ColumnWidth = 7
Columns("D:D").ColumnWidth = 8.71
Columns("E:E").ColumnWidth = 25
Columns("F:F").ColumnWidth = 9
Columns("F:F").ColumnWidth = 7.86
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
Selection.ColumnWidth = 6.57
Columns("H:I").Select
Selection.NumberFormat = "$#,##0"
Selection.ColumnWidth = 6.43
ActiveWindow.SmallScroll ToRight:=6
Selection.ColumnWidth = 9.14
Columns("H:H").Select
Selection.ColumnWidth = 6.29
Columns("J:J").Select
Selection.ColumnWidth = 7.14
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.29
Columns("K:K").ColumnWidth = 8
Columns("K:K").Select
Selection.NumberFormat = "$#,##0"
Columns("L:L").ColumnWidth = 7
Columns("M:M").ColumnWidth = 7.14
Columns("A:A").ColumnWidth = 7
Columns("A:A").ColumnWidth = 8
End Sub

"Jerry W. Lewis" wrote:

Hard to guess without seeing the code.

Jerry

Sarah K wrote:

I have a client who created a macro that applies different formatting to
columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
works fine. When she uses it on a spreadsheet created from one of our
proprietary applications (spreadsheet starts out html, not xls), instead of
highlighting just the one column, it highlights the entire spreadsheet and
applies the formatting to all the columns.

Here are some of the things I've tried:
1. Happens in both Excel XP and 2003
2. Saved file as .xls first, then ran macro (still happens)
3. Tried on different computers (still happens)
4. Copied content and pasted only values in new worksheet (macro works
fine - but there are other issues with doing that)

Any ideas on why the macro highlights all columns?



--

Dave Peterson

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

Advanced Search
Display Modes
Hybrid Mode Hybrid 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
Closing File Error jcliquidtension Excel Discussion (Misc queries) 4 October 20th 05 12:22 PM
Strange Personal Macro Workbook Behavior stretch Excel Discussion (Misc queries) 4 August 4th 05 07:39 PM
Help with macro looping and color query function kevinm Excel Discussion (Misc queries) 10 May 26th 05 01:25 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 10:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 12:40 AM


All times are GMT +1. The time now is 05:04 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement