ExcelBanter - Syntax error

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Syntax error (https://www.excelbanter.com/excel-discussion-misc-queries/211201-syntax-error.html)

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("D10:D66")
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

You worksheet is determine from the following line

from
Set wks = ActiveSheet

to
set wks = sheets("Sheet1")

"Neil Pearce" wrote:

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("D10:D66")
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

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" wrote:

You worksheet is determine from the following line

from
Set wks = ActiveSheet

to
set wks = sheets("Sheet1")

"Neil Pearce" wrote:

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("D10:D66")
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

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

to

= wks.name & "!I7:I24"

"Neil Pearce" wrote:

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" wrote:

You worksheet is determine from the following line

from
Set wks = ActiveSheet

to
set wks = sheets("Sheet1")

"Neil Pearce" wrote:

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("D10:D66")
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

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.

Neil Pearce wrote:

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" wrote:

You worksheet is determine from the following line

from
Set wks = ActiveSheet

to
set wks = sheets("Sheet1")

"Neil Pearce" wrote:

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("D10:D66")
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

--

Dave Peterson