Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making data validation drop down list wider
My VB is very weak (trying to learn). I've followed the instructions on
Debra Dalgleish's site for adding code to the worksheet and it works fine for a single column but I don't know how to change the code when I have multiple columns that need to be temporarily widened so that you can see the entire selection in the drop down list. My worksheet code looks like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 13 Then Target.Columns.ColumnWidth = 18 Else Columns(13).ColumnWidth = 15.38 End If End Sub 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' If Target.Count 1 Then Exit Sub ' If Target.Column = 3 Then ' Target.Columns.ColumnWidth = 28 ' Else ' Columns(3).ColumnWidth = 21 ' End If 'End Sub I have 10+ columns of drop downs that are too wide to view in the spreadsheet (I have the columns formatted to wordwrap so that the sheet isn't too wide to be usable when completed). How do I need to modify the code for the extra columns? Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making data validation drop down list wider
Maybe something like:
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myRng As Range Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn If Target.Count 1 Then Exit Sub 'reset all those column widths myRng.ColumnWidth = 15.38 If Intersect(Target, myRng) Is Nothing Then 'do nothing Else Target.EntireColumn.ColumnWidth = 18 End If End Sub Include a cell from any column you need in this line: Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn Steve E wrote: My VB is very weak (trying to learn). I've followed the instructions on Debra Dalgleish's site for adding code to the worksheet and it works fine for a single column but I don't know how to change the code when I have multiple columns that need to be temporarily widened so that you can see the entire selection in the drop down list. My worksheet code looks like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 13 Then Target.Columns.ColumnWidth = 18 Else Columns(13).ColumnWidth = 15.38 End If End Sub 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' If Target.Count 1 Then Exit Sub ' If Target.Column = 3 Then ' Target.Columns.ColumnWidth = 28 ' Else ' Columns(3).ColumnWidth = 21 ' End If 'End Sub I have 10+ columns of drop downs that are too wide to view in the spreadsheet (I have the columns formatted to wordwrap so that the sheet isn't too wide to be usable when completed). How do I need to modify the code for the extra columns? Thanks, -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making data validation drop down list wider
Dave,
Thanks for taking this on. If I understand correctly, this changes any column width to 18, right? It looks like this changes the width to 18 on entry and then resets to 15.38 -- what if I want to reset to different widths based on the column? My commented out section of code is an example of one of the other columns where the 'normal' width is 21 but I need it to widen to 28 so that you can read all of the input selections in the drop down list. These widths vary from column to column based on the maximum text width and my formatting limitations (on overall width of the sheet). Clear as mud? "Dave Peterson" wrote: Maybe something like: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myRng As Range Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn If Target.Count 1 Then Exit Sub 'reset all those column widths myRng.ColumnWidth = 15.38 If Intersect(Target, myRng) Is Nothing Then 'do nothing Else Target.EntireColumn.ColumnWidth = 18 End If End Sub Include a cell from any column you need in this line: Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn Steve E wrote: My VB is very weak (trying to learn). I've followed the instructions on Debra Dalgleish's site for adding code to the worksheet and it works fine for a single column but I don't know how to change the code when I have multiple columns that need to be temporarily widened so that you can see the entire selection in the drop down list. My worksheet code looks like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 13 Then Target.Columns.ColumnWidth = 18 Else Columns(13).ColumnWidth = 15.38 End If End Sub 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' If Target.Count 1 Then Exit Sub ' If Target.Column = 3 Then ' Target.Columns.ColumnWidth = 28 ' Else ' Columns(3).ColumnWidth = 21 ' End If 'End Sub I have 10+ columns of drop downs that are too wide to view in the spreadsheet (I have the columns formatted to wordwrap so that the sheet isn't too wide to be usable when completed). How do I need to modify the code for the extra columns? Thanks, -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making data validation drop down list wider
How about:
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", "c", "e") myWidthSelected = Array(3, 18.42, 33) myWidthNormal = Array(1, 10, 20) 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 Personally, I wouldn't do this kind of thing--I'd just widen the column to begin with. I hate that I'd lose the edit|Undo and Edit|Redo stack. Steve E wrote: Dave, Thanks for taking this on. If I understand correctly, this changes any column width to 18, right? It looks like this changes the width to 18 on entry and then resets to 15.38 -- what if I want to reset to different widths based on the column? My commented out section of code is an example of one of the other columns where the 'normal' width is 21 but I need it to widen to 28 so that you can read all of the input selections in the drop down list. These widths vary from column to column based on the maximum text width and my formatting limitations (on overall width of the sheet). Clear as mud? "Dave Peterson" wrote: Maybe something like: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myRng As Range Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn If Target.Count 1 Then Exit Sub 'reset all those column widths myRng.ColumnWidth = 15.38 If Intersect(Target, myRng) Is Nothing Then 'do nothing Else Target.EntireColumn.ColumnWidth = 18 End If End Sub Include a cell from any column you need in this line: Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn Steve E wrote: My VB is very weak (trying to learn). I've followed the instructions on Debra Dalgleish's site for adding code to the worksheet and it works fine for a single column but I don't know how to change the code when I have multiple columns that need to be temporarily widened so that you can see the entire selection in the drop down list. My worksheet code looks like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 13 Then Target.Columns.ColumnWidth = 18 Else Columns(13).ColumnWidth = 15.38 End If End Sub 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' If Target.Count 1 Then Exit Sub ' If Target.Column = 3 Then ' Target.Columns.ColumnWidth = 28 ' Else ' Columns(3).ColumnWidth = 21 ' End If 'End Sub I have 10+ columns of drop downs that are too wide to view in the spreadsheet (I have the columns formatted to wordwrap so that the sheet isn't too wide to be usable when completed). How do I need to modify the code for the extra columns? Thanks, -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making data validation drop down list wider
Dave,
Thanks for all your help. This does exactly what I was trying to accomplish. I see that you wouldn't do this but other than the loss of the undo | redo functions (which I don't understand why they won't work now, by the way) what other "cons" are there to this type of solution. I have 7 columns that are quite wide when not word wrapped and I'd never be able to fit them into my legal landscaped format width limitation if I couldn't do something like this. Interested in knowing what other issues this might cause as I don't have another 'top of mind' solution. Best, Steve "Dave Peterson" wrote: How about: 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", "c", "e") myWidthSelected = Array(3, 18.42, 33) myWidthNormal = Array(1, 10, 20) 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 Personally, I wouldn't do this kind of thing--I'd just widen the column to begin with. I hate that I'd lose the edit|Undo and Edit|Redo stack. Steve E wrote: Dave, Thanks for taking this on. If I understand correctly, this changes any column width to 18, right? It looks like this changes the width to 18 on entry and then resets to 15.38 -- what if I want to reset to different widths based on the column? My commented out section of code is an example of one of the other columns where the 'normal' width is 21 but I need it to widen to 28 so that you can read all of the input selections in the drop down list. These widths vary from column to column based on the maximum text width and my formatting limitations (on overall width of the sheet). Clear as mud? "Dave Peterson" wrote: Maybe something like: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myRng As Range Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn If Target.Count 1 Then Exit Sub 'reset all those column widths myRng.ColumnWidth = 15.38 If Intersect(Target, myRng) Is Nothing Then 'do nothing Else Target.EntireColumn.ColumnWidth = 18 End If End Sub Include a cell from any column you need in this line: Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn Steve E wrote: My VB is very weak (trying to learn). I've followed the instructions on Debra Dalgleish's site for adding code to the worksheet and it works fine for a single column but I don't know how to change the code when I have multiple columns that need to be temporarily widened so that you can see the entire selection in the drop down list. My worksheet code looks like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 13 Then Target.Columns.ColumnWidth = 18 Else Columns(13).ColumnWidth = 15.38 End If End Sub 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' If Target.Count 1 Then Exit Sub ' If Target.Column = 3 Then ' Target.Columns.ColumnWidth = 28 ' Else ' Columns(3).ColumnWidth = 21 ' End If 'End Sub I have 10+ columns of drop downs that are too wide to view in the spreadsheet (I have the columns formatted to wordwrap so that the sheet isn't too wide to be usable when completed). How do I need to modify the code for the extra columns? Thanks, -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making data validation drop down list wider
If you type something into a cell and overwrite something important, you can use
Edit|Undo to get it back to the way it used to be. If you delete a few rows in error, you can edit|undo to get things back. I find that I rely on Edit|Undo more than I should <bg. I should be more careful. That's the biggest problem I have with stuff like this. The other potential problem is that if the user doesn't enable macros or just turns off event handling, then this won't work either. But that's not a real big problem (to me, anyway). Steve E wrote: Dave, Thanks for all your help. This does exactly what I was trying to accomplish. I see that you wouldn't do this but other than the loss of the undo | redo functions (which I don't understand why they won't work now, by the way) what other "cons" are there to this type of solution. I have 7 columns that are quite wide when not word wrapped and I'd never be able to fit them into my legal landscaped format width limitation if I couldn't do something like this. Interested in knowing what other issues this might cause as I don't have another 'top of mind' solution. Best, Steve "Dave Peterson" wrote: How about: 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", "c", "e") myWidthSelected = Array(3, 18.42, 33) myWidthNormal = Array(1, 10, 20) 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 Personally, I wouldn't do this kind of thing--I'd just widen the column to begin with. I hate that I'd lose the edit|Undo and Edit|Redo stack. Steve E wrote: Dave, Thanks for taking this on. If I understand correctly, this changes any column width to 18, right? It looks like this changes the width to 18 on entry and then resets to 15.38 -- what if I want to reset to different widths based on the column? My commented out section of code is an example of one of the other columns where the 'normal' width is 21 but I need it to widen to 28 so that you can read all of the input selections in the drop down list. These widths vary from column to column based on the maximum text width and my formatting limitations (on overall width of the sheet). Clear as mud? "Dave Peterson" wrote: Maybe something like: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myRng As Range Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn If Target.Count 1 Then Exit Sub 'reset all those column widths myRng.ColumnWidth = 15.38 If Intersect(Target, myRng) Is Nothing Then 'do nothing Else Target.EntireColumn.ColumnWidth = 18 End If End Sub Include a cell from any column you need in this line: Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn Steve E wrote: My VB is very weak (trying to learn). I've followed the instructions on Debra Dalgleish's site for adding code to the worksheet and it works fine for a single column but I don't know how to change the code when I have multiple columns that need to be temporarily widened so that you can see the entire selection in the drop down list. My worksheet code looks like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 13 Then Target.Columns.ColumnWidth = 18 Else Columns(13).ColumnWidth = 15.38 End If End Sub 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' If Target.Count 1 Then Exit Sub ' If Target.Column = 3 Then ' Target.Columns.ColumnWidth = 28 ' Else ' Columns(3).ColumnWidth = 21 ' End If 'End Sub I have 10+ columns of drop downs that are too wide to view in the spreadsheet (I have the columns formatted to wordwrap so that the sheet isn't too wide to be usable when completed). How do I need to modify the code for the extra columns? Thanks, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation | Excel Worksheet Functions | |||
Data Validation in Excel - drop down list font size | Excel Discussion (Misc queries) | |||
Dependent List (via Data Validation) Error | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Expanding Data validation from List | Excel Discussion (Misc queries) |