Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Width of drop down in data validation
Hi experts!
I found this great code to temporarily change the width of a column to overcome problems with the width of the drop down field (for data validation): Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 4 Then Target.Columns.ColumnWidth = 20 Else Columns(4).ColumnWidth = 5 End If End Sub Checkout: http://www.contextures.com/xlDataVal08.html PROBLEM: I want to make this code applicable to an entire range of columns (columns 17-85) and not only to a single one (in this case column 4). Is there any way to tweak the code? I can change the "If Target.Column =4 Then" to "If TargetColumn 17 And <85" but then the code after "Else" will not work. Thanks for your help. Rene |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Width of drop down in data validation
Use a public variable at the top of the module to "remember" what column you
last increased the width of. public col as Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) if not col is nothing then col.EntireColumn.ColumnWidth = 5 set col = nothing end if If Target.Count 1 Then Exit Sub If Target.Column = 17 and Target.Column <= 85 Then Target.Columns.ColumnWidth = 20 set col = Target.EntireColumn end if End Sub -- Regards, Tom Ogilvy "Rene" wrote: Hi experts! I found this great code to temporarily change the width of a column to overcome problems with the width of the drop down field (for data validation): Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 4 Then Target.Columns.ColumnWidth = 20 Else Columns(4).ColumnWidth = 5 End If End Sub Checkout: http://www.contextures.com/xlDataVal08.html PROBLEM: I want to make this code applicable to an entire range of columns (columns 17-85) and not only to a single one (in this case column 4). Is there any way to tweak the code? I can change the "If Target.Column =4 Then" to "If TargetColumn 17 And <85" but then the code after "Else" will not work. Thanks for your help. Rene |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Width of drop down in data validation
Hi Tom!
Thanks for the quick response! Just what I was looking for! Thank you! Rene |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation drop-down width, with named range source (XL03 and | Excel Worksheet Functions | |||
Data Validation Drop Down Menu Width | Excel Discussion (Misc queries) | |||
Drop down list box width - data validation | Excel Discussion (Misc queries) | |||
Data Validation - width of drop down box | Excel Discussion (Misc queries) | |||
A new twist to the validation drop-down width question. | Excel Discussion (Misc queries) |