Devious multiplying Forms DropDowns

  • Thread starter Thread starter Darren Hill
  • Start date Start date
D

Darren Hill

I've noticed that, when I have a large number of Forms Drop Downs, fairly
often one or more of them seem to decide to copy themselves over other drop
downs. When I click on the new copies, I notice that it is changing the
wrong linked cells, and discover it is a copy.

Does anyone know why this happens? Maybe there's a shortcut for copying
these things that I'm inadvertently typing.

Darren
 
Hello Darren,

I haven't heard of this. Are you sure it didn't occur when you were
creating them.

Perhaps you created them by copying one and then editing the linked cell on
the copy. When you pasted, it is not hard to repeat the action without
noticing. Perhaps you pasted, edited the link and then inadvertently did
another paste on top.

If I was going to add a lot of Drop Downs, I think I would do it with code
as the positioning and sizing can be done a lot more accurately.

Post back with some details if you want to see an example.
 
Hi Tom, you wrote:
"I haven't heard of this. Are you sure it didn't occur when you were
creating them."

Actually, it was a silly mistake on my part - I copied a cell containing a
drop down to paste it's formula into other cells, so the drop down was
copied too.

Is there a way to cycle through the drop downs, and remove any which aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matches that
cell.)


As to your other question, yes I would be interested in seeing more code for
creating drop downs. After playing with the macro recorder, I did come up
with the first macro below to create them, but I am keen to be shown how to
clean it up (getting rid of the select, for example). The second macro was
provided by Vasant Nanavati, which enables me to position the created
macros.


Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Integer
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer

myColumn = "E"
myLinkCell = "AP"

For myRow = 5 To 24
ActiveSheet.DropDowns.Add(209.25, 88.5, 66.75, 15.75).Select
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = "$" & myLinkCell & "$" & myRow
With Selection
.Name = strDropDown
.PrintObject = False
'.ListFillRange = "$BO$5:$BO$24"
.LinkedCell = myLinkString
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub

Sub DropDowns_Resize()

Dim drp As DropDown, iLen As Long
For Each drp In ActiveSheet.DropDowns
With drp
iLen = Len(drp.Name) - InStr(1, drp.Name, "n ") - 1
.Left = Range(Right(drp.Name, iLen)).Left
.Top = Range(Right(drp.Name, iLen)).Top
.Height = Range(Right(drp.Name, iLen)).Height
.Width = Range(Right(drp.Name, iLen)).Width
End With
Next
End Sub
 
I have a line in the next routine that removes all existing dropdowns on the
active sheet. I have it commented out, but my approach would be to always
create them from scratch. So I recommend uncommenting that line.

Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Long
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer
Dim rng As Range

' Uncomment to remove all dropdowns
'ActiveSheet.DropDowns.Delete

myColumn = "E"
myLinkCell = "AP"
For myRow = 5 To 24
Set rng = ActiveSheet.Cells(myRow, myColumn)
With ActiveSheet.DropDowns.Add(Left:=rng.Left, _
Top:=rng.Top, Width:=rng.Width, _
Height:=rng.Height)
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = myLinkCell & myRow
.Name = strDropDown
.PrintObject = False
.ListFillRange = ActiveSheet.Range("BO5:BO24") _
.Address(external:=True)
.LinkedCell = ActiveSheet.Range(myLinkString) _
.Address(external:=True)
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub
Is there a way to cycle through the drop downs, and remove any which aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matches that
cell.)
Click to expand...

You probably don't need it if you delete all the dropdowns and add new using
the above:

Sub RemoveDropDowns()
Dim drpdwn As DropDown
Dim sName As String
Dim sCell As String
Dim iloc As Long
Dim rng As Range
For Each drpdwn In ActiveSheet.DropDowns
sName = Trim(drpdwn.Name)
sCell = sName
iloc = 0
Do While InStr(sCell, " ")
iloc = InStr(sCell, " ")
sCell = Right(sCell, Len(sCell) - iloc)
Loop
Set rng = Nothing
On Error Resume Next
Set rng = ActiveSheet.Range(sCell)
On Error GoTo 0
If Not rng Is Nothing Then
If drpdwn.TopLeftCell.Address(0, 0) <> sCell Then
drpdwn.Delete
End If
Else
drpdwn.Delete
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Darren Hill said:
Hi Tom, you wrote:
"I haven't heard of this. Are you sure it didn't occur when you were
creating them."

Actually, it was a silly mistake on my part - I copied a cell containing a
drop down to paste it's formula into other cells, so the drop down was
copied too.

Is there a way to cycle through the drop downs, and remove any which aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matches that
cell.)


As to your other question, yes I would be interested in seeing more code for
creating drop downs. After playing with the macro recorder, I did come up
with the first macro below to create them, but I am keen to be shown how to
clean it up (getting rid of the select, for example). The second macro was
provided by Vasant Nanavati, which enables me to position the created
macros.


Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Integer
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer

myColumn = "E"
myLinkCell = "AP"

For myRow = 5 To 24
ActiveSheet.DropDowns.Add(209.25, 88.5, 66.75, 15.75).Select
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = "$" & myLinkCell & "$" & myRow
With Selection
.Name = strDropDown
.PrintObject = False
'.ListFillRange = "$BO$5:$BO$24"
.LinkedCell = myLinkString
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub

Sub DropDowns_Resize()

Dim drp As DropDown, iLen As Long
For Each drp In ActiveSheet.DropDowns
With drp
iLen = Len(drp.Name) - InStr(1, drp.Name, "n ") - 1
.Left = Range(Right(drp.Name, iLen)).Left
.Top = Range(Right(drp.Name, iLen)).Top
.Height = Range(Right(drp.Name, iLen)).Height
.Width = Range(Right(drp.Name, iLen)).Width
End With
Next
End Sub
Click to expand...
 
Thanks, Tom. That looks excellent. There's a few things there that I didn't
know you could do - like ActiveSheet.Cells(myRow, myColumn) where myColumn
is a letter. I thought it had to be a number. And the With statement is a
revelation.
I have a question about these two lines:
.ListFillRange = ActiveSheet.Range("BO5:BO24") _
.Address(external:=True)
.LinkedCell = ActiveSheet.Range(myLinkString) _
.Address(external:=True)
Click to expand...

You've fully declared them - I didn't realise there was a need for this. I
thought just using a string like "BO5:BO24" would suffice. What sort of
errors can this cause?

Also, with the Remove macro, you have the line:
sName = Trim(drpdwn.Name)
Click to expand...

Is this necessary because of the section below or is there another reason?
Do While InStr(sCell, " ")
iloc = InStr(sCell, " ")
sCell = Right(sCell, Len(sCell) - iloc)
Loop
Click to expand...

Thanks again. :)
--
Darren
Tom Ogilvy said:
I have a line in the next routine that removes all existing dropdowns on the
active sheet. I have it commented out, but my approach would be to always
create them from scratch. So I recommend uncommenting that line.

Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Long
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer
Dim rng As Range

' Uncomment to remove all dropdowns
'ActiveSheet.DropDowns.Delete

myColumn = "E"
myLinkCell = "AP"
For myRow = 5 To 24
Set rng = ActiveSheet.Cells(myRow, myColumn)
With ActiveSheet.DropDowns.Add(Left:=rng.Left, _
Top:=rng.Top, Width:=rng.Width, _
Height:=rng.Height)
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = myLinkCell & myRow
.Name = strDropDown
.PrintObject = False
.ListFillRange = ActiveSheet.Range("BO5:BO24") _
.Address(external:=True)
.LinkedCell = ActiveSheet.Range(myLinkString) _
.Address(external:=True)
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub
Is there a way to cycle through the drop downs, and remove any which aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matches that
cell.)
Click to expand...

You probably don't need it if you delete all the dropdowns and add new using
the above:

Sub RemoveDropDowns()
Dim drpdwn As DropDown
Dim sName As String
Dim sCell As String
Dim iloc As Long
Dim rng As Range
For Each drpdwn In ActiveSheet.DropDowns
sName = Trim(drpdwn.Name)
sCell = sName
iloc = 0
Do While InStr(sCell, " ")
iloc = InStr(sCell, " ")
sCell = Right(sCell, Len(sCell) - iloc)
Loop
Set rng = Nothing
On Error Resume Next
Set rng = ActiveSheet.Range(sCell)
On Error GoTo 0
If Not rng Is Nothing Then
If drpdwn.TopLeftCell.Address(0, 0) <> sCell Then
drpdwn.Delete
End If
Else
drpdwn.Delete
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Darren Hill said:
Hi Tom, you wrote:
"I haven't heard of this. Are you sure it didn't occur when you were
creating them."

Actually, it was a silly mistake on my part - I copied a cell containing a
drop down to paste it's formula into other cells, so the drop down was
copied too.

Is there a way to cycle through the drop downs, and remove any which aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matches that
cell.)


As to your other question, yes I would be interested in seeing more code for
creating drop downs. After playing with the macro recorder, I did come up
with the first macro below to create them, but I am keen to be shown how to
clean it up (getting rid of the select, for example). The second macro was
provided by Vasant Nanavati, which enables me to position the created
macros.


Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Integer
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer

myColumn = "E"
myLinkCell = "AP"

For myRow = 5 To 24
ActiveSheet.DropDowns.Add(209.25, 88.5, 66.75, 15.75).Select
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = "$" & myLinkCell & "$" & myRow
With Selection
.Name = strDropDown
.PrintObject = False
'.ListFillRange = "$BO$5:$BO$24"
.LinkedCell = myLinkString
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub

Sub DropDowns_Resize()

Dim drp As DropDown, iLen As Long
For Each drp In ActiveSheet.DropDowns
With drp
iLen = Len(drp.Name) - InStr(1, drp.Name, "n ") - 1
.Left = Range(Right(drp.Name, iLen)).Left
.Top = Range(Right(drp.Name, iLen)).Top
.Height = Range(Right(drp.Name, iLen)).Height
.Width = Range(Right(drp.Name, iLen)).Width
End With
Next
End Sub
--
Darren

Tom Ogilvy said:
Hello Darren,

I haven't heard of this. Are you sure it didn't occur when you were
creating them.

Perhaps you created them by copying one and then editing the linked
Click to expand...
cell
on
the copy. When you pasted, it is not hard to repeat the action without
noticing. Perhaps you pasted, edited the link and then inadvertently did
another paste on top.

If I was going to add a lot of Drop Downs, I think I would do it with code
as the positioning and sizing can be done a lot more accurately.

Post back with some details if you want to see an example.

--
Regards,
Tom Ogilvy


I've noticed that, when I have a large number of Forms Drop Downs, fairly
often one or more of them seem to decide to copy themselves over other
drop
downs. When I click on the new copies, I notice that it is changing the
wrong linked cells, and discover it is a copy.

Does anyone know why this happens? Maybe there's a shortcut for copying
these things that I'm inadvertently typing.

Darren
Click to expand...
Click to expand...
Click to expand...
 
Inline:

Darren Hill said:
Thanks, Tom. That looks excellent. There's a few things there that I didn't
know you could do - like ActiveSheet.Cells(myRow, myColumn) where myColumn
is a letter. I thought it had to be a number. And the With statement is a
revelation.
I have a question about these two lines:

You've fully declared them - I didn't realise there was a need for this. I
thought just using a string like "BO5:BO24" would suffice. What sort of
errors can this cause?
Click to expand...

Never hurts to be specific. The results of the address is a string
Errors would be that it defaults to the activesheet. (which is the same
here, but as I said, never hurts . . .)
Also, with the Remove macro, you have the line:

Is this necessary because of the section below or is there another reason?
Click to expand...

Again, never hurts to be specific. My thought was spaces on the right of the
name.
Thanks again. :)
Click to expand...

Your welcome.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
I have a line in the next routine that removes all existing dropdowns on the
active sheet. I have it commented out, but my approach would be to always
create them from scratch. So I recommend uncommenting that line.

Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Long
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer
Dim rng As Range

' Uncomment to remove all dropdowns
'ActiveSheet.DropDowns.Delete

myColumn = "E"
myLinkCell = "AP"
For myRow = 5 To 24
Set rng = ActiveSheet.Cells(myRow, myColumn)
With ActiveSheet.DropDowns.Add(Left:=rng.Left, _
Top:=rng.Top, Width:=rng.Width, _
Height:=rng.Height)
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = myLinkCell & myRow
.Name = strDropDown
.PrintObject = False
.ListFillRange = ActiveSheet.Range("BO5:BO24") _
.Address(external:=True)
.LinkedCell = ActiveSheet.Range(myLinkString) _
.Address(external:=True)
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub
Is there a way to cycle through the drop downs, and remove any which aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matches that
cell.)
Click to expand...

You probably don't need it if you delete all the dropdowns and add new using
the above:

Sub RemoveDropDowns()
Dim drpdwn As DropDown
Dim sName As String
Dim sCell As String
Dim iloc As Long
Dim rng As Range
For Each drpdwn In ActiveSheet.DropDowns
sName = Trim(drpdwn.Name)
sCell = sName
iloc = 0
Do While InStr(sCell, " ")
iloc = InStr(sCell, " ")
sCell = Right(sCell, Len(sCell) - iloc)
Loop
Set rng = Nothing
On Error Resume Next
Set rng = ActiveSheet.Range(sCell)
On Error GoTo 0
If Not rng Is Nothing Then
If drpdwn.TopLeftCell.Address(0, 0) <> sCell Then
drpdwn.Delete
End If
Else
drpdwn.Delete
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Darren Hill said:
Hi Tom, you wrote:
"I haven't heard of this. Are you sure it didn't occur when you were
creating them."

Actually, it was a silly mistake on my part - I copied a cell
Click to expand...
Click to expand...
containing
a
drop down to paste it's formula into other cells, so the drop down was
copied too.

Is there a way to cycle through the drop downs, and remove any which aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matches that
cell.)


As to your other question, yes I would be interested in seeing more
Click to expand...
code
for
creating drop downs. After playing with the macro recorder, I did come up
with the first macro below to create them, but I am keen to be shown
Click to expand...
how
to
clean it up (getting rid of the select, for example). The second macro was
provided by Vasant Nanavati, which enables me to position the created
macros.


Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Integer
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer

myColumn = "E"
myLinkCell = "AP"

For myRow = 5 To 24
ActiveSheet.DropDowns.Add(209.25, 88.5, 66.75, 15.75).Select
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = "$" & myLinkCell & "$" & myRow
With Selection
.Name = strDropDown
.PrintObject = False
'.ListFillRange = "$BO$5:$BO$24"
.LinkedCell = myLinkString
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub

Sub DropDowns_Resize()

Dim drp As DropDown, iLen As Long
For Each drp In ActiveSheet.DropDowns
With drp
iLen = Len(drp.Name) - InStr(1, drp.Name, "n ") - 1
.Left = Range(Right(drp.Name, iLen)).Left
.Top = Range(Right(drp.Name, iLen)).Top
.Height = Range(Right(drp.Name, iLen)).Height
.Width = Range(Right(drp.Name, iLen)).Width
End With
Next
End Sub
--
Darren

Hello Darren,

I haven't heard of this. Are you sure it didn't occur when you were
creating them.

Perhaps you created them by copying one and then editing the linked cell
on
the copy. When you pasted, it is not hard to repeat the action without
noticing. Perhaps you pasted, edited the link and then
Click to expand...
inadvertently
did
another paste on top.

If I was going to add a lot of Drop Downs, I think I would do it
Click to expand...
with
code
as the positioning and sizing can be done a lot more accurately.

Post back with some details if you want to see an example.

--
Regards,
Tom Ogilvy


I've noticed that, when I have a large number of Forms Drop Downs,
fairly
often one or more of them seem to decide to copy themselves over other
drop
downs. When I click on the new copies, I notice that it is
Click to expand...
changing
the
wrong linked cells, and discover it is a copy.

Does anyone know why this happens? Maybe there's a shortcut for copying
these things that I'm inadvertently typing.

Darren
Click to expand...
Click to expand...
Click to expand...
 
You said "never hurts to be specific."
I thought that might be the case, I was just checking that there wasn't
something I was missing. At the risk of repeating myself, thanks :)

I've been constructing the macro for creating all my drop downs, and hit a
snag.
One of the Fill Ranges uses the range name "List_GarmentTypes". It's on the
sheet "GarmentTypes".

How would I replace the line

..ListFillRange = Worksheets("GarmentTypes"). _
Range("List_GarmentTypes").Address(external:=True)

to preserve the range name in the listfillrange box?

--
Darren
Tom Ogilvy said:
Inline:

Darren Hill said:
Thanks, Tom. That looks excellent. There's a few things there that I didn't
know you could do - like ActiveSheet.Cells(myRow, myColumn) where myColumn
is a letter. I thought it had to be a number. And the With statement is a
revelation.
I have a question about these two lines:

You've fully declared them - I didn't realise there was a need for this. I
thought just using a string like "BO5:BO24" would suffice. What sort of
errors can this cause?
Click to expand...

Never hurts to be specific. The results of the address is a string
Errors would be that it defaults to the activesheet. (which is the same
here, but as I said, never hurts . . .)
Also, with the Remove macro, you have the line:

Is this necessary because of the section below or is there another reason?
Click to expand...

Again, never hurts to be specific. My thought was spaces on the right of the
name.


Thanks again. :)
Click to expand...

Your welcome.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
I have a line in the next routine that removes all existing dropdowns
Click to expand...
on
the
active sheet. I have it commented out, but my approach would be to always
create them from scratch. So I recommend uncommenting that line.

Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Long
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer
Dim rng As Range

' Uncomment to remove all dropdowns
'ActiveSheet.DropDowns.Delete

myColumn = "E"
myLinkCell = "AP"
For myRow = 5 To 24
Set rng = ActiveSheet.Cells(myRow, myColumn)
With ActiveSheet.DropDowns.Add(Left:=rng.Left, _
Top:=rng.Top, Width:=rng.Width, _
Height:=rng.Height)
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = myLinkCell & myRow
.Name = strDropDown
.PrintObject = False
.ListFillRange = ActiveSheet.Range("BO5:BO24") _
.Address(external:=True)
.LinkedCell = ActiveSheet.Range(myLinkString) _
.Address(external:=True)
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub

Is there a way to cycle through the drop downs, and remove any which
aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name matche
Click to expand...
s
that
cell.)

You probably don't need it if you delete all the dropdowns and add new using
the above:

Sub RemoveDropDowns()
Dim drpdwn As DropDown
Dim sName As String
Dim sCell As String
Dim iloc As Long
Dim rng As Range
For Each drpdwn In ActiveSheet.DropDowns
sName = Trim(drpdwn.Name)
sCell = sName
iloc = 0
Do While InStr(sCell, " ")
iloc = InStr(sCell, " ")
sCell = Right(sCell, Len(sCell) - iloc)
Loop
Set rng = Nothing
On Error Resume Next
Set rng = ActiveSheet.Range(sCell)
On Error GoTo 0
If Not rng Is Nothing Then
If drpdwn.TopLeftCell.Address(0, 0) <> sCell Then
drpdwn.Delete
End If
Else
drpdwn.Delete
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Hi Tom, you wrote:
"I haven't heard of this. Are you sure it didn't occur when you were
creating them."

Actually, it was a silly mistake on my part - I copied a cell
Click to expand...
containing
a
drop down to paste it's formula into other cells, so the drop down was
copied too.

Is there a way to cycle through the drop downs, and remove any which
aren't
named in the format:
"Drop Down [RC]" with [RC] = the address of the cell containing the Drop
Down, example: "A5". (So, I'd need to identify the cell that the DD is
currently hovering over, and then check to see that it's name
Click to expand...
matches
that
cell.)


As to your other question, yes I would be interested in seeing more code
for
creating drop downs. After playing with the macro recorder, I did
Click to expand...
come
up
with the first macro below to create them, but I am keen to be shown how
to
clean it up (getting rid of the select, for example). The second
Click to expand...
macro
was
provided by Vasant Nanavati, which enables me to position the created
macros.


Sub DropDowns_Create()
'
' DropDown_Create Macro
' Macro recorded 08/11/2003 by Darren
' MyCell = the cell in which the

Dim myRow As Integer
Dim myColumn As String
Dim strDropDown As String
Dim myLinkCell As String
Dim myLinkString As String
Dim myCounter As Integer

myColumn = "E"
myLinkCell = "AP"

For myRow = 5 To 24
ActiveSheet.DropDowns.Add(209.25, 88.5, 66.75, 15.75).Select
strDropDown = "Drop Down " & myColumn & myRow
myLinkString = "$" & myLinkCell & "$" & myRow
With Selection
.Name = strDropDown
.PrintObject = False
'.ListFillRange = "$BO$5:$BO$24"
.LinkedCell = myLinkString
.DropDownLines = 8
.Display3DShading = False
End With
Next
End Sub

Sub DropDowns_Resize()

Dim drp As DropDown, iLen As Long
For Each drp In ActiveSheet.DropDowns
With drp
iLen = Len(drp.Name) - InStr(1, drp.Name, "n ") - 1
.Left = Range(Right(drp.Name, iLen)).Left
.Top = Range(Right(drp.Name, iLen)).Top
.Height = Range(Right(drp.Name, iLen)).Height
.Width = Range(Right(drp.Name, iLen)).Width
End With
Next
End Sub
--
Darren

Hello Darren,

I haven't heard of this. Are you sure it didn't occur when you were
creating them.

Perhaps you created them by copying one and then editing the
Click to expand...
linked
cell
on
the copy. When you pasted, it is not hard to repeat the action without
noticing. Perhaps you pasted, edited the link and then inadvertently
did
another paste on top.

If I was going to add a lot of Drop Downs, I think I would do it with
code
as the positioning and sizing can be done a lot more accurately.

Post back with some details if you want to see an example.

--
Regards,
Tom Ogilvy


I've noticed that, when I have a large number of Forms Drop Downs,
fairly
often one or more of them seem to decide to copy themselves over other
drop
downs. When I click on the new copies, I notice that it is changing
the
wrong linked cells, and discover it is a copy.

Does anyone know why this happens? Maybe there's a shortcut for
copying
these things that I'm inadvertently typing.

Darren
Click to expand...
Click to expand...
Click to expand...
 
Doh! I figured it out:

I asked:
How would I replace the line

..ListFillRange = Worksheets("GarmentTypes"). _
Range("List_GarmentTypes").Address(external:=True)

and the answer was, of course

..ListFillRange = "List_GarmentTypes"

Thanks.

Darren
 
The nice thing about the Forms controls, if you turn on the macro recorder
and make a change manually, it is recorded (at least for most things). I
always find it useful. (certainly isn't the complete solution, but it
helps). So don't forget the macro recorder. <g>
 
Back
Top