Hi,

Does anyone know how to name a Check Box in code while creating the box?
Im 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

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"


"Paul" wrote:

Hi,

Does anyone know how to name a Check Box in code while creating the box?
Im 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

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

"Paul" wrote in message
...
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

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)


"Paul" wrote in message
...
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

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




Paul wrote:

Hi,

Does anyone know how to name a Check Box in code while creating the box?
Im 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

--

Dave Peterson