Problem to ColumnWidth

Hi,

Further to this thread,
https://www.experts-exchange.com/questions/29086897/Problem-to-SaveAs-line.html#a42487490

what can be the reason of error
21u.png
due to last ColumnWidth line below
            ...
            Application.Workbooks(Window0).Close SaveChanges:=False
            If cnt0 = 1 Then
                Set HOBook = Workbooks.Add
                With HOBook
                    .Title = Title0
                    .Subject = Title0
                    .SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                    '.SaveAs Filename:=File0, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                    .Close SaveChanges:=False
                End With

                With Worksheets("Sheet1").Columns("A")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("B")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("D")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("E")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("F")
                 .ColumnWidth = .ColumnWidth * 3.5
                End With

Open in new window

LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

is there a merged cell or
Maximum attainable column width is 255.

Regards
0
Roy CoxGroup Finance ManagerCommented:
Is the worksheet or workbook protected?
0
ste5anSenior DeveloperCommented:
Just a comment:

Multiplying column widths is pretty uncommon. I use either AutoFit() or fixed column width values.

Clean up your code.. e.g.

Option Explicit

Private Sub MultiplyColumnWidth(ARange As Excel.Range, AFactor As Double)

  ARange.ColumnWidth = ARange.ColumnWidth * AFactor

End Sub
                               
Public Sub test()

  Dim CurrentSheet As Excel.Worksheet
                
  Set CurrentSheet = Worksheets(2)
  MultiplyColumnWidth CurrentSheet.Columns("A"), 2
  MultiplyColumnWidth CurrentSheet.Columns("B"), 2
  MultiplyColumnWidth CurrentSheet.Columns("D"), 2
  MultiplyColumnWidth CurrentSheet.Columns("E"), 2
  MultiplyColumnWidth CurrentSheet.Columns("F"), 3.5
  Set CurrentSheet = Nothing

End Sub

Open in new window


Cause now you can add proper error handling to your method.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HuaMin ChenProblem resolverAuthor Commented:
Thanks to all.
There is no merged cell. Worksheet and Workbook is not protected.
0
Rgonzo1971Commented:
and what is the goal width of Col F?
0
Roy CoxGroup Finance ManagerCommented:
Your code multiplies the columnwidth by two, so eventually it is going to cause problems. The result needs checking first

   
 With Worksheets("Sheet1").Columns("A")
        If .ColumnWidth * 2 <= 255 Then
            .ColumnWidth = .ColumnWidth * 2
        Else: MsgBox "Cannot increase Column Width"
               Exit Sub
        End With
    End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
In my 2 answers, I pointed the problem of the max width, but you ignored them, pls read carefully all answers
1
ste5anSenior DeveloperCommented:
And I would do it like this:

Option Explicit

Private Sub MultiplyColumnWidth(ARange As Excel.Range, AFactor As Double)
  
  On Local Error GoTo LocalError

  Const MAX_COLUMN_WIDTH As Long = 255
  
  Dim NewWidth As Long
  
  NewWidth = ARange.ColumnWidth * AFactor
  If NewWidth <= MAX_COLUMN_WIDTH Then
    ARange.ColumnWidth = NewWidth
  Else
    Debug.Print "Column " & ARange.Address & " exceeds minmium width (" & ARange.ColumnWidth & ") for current factor (" & AFactor & ")."
  End If
  
  Exit Sub

LocalError:
  Debug.Print "Error " & Err.Number & " while settting new width of " & ARange.Address & "."

End Sub
                               
Public Sub test()

  Dim CurrentSheet As Excel.Worksheet
                
  Set CurrentSheet = Worksheets(1)
  MultiplyColumnWidth CurrentSheet.Columns("A"), 2
  MultiplyColumnWidth CurrentSheet.Columns("B:C"), 2
  MultiplyColumnWidth CurrentSheet.Columns("D"), 2
  MultiplyColumnWidth CurrentSheet.Columns("E"), 2
  MultiplyColumnWidth CurrentSheet.Columns("F"), 3.5
  Set CurrentSheet = Nothing

End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.