Name Check Box in code

[Guest]

Hi,

Does anyone know how to name a Check Box in code while creating the box?
I’m using a WITH statement and .NAME. This is on an Excel Sheet.

Here is the code:
Sub CreateDropDownBox()
' Create Drop Down Box
Dim DropDown

With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=150, Top:=10, Width:=100, Height:=10)
.ControlFormat.DropDownLines = 10
.Name = DropDown

End With
ActiveSheet.DropDowns.Select
With Selection
.ListFillRange = "$C$1:$C$6"
.LinkedCell = ""
.DropDownLines = 8
.Display3DShading = False
End With
End Sub

The ‘.Name = DropDown’ returns ‘The specific value is out of range’ error
statement.

Any help will be appreciated.

Thanks
Paul

 

[Guest]

I think all you forgot was the quotes:
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=150, Top:=10, Width:=100, Height:=10)
.ControlFormat.DropDownLines = 10
.Name = "DropDown"

 

[Sharad Naik]

Hi Paul,

It should be
DropDown = .Name since want to read the name and assing it to variable
DropDown.
And NOT
..Name = Dropdown cause this mean you are trying to set the name and
since Varibale DropDown is empty, it is giving error.

Sharad

 

[Bob Phillips]

Paul,

This works for me

Sub CreateDropDownBox()
' Create Drop Down Box
Dim DropDown

With Worksheets(1).Shapes
Set DropDown = .AddFormControl(xlDropDown, _
Left:=150, Top:=10, Width:=100, Height:=10)
End With

With DropDown
.Name = "myDropdown"
With .ControlFormat
.DropDownLines = 10
.ListFillRange = "$C$1:$C$6"
.LinkedCell = ""
End With
.DrawingObject.Display3DShading = False
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

I don't think I would use DropDown as my variable name. That's a name of a
control that excel uses.

And you can still work with the dropdown directly--instead of going through the
shapes collection:

Option Explicit
Sub CreateDropDownBox()

Dim MyDropDown As DropDown

With Worksheets(1)
Set MyDropDown = .DropDowns.Add _
(Left:=150, Top:=10, _
Width:=100, Height:=10)
End With

With MyDropDown
.Name = "dd_" & .TopLeftCell.Address(0, 0)
.ListFillRange = .Parent.Range("c1:c6").Address(external:=True)
.DropDownLines = 8
.Display3DShading = False
End With

End Sub