interactive column width

[hawki]

i need the code for setting the column width. the code needs to "pause" for
allowing a number to be entered and then resume.

Any suggestions?

lhawk

 

[JLGWhiz]

I am not sure what you mean by "pause while a number is entered" but here is
the code for setting the column width:

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

If you then want to enter something into a cell in the column, you could use
an inputbox.

myNum = InputBox("Enter a number", "Entry")
Range("A2") = CLng(myNum)

If the entry must be a number then the number you enter in the inputbox will
be stored in the variable myNum as a string value, so when you put it in the
cell, you would use the CLng function to coerce the variable value to an long
integer data type.

 

[JLGWhiz]

You could also use:

Columns("A").Autofit

Then, whatever length the entry, up to something like 256 characters, the
column would automatically adjust to the entry length.

 

[Erin Searfoss]

The VBA code to show the column width dialog box and change the width of the
active column(s) accordingly is

Sub ColWidth()

Application.Dialogs(xlDialogColumnWidth).Show

End Sub

 

[hawki]

i would like to press a short cut key, the column width window to appear,
enter the desired column width while seeing the current setting, and press
enter to complete the task.

hawk

 

[hawki]

the "Application.Dialogs(xlDialogColumnWidth).Show" is what i was looking for.

thanks to the both of you!

Lhawk

 

[Rick Rothstein \(MVP - VB\)]

While not a shortcut per se, you can use the keyboard to call up the Column
Width dialog box by holding down the Alt key while keying in "ocw". Since
the dialog box displays the old column width when it first comes up, you can
remember the letters by thinking "old column width" where "ocw" come from
the first letter of each word.

Rick