Syntax error

[Neil Pearce]

I have previously been kindly provided with code to automatically add combo
boxes to a workbook (see below).

However I now wish to amend the code so that the range for the combo boxes
input are on a different tab. I can not get the right Syntax for this to
work correctly. I wonder if some one could amend correctly please, thank-you.

The troublesome line is:

= .Parent.Range(Back-Up Data!"I7:I24").Address(external:=True)


Kindest of regards,

Neil


Option Explicit
Sub COMBOboxes()

Dim myCell As Range
Dim myRng As Range
Dim myDD As DropDown
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.DropDowns.Delete 'nice for testing!
Set myRng = .Range("D1066")
For Each myCell In myRng.Cells
With myCell
Set myDD = .Parent.DropDowns.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)

myDD.ListFillRange _
= .Parent.Range(Back-Up
Data!"I7:I24").Address(external:=True)
myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.ListIndex = 1 '<-- added
End With
Next myCell
End With
End Sub

 

[Joel]

You worksheet is determine from the following line

from
Set wks = ActiveSheet

to
set wks = sheets("Sheet1")

 

[Neil Pearce]

Thanks Joel!

This sorts the Syntax error out nicely. However it also results in the
combo boxes being created in the Back-Up Data tab rather than the tab that
the macro is actually run in.

Any ideas?


Thanking-you in advance,

Neil

 

[Joel]

from
= .Parent.Range(Back-Up
Data!"I7:I24").Address(external:=True)

to

= wks.name & "!I7:I24"

 

[Dave Peterson]

I think those dropdowns were left over from a previous run--maybe when the
activesheet was that "back-up data" worksheet????

Try it again and I bet you don't get any dropdowns on that sheet.

ps.

Instead of this syntax:

myDD.ListFillRange _
= .Parent.Range(Back-Up Data!"I7:I24").Address(external:=True)

I'd use:

myDD.ListFillRange _
= .Parent.Parent.worksheets("Back-Up Data") _
.range("I7:I24").Address(external:=True)

I find it easier to understand.

The previous with statement is "With myCell".

mycell.parent is the worksheet that owns the cell (wks = worksheets("Sheet1")).

mycell.parent.parent is the workbook that owns that worksheet that owns that
cell.