none
Validation List Dropdown Event RRS feed

  • Question

  • the following code will allow me to temporarily expand the width of a cell to display the full list of any items in my validation list.   but the cell expands as soon as the cursor is positioned into the cell, instead of expanding when the dropdown button is clicked to display the list.   Is there a way to code this, so the width only expands when the dropdown button is clicked?   Also how can I code it so that the cell is automatically collapsed to it's default site when the dropdown list is closed.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Column = 28 Then
            Application.EnableEvents = False
            Target.ColumnWidth = 30
            Application.EnableEvents = True
        Else
            Application.EnableEvents = False
            Target.ColumnWidth = 8.43
            Application.EnableEvents = True
        End If

    Wednesday, April 11, 2012 10:50 PM

Answers

  • Excel has no event which will do the following   "width only expands when the dropdown button is clicked? "

    If you need in anyway you have to insert dropdown for each row which is not required for the case.

    Mr Isabell has given a nice solution and you can easily folllow that.

    • Marked as answer by sarndt01 Friday, April 13, 2012 4:01 PM
    Thursday, April 12, 2012 5:55 AM
    Answerer

All replies

  • hi,
     
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 4 Then Target.Columns.AutoFit
    End Sub
     
    --
    isabelle
     
    Le 2012-04-11 18:50, sarndt01 a écrit :
    > the following code will allow me to temporarily expand the width of a cell to display the full list of any items in my validation list. but the cell expands as soon as the cursor is positioned into the cell, instead of expanding when the dropdown button is clicked to display the list. Is there a way to code this, so the width only expands when the dropdown button is clicked? Also how can I code it so that the cell is automatically collapsed to it's default site when the dropdown list is closed.
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > If Target.Column = 28 Then
    > Application.EnableEvents = False
    > Target.ColumnWidth = 30
    > Application.EnableEvents = True
    > Else
    > Application.EnableEvents = False
    > Target.ColumnWidth = 8.43
    > Application.EnableEvents = True
    > End If
    >
     
    Thursday, April 12, 2012 12:54 AM
  • Excel has no event which will do the following   "width only expands when the dropdown button is clicked? "

    If you need in anyway you have to insert dropdown for each row which is not required for the case.

    Mr Isabell has given a nice solution and you can easily folllow that.

    • Marked as answer by sarndt01 Friday, April 13, 2012 4:01 PM
    Thursday, April 12, 2012 5:55 AM
    Answerer
  • Thanks - isabelleV's solution doesn't work for what I was hoping to do, but I appreciate the response as well as the addition info from Learning and Learning.
    Thursday, April 12, 2012 8:35 PM