Prev: how do I convert a spreadsheet to landscape format?
Next: too many columns for screen can't see all
From: Joe M. on 10 May 2010 15:57
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
From: Chip Pearson on 10 May 2010 16:08
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.
<JoeM(a)discussions.microsoft.com> 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
From: Tom Hutchins on 10 May 2010 16:11
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
From: FSt1 on 10 May 2010 16:14
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
From: Joe M. on 11 May 2010 07:42
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.
> <JoeM(a)discussions.microsoft.com> 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
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: how do I convert a spreadsheet to landscape format?
Next: too many columns for screen can't see all