Temporarily increase cell size of drop down validation list

[writyl0730]

Morning,

I'm creating a 1 page document that each column (columns A - I) is a set width. Most of the worksheet is filled with drop down lists that are wordy. Due to the size of each column, you can't read the each item in the drop down to the cell size.

I'm looking for a way to increase the cell size to view the full item when the drop down is activated only. Example Column A is set to 24 width and will not show the full description of each item in the drop down box - is there way when activating the drop down to temporarily change the width to say 60 or something.

Is there a code to use to do this?

 

[Rick Rothstein]

Morning,

I'm creating a 1 page document that each column (columns A - I) is a set width. Most of the worksheet is filled with drop down lists that are wordy. Due to the size of each column, you can't read the each item in the drop down to the cell size.

I'm looking for a way to increase the cell size to view the full item when the drop down is activated only. Example Column A is set to 24 width and will not show the full description of each item in the drop down box - is there way when activating the drop down to temporarily change the width to say 60 or something.

Is there a code to use to do this?

I think you will need to use a sheet level event procedure to do what you want. In order to write it for you, we would need to know the column letters that have drop downs that will need this feature along with the "normal" column width and desired expanded column width for each of those columns.

 

[writyl0730]

Thanks Rick!

Columns A-D. Column width is 24; desired width while drop down is activated would 60.
Column E. Column width is 40.14; desired width while drop down is activated would 80.
Columns F-I. Column width is 24; desired width while drop down is activated would 60.

Appreciate your assistance!

 

[Rick Rothstein]

Thanks Rick!

Columns A-D. Column width is 24; desired width while drop down is activated would 60.
Column E. Column width is 40.14; desired width while drop down is activated would 80.
Columns F-I. Column width is 24; desired width while drop down is activated would 60.

I don't know of a way to react to the drop down being activated, so the following code widens the column to your desired width whenever a cell with validation is selected in one of the first nine columns. Give it a try and see if you can use it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim IsDropDown As Boolean
  If Target.CountLarge = 1 Then
    Range("A:D,F:I").ColumnWidth = 24
    Range("E:E").ColumnWidth = 40.14
    If Target.Column <= 9 Then
      On Error Resume Next
        IsDropDown = Left(Target.Validation.Formula1, 1) = "="
      On Error GoTo 0
      If IsDropDown Then
        Select Case Target.Column
          Case 1 To 4, 6 To 9: Target.ColumnWidth = 60
          Case 5:              Target.ColumnWidth = 80
        End Select
      End If
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[writyl0730]

Rick, I did get it to work and it makes sense of how you have it!

If I wanted to protect the worksheet, outside of the drop down cells, I'm assuming the code would error out. Is there anything in the code that would need to be updated? I know with macro's you can unprotect/reprotect at the beginning/end of the macro. For this I'm unsure.

Thank you again!

 

[writyl0730]

Rick, any thoughts on the protected worksheet with the code? Really appreciate it!