Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default Copying list-box control

I need to copy a list box control into about five hundred
cells. I need the cell link of the control, however, to
change so that it references the cell the control is
pasted into. No matter what I do, however, the cell link
of the pasted control stays the same (not surprisingly)
as in the original control. (And nope, it doesn't seem
to matter whether the cell-link reference is absolute
[including dollar signs] or not.)

If anyone has any ideas, I'd be extremely appreciative--I
really don't want to individually format 300 controls.
Yuck.


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

What kind of listboxes are they?

Are they from the Forms toolbar or from the Control Toolbox toolbar?

If they're from the control toolbox toolbar, you could have a macro like this
that copies and changes the linked cell.

This might get you started:
Option Explicit
Sub testme()

Dim MstrLB As OLEObject
Dim AnyLB As OLEObject

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("b1:b10")
Set MstrLB = .OLEObjects("ListBox1")
For Each myCell In myRng.Cells
MstrLB.ShapeRange.Duplicate
Set AnyLB = .OLEObjects(.OLEObjects.Count)
With AnyLB
.Top = myCell.Top
.Left = myCell.Left
.Width = myCell.Width
.Height = myCell.Height
.LinkedCell = myCell.Address(external:=True)
.Name = "Listbox" & myCell.Address(0, 0)
End With
Next myCell
End With

End Sub


If they're listboxes from the Forms toolbar, maybe this'll get you started:

Option Explicit
Sub testme2()

Dim MstrLB As ListBox
Dim AnyLB As ListBox

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("b1:b10")
Set MstrLB = .ListBoxes("List Box 1")
For Each myCell In myRng.Cells
MstrLB.ShapeRange.Duplicate
Set AnyLB = .ListBoxes(.ListBoxes.Count)
With AnyLB
.Top = myCell.Top
.Left = myCell.Left
.Width = myCell.Width
.Height = myCell.Height
.LinkedCell = myCell.Address(external:=True)
.Name = "Listbox" & myCell.Address(0, 0)
End With
Next myCell
End With

End Sub

===
If you've already copied the listboxes, you could cycle through them and just
change that .linkedcell.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

As an aside, 300 of these listboxes may be too much for excel to
handle--especially the control toolbox versions.

You may want to reconsider your approach--maybe data|validation--or one listbox
that moves where you want it and puts the value in the activecell???


wrote:

I need to copy a list box control into about five hundred
cells. I need the cell link of the control, however, to
change so that it references the cell the control is
pasted into. No matter what I do, however, the cell link
of the pasted control stays the same (not surprisingly)
as in the original control. (And nope, it doesn't seem
to matter whether the cell-link reference is absolute
[including dollar signs] or not.)

If anyone has any ideas, I'd be extremely appreciative--I
really don't want to individually format 300 controls.
Yuck.


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I find out what items are in one list but not in another l. Michelle Craig Excel Discussion (Misc queries) 2 December 22nd 04 09:32 PM
edit a drop down list paulp Excel Discussion (Misc queries) 1 December 22nd 04 04:20 PM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM
Drop dow list complication Ryan Excel Discussion (Misc queries) 2 December 16th 04 08:49 PM
Counting Repeated text or duplicates in a list Repeatdude Excel Discussion (Misc queries) 5 November 26th 04 08:10 PM


All times are GMT +1. The time now is 06:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"