vba: protect width of columns

shodan

Active Member
Joined
Jul 6, 2005
Messages
486
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Active Member
Joined
Jul 6, 2005
Messages
486
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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?
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top