Trying to set the column width of range of cells ... problem

[bisel]

Hello All,

I am stumped. I have a range of cells in my sheet with some values. I want to use these value to set the column width of the column for that cell. I have the following code and cannot get it to work. Hoping someone can steer me in the right direction ...

Private Sub worksheet_activate()
Dim c As Range
For Each c In Range("sht1_setcolwidths")
    c.EntireColumn.ColumnWidth = c.Offset(1, 0).Value
Next c
End Sub

I have tried various syntax and always get this message ...

Any help appreciated.

Thanks,

Steve

 

[offthelip]

It means you are trying to set the column width to something which is not a number . Are you using the offset correctly?? you are offsettting to the the row below which doesn't make sense. If you are trying to offset to the column to right then that line should be:

    c.EntireColumn.ColumnWidth = c.Offset(0, 1).Value

 

[rollis13]

Well, must say that your macro does exactly what it's supposed to do. It's written to read the number under the cells in the range Named "sht1_setcolwidths" and accordingly set the columnwidth.
So, if you Named the range A1 to F1 "sht1_setcolwidths" it will read the cells from A2 through to F2 and set the relative columnwidth to the found value (must be a number from 0 to 255, no text) in them.

 

[bisel]

It means you are trying to set the column width to something which is not a number . Are you using the offset correctly?? you are offsettting to the the row below which doesn't make sense. If you are trying to offset to the column to right then that line should be:

    c.EntireColumn.ColumnWidth = c.Offset(0, 1).Value

Here are cells with values ... normally these rows are hidden ...

The yellow highlighted cells is the range, "sht1_setcolwidths"
Using the offset (0, 1) selects the cell immediately below the active cell and is the column width ... yes it is numeric not text
The offset(0, 2) is the value of v, h used for cell visibility

Strangely, if state On Error Resume Next prior to the above code, the error message is not produced and the macro correctly sets the column widths. I have to reset the On Error by using On Error GoTo 0. I am really stumped here.

I have tried various things, such as ...

c.ColumnWidth = c.Offset(1, 0).Value ... displays error
c.EntireColumn.ColumnWidth = c.Offset(1, 0).Value ... displays error

I created a blank workbook and created a sub. Works fine in that new workbook. Very strange.

Any thoughts

Steve

 

[offthelip]

I created a blank workbook and created a sub. Works fine in that new workbook. Very strange.

That would imply it is something to do with how your named range is defined

 

[StephenCrump]

Is your worksheet protected perhaps?

 

[bisel]

Is your worksheet protected perhaps?

No. The workbook and worksheet are both unprotected.

Steve

 

[bisel]

That would imply it is something to do with how your named range is defined

The named range is simply that. A range of cells with a name. The only option is the scope and it is set to "workbook" ... which is the default when creating a named range.

 

[bisel]

Hello All,

I am stumped. I have a range of cells in my sheet with some values. I want to use these value to set the column width of the column for that cell. I have the following code and cannot get it to work. Hoping someone can steer me in the right direction ...

Private Sub worksheet_activate()
Dim c As Range
For Each c In Range("sht1_setcolwidths")
    c.EntireColumn.ColumnWidth = c.Offset(1, 0).Value
Next c
End Sub

I have tried various syntax and always get this message ...

View attachment 53465


Any help appreciated.

Thanks,

Steve

I resolved the problem. One of the cells used to obtain the column width had a leading blank so it appeared like numeric but was in fact text. This caused the error. Everything fine now.

 

[rollis13]

Oh, I thought that this had been already said in posts 2# and #3.