Add dropdown on an event.
 

I have some code which when a user double clicks on certain cells will
add a dropdown. The code is supposed to add the dropdown so its
dimensions match that of the target cell. While the actual dropdowns
have the correct length and width, and are placed correctly
horizontally, they are always off Vertically. Any ideas?



Sub adddropdown(Target As Range, typer As Integer)
Dim ddbox As DropDown
Dim vaproducts As Variant
Dim i As Integer
Dim skilllevel As Variant
Dim shift As Variant
shift = Array("1-Night", "2-Day", "3-Evening")
skilllevel = Array("RN", "LVN", "NA", "Other", "Traveler")
vaproducts = Array("Maternity Leave", "Workman's Comp", "Family
Leave", "Medical Leave", "Other")
c = Target.Address

'I HAVE CHECKED THE TARGET ADDRESS AND IT IS THE CORRECT CELL
'BUT THIS NEXT CODE WHERE IT ADDS THE DROPDOWN PUTS IT IN
'THE WRONG SPOT

With Target
Set ddbox = ActiveSheet.DropDowns.Add (.Left, .Top, .Width, .Height)
End With

With ddbox
..OnAction = "Enterlet"

Select Case typer
Case 1
For i = LBound(vaproducts) To UBound(vaproducts)
.AddItem vaproducts(i)
Next i
Case 2
For i = LBound(skilllevel) To UBound(skilllevel)
.AddItem skilllevel(i)
Next i
Case 3
For i = LBound(shift) To UBound(shift)
.AddItem shift(i)
Next i

End Select
End With
End Sub

Add dropdown on an event.
 

Sub ABCD()
Dim rng As Range
Set rng = Range("B9")
adddropdown rng, 2
End Sub


Sub adddropdown(Target As Range, typer As Integer)
Dim ddbox As DropDown
Dim vaproducts As Variant
Dim i As Integer
Dim skilllevel As Variant
Dim shift As Variant
shift = Array("1-Night", "2-Day", "3-Evening")
skilllevel = Array("RN", "LVN", "NA", "Other", "Traveler")
vaproducts = Array("Maternity Leave", _
"Workman's Comp", "Family Leave", _
"Medical Leave", "Other")
c = Target.Address

'I HAVE CHECKED THE TARGET ADDRESS AND IT IS THE CORRECT CELL
'BUT THIS NEXT CODE WHERE IT ADDS THE DROPDOWN PUTS IT IN
'THE WRONG SPOT

With Target
Set ddbox = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)
End With

With ddbox
..OnAction = "Enterlet"

Select Case typer
Case 1
For i = LBound(vaproducts) To UBound(vaproducts)
.AddItem vaproducts(i)
Next i
Case 2
For i = LBound(skilllevel) To UBound(skilllevel)
.AddItem skilllevel(i)
Next i
Case 3
For i = LBound(shift) To UBound(shift)
.AddItem shift(i)
Next i

End Select
End With
End Sub

worked fine for me. It must be that your passing the wrong range into the
function.

--
Regards,
Tom Ogilvy

"Chip" wrote in message
oups.com...
I have some code which when a user double clicks on certain cells will
add a dropdown. The code is supposed to add the dropdown so its
dimensions match that of the target cell. While the actual dropdowns
have the correct length and width, and are placed correctly
horizontally, they are always off Vertically. Any ideas?



Sub adddropdown(Target As Range, typer As Integer)
Dim ddbox As DropDown
Dim vaproducts As Variant
Dim i As Integer
Dim skilllevel As Variant
Dim shift As Variant
shift = Array("1-Night", "2-Day", "3-Evening")
skilllevel = Array("RN", "LVN", "NA", "Other", "Traveler")
vaproducts = Array("Maternity Leave", "Workman's Comp", "Family
Leave", "Medical Leave", "Other")
c = Target.Address

'I HAVE CHECKED THE TARGET ADDRESS AND IT IS THE CORRECT CELL
'BUT THIS NEXT CODE WHERE IT ADDS THE DROPDOWN PUTS IT IN
'THE WRONG SPOT

With Target
Set ddbox = ActiveSheet.DropDowns.Add (.Left, .Top, .Width, .Height)
End With

With ddbox
.OnAction = "Enterlet"

Select Case typer
Case 1
For i = LBound(vaproducts) To UBound(vaproducts)
.AddItem vaproducts(i)
Next i
Case 2
For i = LBound(skilllevel) To UBound(skilllevel)
.AddItem skilllevel(i)
Next i
Case 3
For i = LBound(shift) To UBound(shift)
.AddItem shift(i)
Next i

End Select
End With
End Sub

Add dropdown on an event.
 

A long time ago, someone posted a similar problem--it really raised its ugly
head when the zoom factor wasn't at 100% (smaller, IIRC).

I wrote this macro that went into a loop and adjusted the location until it was
entirely in the cell. (Pretty ugly!)

Tom Ogilvy just set the position a second time and it worked fine. (DOH!).

maybe that would work for you:

With Target
Set ddbox = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)
ddbox.Top = .Top
ddbox.Left = .Left
ddbox.Width = .Width
ddbox.Height = .Height
End With





Chip wrote:

I have some code which when a user double clicks on certain cells will
add a dropdown. The code is supposed to add the dropdown so its
dimensions match that of the target cell. While the actual dropdowns
have the correct length and width, and are placed correctly
horizontally, they are always off Vertically. Any ideas?

Sub adddropdown(Target As Range, typer As Integer)
Dim ddbox As DropDown
Dim vaproducts As Variant
Dim i As Integer
Dim skilllevel As Variant
Dim shift As Variant
shift = Array("1-Night", "2-Day", "3-Evening")
skilllevel = Array("RN", "LVN", "NA", "Other", "Traveler")
vaproducts = Array("Maternity Leave", "Workman's Comp", "Family
Leave", "Medical Leave", "Other")
c = Target.Address

'I HAVE CHECKED THE TARGET ADDRESS AND IT IS THE CORRECT CELL
'BUT THIS NEXT CODE WHERE IT ADDS THE DROPDOWN PUTS IT IN
'THE WRONG SPOT

With Target
Set ddbox = ActiveSheet.DropDowns.Add (.Left, .Top, .Width, .Height)
End With

With ddbox
.OnAction = "Enterlet"

Select Case typer
Case 1
For i = LBound(vaproducts) To UBound(vaproducts)
.AddItem vaproducts(i)
Next i
Case 2
For i = LBound(skilllevel) To UBound(skilllevel)
.AddItem skilllevel(i)
Next i
Case 3
For i = LBound(shift) To UBound(shift)
.AddItem shift(i)
Next i

End Select
End With
End Sub

--

Dave Peterson

Add dropdown on an event.
 

Thanks Dave. That worked perfectly for me. I don't know why that
works, but I'll take it. Yea it's odd Tom because it definitely
doesn't work for me as is, but with Dave's code (setting the position a
second time it worked perfectly). Thank you both for the help. This
forum wouldnt be where it is without you both.

Add dropdown on an event.
 

Your code worked for me as-is.

(just to muddy the waters)

Chip wrote:

Thanks Dave. That worked perfectly for me. I don't know why that
works, but I'll take it. Yea it's odd Tom because it definitely
doesn't work for me as is, but with Dave's code (setting the position a
second time it worked perfectly). Thank you both for the help. This
forum wouldnt be where it is without you both.

--

Dave Peterson