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 |
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 |
Width of drop down in data validation
Hi Tom!
Thanks for the quick response! Just what I was looking for! Thank you! Rene |
All times are GMT +1. The time now is 09:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com