vba: protect width of columns

[shodan]

Hi board,

I use an .xls file to convert it into a .prn file afterwards. The .prn file is used as a dataloader and the width of the columns may never change.

Therefore following question:

Can someone supply me a vba code that makes it impossible to change the widht of the columns. Or a code that fixes the width to the current width.

Regards,
shodan

 

[HalfAce]

Hello shodan,
Is protecting the sheet not an option?
The column widths can't be changed if the sheet's protected.

If not, then can the desired widths be hard coded? (How many columns are we talking about?)
Are they all the default width, - all their own widths, or . . . (?)

 

[shodan]

halface,

I tried the protect sheet option already, but I think that this also won't allow me to change the content of the cells and that should still be possible.

right now, I'm talking about 3 columns, but it can be also be more columns. their width is indeed variable.

Another option would be to use a CSV file in stead of a prn file, but the program than wants the text between quotes like "text"

This would even be a better solution. A line of code that makes sure that all non blank cells starts and ends with " when saving the file.

The range in rows can be determined by column A since this column never hold blanks, and the range rows can also be determined by row1 since there is never a blank in row 1

Regards,
shodan

 

[HalfAce]

So am I correct in thinking you just want to replace every cell's value with its same value
between quotes? - (In what, just sheet1?)
If so you can give something like this a try. - Code goes into the ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim c As Range
'(Replace 'Sheet1' with the sheet's real name)
With Sheets("Sheet1")
  For Each c In .UsedRange
    If Not IsEmpty(c) Then c.Value = Chr(34) & c.Value & Chr(34)
  Next c
End With
End Sub

Is that what you're looking to do?

 

[shodan]

halface,

that's it! thanks for that

shodan

 

[HalfAce]

Most welcome. Glad it helped.