Annoying problem with viewing validation drop down box

Howdy Robert, yes indeed, here's an example, since you didn't provide ranges. Ranges c7 & f1 have drop down boxes and column H holds your list range (and is sized appropriately).

Since the validation drop downs take the form shape and dynamically create and destroy themselves, if you haven't created other form dropdowns in your worksheet, every one of 'em will be "drop down 1."

Accordingly, place the following macro in the appropriate worksheet class module:<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myShp As Shape, Drp As Single
On Error Resume Next
'cells holding drop downs
If Intersect(Target, [f1,c7]) Is Nothing Then Exit Sub
If Target.Validation.Type = xlValidateList Then
Set myShp = ActiveSheet.Shapes("Drop Down 1")
Drp = myShp.Width - Target.Width
'Column holding list, sized appropriately
myShp.Width = [h:h].Width
myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2
End If
Set myShp = Nothing
End Sub</pre>

To access your worksheet module, right click on the sheet tab, and left-click view code. Now paste the procedure in the now opened module.

Hope this helps. Happy Holidays eh!

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-12-21 20:49

Thanks for the reply Nat
Thanks for the reply to the query I sent to the board . I attempted the procedure as you described it but had no success the test sheet I set up were as follows, range my drop down are in F1:F7 and the columns containing the data was H. I am running excel 97 (due to the my employer this can not be changed) is there some thing I have missed ???
Regards
Robert

Howdy Robert, I wrote this and tested it in xl 2000, but I will have a look in '97, I believe it will work.

Did you make sure to get the code in the correct worksheet module as described above?

Change [f1,c7] to [f1:f7]

Do you have other form drop downs in your worksheet? Depending on the order you created them, you may need to change the name to 2, 3, etc...

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-12-21 17:14

Howdy Robert, do you mind my asking what you think of Bazza?

I did test this in Excel '97. It turns out that I'm not smart enough to comment on the vertical aspect of the click button (there's nothing in the code which should prompt it to shift (yes-no?)). But otherwise, this is a fully functional approach:<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myShp As Shape, Drp As Single
On Error Resume Next
'cells holding drop downs
If Intersect(Target, [f1:f7]) Is Nothing Then Exit Sub
If Target.Validation.Type = xlValidateList Then
Set myShp = ActiveSheet.Shapes("Drop Down 1")
Drp = myShp.Width - Target.Width
'Column holding list, sized appropriately
myShp.Width = [h:h].Width
myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2
End If
Set myShp = Nothing
End Sub</pre>

Merry Christmas!

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-12-21 20:52

Thanks for the reply NateO
After a few changes I have my sheet operating now.
I can't say to much about Bazza as my son is a big Rangers fan, but if I was able to email you I would pass on a copy of the word doc containing Bazza's user setting for his computer running system

Windies Twa Thoosan’

The traditional “Start” button has been replaced with a more familiar term
Goan Yesel’ Bigman (and so on)

Thanks again for the help
Robert

Increasing drop down validation box problem

Hello!

I was trying to implement the code underneath posted by Nate Oliver on 2002-12-21 to widen the list. The problem started at the 7th line, it seems that it doesn't want to set my validation box to myshp. Any ideas? I use Excel 97.

Thanks a lot.

<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myShp As Shape, Drp As Single
On Error Resume Next
'cells holding drop downs
If Intersect(Target, [f1:f7]) Is Nothing Then Exit Sub
If Target.Validation.Type = xlValidateList Then
Set myShp = ActiveSheet.Shapes("Drop Down 1")
Drp = myShp.Width - Target.Width
'Column holding list, sized appropriately
myShp.Width = [h:h].Width
myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2
End If
Set myShp = Nothing
End Sub</pre>

Adrian