Change width of a dropdown list

**Masun** · 11-14-2014, 03:40 AM

Hi all,

I am trying to change the width of one specific column where i have a drop down list and i want all the text to be readable when making the choice in the list.

I have tried the solution by contexture (http://www.contextures.com/xlDataVal08.html#Wider)

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

I want the width of the column to change when i click the arrow of the drop down list so when people are about to make the choice in the list it appears wider and therefor increase the readability. Not just when clicking a cell in that column.

Is this possible in some way? Hope someone could help me out!

/Masun

**:) Sixthsense :)** · 11-14-2014, 03:51 AM

May be check whether this suggestion is useful.

Autofit the DV List Source Data Column and get the Column width from it. On selection event set that column width to that column and reset it to the default column with after on change

**Masun** · 11-14-2014, 04:03 AM

Thanks for the suggestion. Maybe thats a good idea, you are very often spot on, the trouble here, for me that is, i am not able to produce a vba code of your suggestion cause i really dont have that knowledge. But its important for me that the column, and therefor the drop down list, resizes when clicking on the drop down list arrow, not just when clicking on the cell. I dont understand if it will end up in that way with your suggestion?

**:) Sixthsense :)** · 11-14-2014, 04:09 AM

I will provide the vba if you attach a sample workbook with DV List.

**Masun** · 11-14-2014, 04:59 AM

That sounds fair. ;-)

Seriously...thank you very much!

**:) Sixthsense :)** · 11-14-2014, 06:07 AM

I tried to make it but not sure how you like it

Dim sgWidth As Single, sgActive As Single, rSource As Range, rTarget As Range

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 3 Then Exit Sub
Target.ColumnWidth = sgActive
If Not rSource Is Nothing Then rSource.ColumnWidth = sgWidth
Set rSource = Nothing

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not rSource Is Nothing Then rSource.ColumnWidth = sgWidth
If Not rTarget Is Nothing Then rTarget.ColumnWidth = sgActive

If Target.Column <> 3 Then Exit Sub

With Target
    On Error Resume Next
        v = .Validation.Type
        If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    If .Validation.Type = xlValidateList Then
        On Error Resume Next
            Set rSource = Excel.Range(.Validation.Formula1)
        On Error GoTo 0
       
        If Not rSource Is Nothing Then
            rSource.WrapText = False
            sgActive = .ColumnWidth
            sgWidth = rSource.ColumnWidth
            rSource.EntireColumn.AutoFit
            Target.ColumnWidth = rSource.ColumnWidth
            Set rTarget = Target
        End If
    End If
End With

End Sub

**Masun** · 11-14-2014, 09:05 AM

Hi,

I am getting some compilation error. I guess it has to do with an other drop down list related code im using from contexture. Could you please take a look?

The other macro makes it possible to choose multiple items from the drop down list. In this case colum nr 6...

Change width of a dropdown list

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Change width of a dropdown list

Re: Change width of a dropdown list

Re: Change width of a dropdown list

Re: Change width of a dropdown list

Re: Change width of a dropdown list

Re: Change width of a dropdown list

Re: Change width of a dropdown list

Thread Information

Users Browsing this Thread

Similar Threads

Change Language in SAP through dropdown list - VBA

[SOLVED] How do I change the width of the data validation list box?

Dropdown List - Change Display

Dropdown list width changing code

set dropdown list width in validation

Bookmarks

Bookmarks

Posting Permissions