If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Resize columns wider for pick list
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 |
#3
|
|||
|
|||
Resize columns wider for pick list
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 |
#4
|
|||
|
|||
Resize columns wider for pick list
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 |
#5
|
|||
|
|||
Resize columns wider for pick list
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 . |
#6
|
|||
|
|||
Resize columns wider for pick list
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 . |
Thread Tools | |
Display Modes | |
|
|