Use combobox for universal purpose

[shrekut]

Hi Jack,
We welcome any help you can give. I believe that what Pat is asking fo
is doable, but my experience in vba and vb, for that matter, limits m
ability to solve this one.
I would like to explain the current status if I may. In the start o
this thread Pat was asking for a single ComboBox object that could b
used for any cell within a given range. So what I came up with wa
using the SclectionChange event of the worksheet to trigger th
assignment of size and placement properties of the ComboBox to equa
that of the ActiveCell. Like So...
cb.LinkedCell = ActiveWindow.ActiveCell.Address(rowabsolute
False, columnabsolute = False)
cb.Height = ActiveWindow.ActiveCell.Height
cb.Width = ActiveWindow.ActiveCell.Width
cb.Top = ActiveWindow.ActiveCell.Top
cb.Left = ActiveWindow.ActiveCell.Left

In order to define the range and to test to see if the ActiveCell wa
within that range. WE setup this...
Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If InRange(ActiveCell, Range("A2:A14")) Then
cb.LinkedCell = ActiveWindow.ActiveCell.Address(rowabsolute
False, columnabsolute = False)
cb.Height = ActiveWindow.ActiveCell.Height
cb.Width = ActiveWindow.ActiveCell.Width
cb.Top = ActiveWindow.ActiveCell.Top
cb.Left = ActiveWindow.ActiveCell.Left
End If
End Sub

How the problem we are faced with solving is, the Range that is define
if the If statement "Range("A2:A14")" needs to change if a new colum
is inserted. Making it a Dynamic Range.

Can you show us how a Named Dynamic Range will work here

 

[Pat]

Can anyone tell me why the combobox I have been using quite successfull
for some time nolonger align itself to the target cell. All th
property settings are correct. When a cell in the range is selected th
combobox appears several cell above. It does not matter how many time
the settings are changed it still appears exactly the same number o
cell above the target cell.

I know this is an old thread, hopefully someone with the knowledge wil
be able to help.

Regards
Pa