Resize columns wider for pick list

I have some code that widens certain colums when clicked upon so I can see
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long

myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)

If Target.Count 1 Then Exit Sub

For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr

End Sub

You cannot change the width of individual cells. All the cells in a
column must have the same width. Similarly, all cells in a row must
have the same height.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Mon, 10 May 2010 12:57:01 -0700, Joe M.
wrote:

I have some code that widens certain colums when clicked upon so I can see
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long

myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)

If Target.Count 1 Then Exit Sub

For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr

End Sub

A column-width setting applies to the entire column. You can't make some
cells wider or narrower than other cells in the same column. You can merge
adjacent cells to simulate a wider column, but there are drawbacks to merged
cells. If more than one of the cells has data before merging, only the first
(topmost, leftmost) cell will retain its data after merging. Merged cells
also interfere with some features (like sorting).

Hope this helps,

Hutch

"Joe M." wrote:

I have some code that widens certain colums when clicked upon so I can see
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long

myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)

If Target.Count 1 Then Exit Sub

For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr

End Sub

hi
when adjusting column widths and row heighths, its an all or nothing deal.
you cann't adjust part of a column to different widths than the rest of the
column. same for row heighths. at times it may seem that you can but this may
be to the illusion of merged cells.

regards
FSt1



"Joe M." wrote:

I have some code that widens certain colums when clicked upon so I can see
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long

myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)

If Target.Count 1 Then Exit Sub

For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr

End Sub

I understand that certain cells cannot be widened, that only the entire
column can be widened. However, I don't want the column to be widened when
clicking anywhere in the column. I would like the column to be widened only
upon clicking of certain cells or a range of vertical cells within the column
to be widened. Can this be done?

Thanks,
Joe M.

"Chip Pearson" wrote:

You cannot change the width of individual cells. All the cells in a
column must have the same width. Similarly, all cells in a row must
have the same height.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Mon, 10 May 2010 12:57:01 -0700, Joe M.
wrote:

I have some code that widens certain colums when clicked upon so I can see
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long

myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)

If Target.Count 1 Then Exit Sub

For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr

End Sub
.

Using event code you can do this.

See Debra Dalgleish's site for code.

http://www.contextures.on.ca/xlDataVal08.html#Wider


Gord Dibben MS Excel MVP

On Tue, 11 May 2010 04:42:01 -0700, Joe M.
wrote:

I understand that certain cells cannot be widened, that only the entire
column can be widened. However, I don't want the column to be widened when
clicking anywhere in the column. I would like the column to be widened only
upon clicking of certain cells or a range of vertical cells within the column
to be widened. Can this be done?

Thanks,
Joe M.

"Chip Pearson" wrote:

You cannot change the width of individual cells. All the cells in a
column must have the same width. Similarly, all cells in a row must
have the same height.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Mon, 10 May 2010 12:57:01 -0700, Joe M.
wrote:

I have some code that widens certain colums when clicked upon so I can see
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long

myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)

If Target.Count 1 Then Exit Sub

For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr

End Sub
.