Previous Topic Next Topic

VB code for drop-list menu width

Solved/Closed
Issa - Aug 11, 2011 at 10:01 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 19, 2011 at 05:58 AM
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 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 11, 2011 at 10:39 AM
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
0
Answer 2 / 2
Issa
Aug 11, 2011 at 12:52 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 16, 2011 at 09:51 AM
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
0
issa
Aug 18, 2011 at 01:19 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 18, 2011 at 09:28 AM
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
0
Issa
Aug 19, 2011 at 03:30 AM
Dear Trowa,

I think it solved.

Thank you very very much to all your kind assistance.

Best regards,
Issa
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 19, 2011 at 05:58 AM
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.
0

Similar discussions

lava hard reset & factory reset
AVAY -
Har -

3 responses
How to boot proline laptop
REDX -
Thulisile -

3 responses
Proline w763s BIOS Resetting
Neo -
carminabigue -

1 response
No device output installed on ProLine notebook
BossoPcWorkx -
HelpiOS -

1 response
Re:Lava A16 mobile restore factory settings code
DEEPUKUMAR -
Krunal -

11 responses