making dropdown list wider without making cell wider ???

i have a spreadsheet that has a small cells with drop down list attached to it. the choices on the dropdown list are bigger than the cell but i have the cells either side to make up for the room. can i get the list wider without making the cell wider.


i cant use Macro's because of my works limitations. please let me know if you need a sample and i will post it.

If you can't use macros, then you're stuck. Contextures.com has good materials for making the dropdown wider or larger, but they all involve VBA

http://www.contextures.com/xlDataVal08.html#wider

i didnt think it could be done without macros... my work place doesnt allow them and because of this i have never bothered learning it.

ok. i have managed to talk my IT dept about letting me use macros.
i have used the contexture website and used the code provided, i changed column 4 to read column 5 and it worked ok. but i need the same scipt for columns 5,9,13,17,21,25 and 29.

as i dont now how to use macros at all, could someone please post the code needed.


thanks

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

hi... the default width is 1.43 which i changed your code to as per request. i put the macro in and it worked ok on the cell selected (they opened bigger as expected), but didnt reduce down to 1.43 afterwards... on my code from contexture it stated to close column 5 back to 1.43 when the cell isnt selected anymore (well i think thats what it means) but on the code you posted it says "Columns(Target.Column)"... do i need to change this as well?????

i have relooked at this code. the cell grows as expected (Case 5, 9, 13, 17, 21, 25) but when i click a cell that isnt stated (Case 5, 9, 13, 17, 21, 25) afterwards, its this cells that shrinks to 1.43

please help

thanks but its still not want i need.. i dont want to change all the other cells sizes, just change back the one i selected in column 5, 9, 13, 17, 21, and 25.

here is my project

as you can see on the off duty sheet on the columns 5, 9, 13, 17, 21, and 25 the reason why i only want those to change

Hi npamcpp, I wonder if you can help me?

I am looking to use the above code to temporarily make my drop down list wider (maybe 7.0) and I want the columns to return to their original size of 0.40 even after I select something from the list.

I only want it to apply to a table range from Col. BG thru Col. BTK and rows 6 thru 215. I do not want any other cells to resize in my workbook.

Can you help me?

cheers.. that looks exactly like i want

I know this thread has been dead for a number of years but it was exactly what I was looking for. My only question, is there a tweak that can be made so that the column only performs the above action (widening on click) when the user clicks the dropdown list cell to make their selection but does not perform the action if clicking in any of the cells below that dropdown cell? Thanks in advance.