Combo Box Code
The code below was kindly written for me by Dave Peterson. It fills the
range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5 and offsets the outputs by 1 column. This has proven an extremley useful tool over the last few months. I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e. another tab, and am stuggling. Any ideas? Help much appreciated. Kind regards, Neil 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("A1:A30") 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("z1:z5").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 |
Combo Box Code
Hi Neil,
change Set wks = ActiveSheet by Set ws = Worksheets("Sheet2") "Neil Pearce" wrote: The code below was kindly written for me by Dave Peterson. It fills the range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5 and offsets the outputs by 1 column. This has proven an extremley useful tool over the last few months. I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e. another tab, and am stuggling. Any ideas? Help much appreciated. Kind regards, Neil 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("A1:A30") 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("z1:z5").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 |
Combo Box Code
The dropdowns go on the activesheet, but the list to fill those dropdowns is on
Sheet2??? change this: myDD.ListFillRange _ = .Parent.Range("z1:z5").Address(external:=True) to myDD.ListFillRange _ = worksheets("Sheet2").Range("z1:z5").Address(extern al:=True) The linked cells still go on the same sheet as the dropdowns, right??? Neil Pearce wrote: The code below was kindly written for me by Dave Peterson. It fills the range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5 and offsets the outputs by 1 column. This has proven an extremley useful tool over the last few months. I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e. another tab, and am stuggling. Any ideas? Help much appreciated. Kind regards, Neil 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("A1:A30") 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("z1:z5").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 |
Combo Box Code
myDD.ListFillRange _
=Sheets("Sheet2").Range("z1:z5").Address(external: =True) Works for me. Gord Dibben MS Excel MVP On Mon, 5 Jan 2009 05:57:25 -0800, Neil Pearce wrote: The code below was kindly written for me by Dave Peterson. It fills the range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5 and offsets the outputs by 1 column. This has proven an extremley useful tool over the last few months. I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e. another tab, and am stuggling. Any ideas? Help much appreciated. Kind regards, Neil 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("A1:A30") 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("z1:z5").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 |
All times are GMT +1. The time now is 09:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com