Change width of data validation dropdown

Question

In my workbook, cells AR8:AS8 are merged and in the cell is a data validation dropdown list. The source of the list uses the formula =indirect(GG8) and this refers to lists in a different tab.

My problem is that when I click on the dropdown, the box isn't wide enough to show the full item.

Is there any way of changing this? I would prefer to NOT use VBA if possible..

I look forward to your responses :)

Answer 1

There is no possible way to achieve this W/O VBA. if you wish to use VBA solution, then please find code below. You have to paste this code to your Worksheet module, not Regular module, and adjust based on comments.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Cells.Count > 1 Then Exit Sub
   If Target.Address = "$H$1" Then 'adjust this range to your drop down list
       Target.Columns.ColumnWidth = 30 'adjust to your needs
   Else
       Columns(8).ColumnWidth = 8 'adjust column number to column with drop down values
   End If
End Sub

When dropdown is not selected:

Dropdown selected: