PDA

View Full Version : Column Formatting Works in one instance, but not another



bassnsjp
06-02-2009, 06:43 AM
I'm using MS Office 2003 in an Windows XP Pro environment.

The code is on another PC that I cannot access so I must retype it so, please bare with me. The issue I'm having is that in testing the code in a small macro the column formatting works, but for whatever reason nearly the same code does not in the operational macro. I'm banging my head against the wall and hope that someone can help. It's frustrating to say the least.

Here is the test code:

Sub Testfmt()
Worksheets("Inv").Columns("C:D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With

End Sub

Here is the operational code:

Call Setup_wks ("Inv")


Sub Setup_wks(ByVal CURwksname As String)

Worksheets(CURwksname).Cells(3, 1) = "Part No"
Worksheets(CURwksname).Cells(3, 2) = "Description"
Worksheets(CURwksname).Cells(3, 3) = "Qty"
Worksheets(CURwksname).Cells(3, 4) = "Qty"
Worksheets(CURwksname).Cells(3, 5) = "Description"
Worksheets(CURwksname).Cells(3, 6) = "Part No"
Worksheets(CURwksname).Columns("C:D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
etc.
etc.

End Sub

First three statements are processed with no problems, but once the .Columns statement is reached the control goes back to the main subroutine, which is two levels up from the Setup_wks subroutine. In that the main calls subroutine Finalize_wks which in turn calls Setup_wks. In stepping through the macro I confirmed the value of CURwksname is correct and the other cells are formatting with no problem. I also changed .Columns("C:D") to .Range("C:D") with the same results.

The test code works with no problems whatsoever on the same worksheet and data and it produces the desired results.

Any assistance would be greately appreciated, thanks in advance.

MaximS
06-02-2009, 09:01 AM
try that:


Sub a()
Call Setup_wks("Sheet2")
End Sub
Sub Setup_wks(ByVal CURwksname As String)
Worksheets(CURwksname).Cells(3, 1) = "Part No"
Worksheets(CURwksname).Cells(3, 2) = "Description"
Worksheets(CURwksname).Cells(3, 3) = "Qty"
Worksheets(CURwksname).Cells(3, 4) = "Qty"
Worksheets(CURwksname).Cells(3, 5) = "Description"
Worksheets(CURwksname).Cells(3, 6) = "Part No"
With Worksheets(CURwksname).Range("C1:C" & Rows.Count)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
End Sub

bassnsjp
06-02-2009, 07:56 PM
I will give it a shot tomorrow and let you know, thanks for the taking the time to reply.

bassnsjp
06-07-2009, 06:30 PM
MaximS,

I was not able to test your recommendation until late on Friday. Thank you for your assistance, what you suggested worked. The question remains why didn't the other statement work especially since it worked in my test routine, but not when I inserted it into my operational macro???? Very Strange!!!

mikerickson
06-07-2009, 06:36 PM
Does the sheet have a SelectionChange event routine?

bassnsjp
06-07-2009, 06:56 PM
No, it does not.

MaximS
06-08-2009, 04:01 AM
Got no idea but for some reasons Excel doesn't like the columns as much as ranges.