VB code for drop-list menu width

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

If any one can help me.

In one excel sheet I made list of names and defined that list as "Names", then in another excel sheet in column "E" I made drop-list menues through (Data/Validation/list) then in the source option I wrote "=Names".

Now I need 2 things:

1. The width of that drop list is too wide. (i.e the width of column E is 7 but the drop-list menu maybe 22. So how can I control the width? Is there any settings or through VBA.

2. If I filled in column E any value by mistake not included in the list, it will be accepted.
So how can I restrict the entry of data to be only one of the list values.

Thank you in advance for any kind assistance.
Issa

2 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Issa,

1. Uhm adjust the width of column E. If you wish to autofit the column then record a macro. If you need help with that let me know.

2. This should not be possible. Did you made sure you applied your list to the entire column of E and not just a single cell like E1?

Best regards,
Trowa
Dear Trowa ,

Thanks to your prompt reply.

1. Yes I need help for the VBA code.

2. Yes it applied, to E1, E2, E3, etc this is what I mean.

Thank you.
Issa
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Issa,

Sorry this reply took a little longer. I have some free days this time of the year.

1. Right-click on the sheets name > view code.
The Visual Basic window will open. Paste this code in the big white field:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1").EntireColumn) Is Nothing Then Exit Sub
Columns("E:E").EntireColumn.AutoFit
End Sub
Any changes made in column E will autofit the column.

2. That was basically my only guess. You could upload your file using a filesharing site, you I can take a look to see if I can find something else.

Best regards,
Trowa
Thanks again Trowa,

But what I need is the width of the drop list menu not the column it self, so when I press the arrow to select from the list it droped down but it's too wide more than the width of the words in that list.

Thank you again for your interest.
Issa
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Issa,

The width of the drop down list is linked to the width of the column. So the only thing I can think of is to autofit the column in which you pull your drop down list data from and apply that width to the column of the drop down list.

Does this sound as a solution to you?

Best regards,
Trowa
Dear Trowa,

I think it solved.

Thank you very very much to all your kind assistance.

Best regards,
Issa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Two things.
1. You "think" ? Either it is solved or it is not. Why think part ?
2. It would have been nicer had you shared your solution too

One more thing, thanks for at least coming back and acknowledging help that Trowa provided. Not many people are courteous enough to do that.