Make Dropdown List Wider on Merged Cells

[nyrangers1994]

I found a code to make a data validation dropdown list wider temporarily while the column is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = "15" Then
Target.Columns.ColumnWidth = 20
Else
Columns(15).ColumnWidth = 8.43
End If
End Sub


The issue is that the cell I'm trying to expand is actually a merged cell between columns 15 and 16 (Columns O and P). How do I make it work when both columns are selected?

Thanks for your help.

 

[Andrew Poulsom]

Welcome to MrExcel.

How about?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 15 And Target.Count = 2 Then
        Target.EntireColumn.ColumnWidth = 20
    Else
        Columns(15).Resize(, 2).ColumnWidth = 8.43
    End If
End Sub

 

[nyrangers1994]

Beautiful. It worked. Thanks!

 

[JP_newbie]

Hi,

I have a similar problem and wondered if there is a solution?
The prolem is that I have multiply columns (O to AZ) that have verticle text choosen from a Validation List (row height is 150 & column width is 6) so the drop down list is very small and difficult to read the contents.
Can the code be manipulated to expand the width to say 30 when selecting a value and then drop back to 6 once the value has been choosen for any column that has data validation lists?


I found the following code but don't know how to merge them together, if at all possible?

HELP please
The following code will change the zoom setting to 120% when any cell with a data validation list is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lZoom As Long Dim lZoomDV As Long Dim lDVType As Long lZoom = 100 lZoomDV = 120 lDVType = 0 Application.EnableEvents = False On Error Resume Next lDVType = Target.Validation.Type On Error GoTo errHandler If lDVType <> 3 Then With ActiveWindow If .Zoom <> lZoom Then .Zoom = lZoom End If End With Else With ActiveWindow If .Zoom <> lZoomDV Then .Zoom = lZoomDV End If End With End IfexitHandler: Application.EnableEvents = True Exit SuberrHandler: GoTo exitHandlerEnd Sub
</PRE>
</PRE>


</PRE>