I want to use just one combobox to change any cell in a single column. What
I purpose is to have the Linked Cell, say A1 used to identify the row number
of the cell to be have the focus. For eg. lets say I type 51 into A1 (51
representing the row number), when the combobox is activated this fires
code which tells the combobox that A1 has the row 51 column A as the current
linked cell.

What that means is that that one combobox will function for any number of
cell in a single column.

What do you think, is this possible?

regards
Pat

Pat,

It is possible, but what is the purpose of the combobox if you will just
display 1 cell. Why not just goto row 51 when 51 is entered in A1?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pat" wrote in message
...
I want to use just one combobox to change any cell in a single column.
What
I purpose is to have the Linked Cell, say A1 used to identify the row
number
of the cell to be have the focus. For eg. lets say I type 51 into A1 (51
representing the row number), when the combobox is activated this fires
code which tells the combobox that A1 has the row 51 column A as the
current
linked cell.

What that means is that that one combobox will function for any number of
cell in a single column.

What do you think, is this possible?

regards
Pat

Oh. Sorry I should have clarified what will be contained in the
FillListRange.
The FillListRange will contain data from another part of the workbook and
will have at least 2 columns of data contained in it. It will be one of
these two columns of data where an item of information will be selected and
thereby be displayed in say A51.
All A1 will be used for is to enter the row number of the sheet and then
when the combobox is clicked the combobox will look at A1 (using code) and
after determining the value in A1 will place the selection from the combobox
into the corresponding cell.

Pat

"Bob Phillips" wrote in message
...
Pat,

It is possible, but what is the purpose of the combobox if you will just
display 1 cell. Why not just goto row 51 when 51 is entered in A1?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pat" wrote in message
...
I want to use just one combobox to change any cell in a single column.
What
I purpose is to have the Linked Cell, say A1 used to identify the row
number
of the cell to be have the focus. For eg. lets say I type 51 into A1 (51
representing the row number), when the combobox is activated this fires
code which tells the combobox that A1 has the row 51 column A as the
current
linked cell.

What that means is that that one combobox will function for any number
of
cell in a single column.

What do you think, is this possible?

regards
Pat

Pat,

Is this what you mean?

Range("G10").Value = Activesheet.Combobox1.List(Range("A1").Value-1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pat" wrote in message
...
Oh. Sorry I should have clarified what will be contained in the
FillListRange.
The FillListRange will contain data from another part of the workbook and
will have at least 2 columns of data contained in it. It will be one of
these two columns of data where an item of information will be selected
and
thereby be displayed in say A51.
All A1 will be used for is to enter the row number of the sheet and then
when the combobox is clicked the combobox will look at A1 (using code) and
after determining the value in A1 will place the selection from the
combobox
into the corresponding cell.

Pat

"Bob Phillips" wrote in message
...
Pat,

It is possible, but what is the purpose of the combobox if you will just
display 1 cell. Why not just goto row 51 when 51 is entered in A1?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pat" wrote in message
...
I want to use just one combobox to change any cell in a single column.
What
I purpose is to have the Linked Cell, say A1 used to identify the row
number
of the cell to be have the focus. For eg. lets say I type 51 into A1
(51
representing the row number), when the combobox is activated this
fires
code which tells the combobox that A1 has the row 51 column A as the
current
linked cell.

What that means is that that one combobox will function for any number
of
cell in a single column.

What do you think, is this possible?

regards
Pat

Not sure what to do with this?

Pat

"Bob Phillips" wrote in message
...
Pat,

Is this what you mean?

Range("G10").Value = Activesheet.Combobox1.List(Range("A1").Value-1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pat" wrote in message
...
Oh. Sorry I should have clarified what will be contained in the
FillListRange.
The FillListRange will contain data from another part of the workbook
and
will have at least 2 columns of data contained in it. It will be one of
these two columns of data where an item of information will be selected
and
thereby be displayed in say A51.
All A1 will be used for is to enter the row number of the sheet and
then
when the combobox is clicked the combobox will look at A1 (using code)
and
after determining the value in A1 will place the selection from the
combobox
into the corresponding cell.

Pat

"Bob Phillips" wrote in message
...
Pat,

It is possible, but what is the purpose of the combobox if you will
just
display 1 cell. Why not just goto row 51 when 51 is entered in A1?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pat" wrote in message
...
I want to use just one combobox to change any cell in a single
column.
What
I purpose is to have the Linked Cell, say A1 used to identify the
row
number
of the cell to be have the focus. For eg. lets say I type 51 into A1
(51
representing the row number), when the combobox is activated this
fires
code which tells the combobox that A1 has the row 51 column A as the
current
linked cell.

What that means is that that one combobox will function for any
number
of
cell in a single column.

What do you think, is this possible?

regards
Pat

You might get a start with this. I used one ComboBox names "cb" Left is
set to zero, and setup 18 Rows to have a height that worked well with
my combobox size (17.25). The combobox will follow the ActiveCell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (ActiveWindow.ActiveCell.Column = 1) And
(ActiveWindow.ActiveCell.Row <= 18) Then 'Column A & <= Row 18
cb.LinkedCell = ActiveWindow.ActiveCell.Address(rowabsolute =
False, columnabsolute = False)
cb.Top = (ActiveWindow.ActiveCell.Row *
ActiveWindow.ActiveCell.Height) - ActiveWindow.ActiveCell.Height
End If
End Sub



Good Luck.


---
Message posted from http://www.ExcelForum.com/

The solution you have provided is interesting. I have followed your
instruction and I am have a little difficulty with the code.
Where the code is causing a problem is from:

'Column A & <= Row 18
cb.LinkedCell = ActiveWindow.ActiveCell.Address(rowabsolute = False,
columnabsolute = False)
cb.Top = (ActiveWindow.ActiveCell.Row * ActiveWindow.ActiveCell.Height)
- ActiveWindow.ActiveCell.Height

should "'Column A & <= Row 18" be a comment?

the remaining code is highlighted in red.



Pat


---
Message posted from http://www.ExcelForum.com/

That's Correct Pat. The 'Column A & <= Row 18 is a comment to the IF
statement. The IF statement is designed to fire the code only when the
user changes ActiveCell in the range of A1:A18. All of the code belongs
in the Worksheet's SelcetionChange Event. I have attached a sample Work
Book.

Attachment filename: moving combobox.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=427008
---
Message posted from http://www.ExcelForum.com/

That's a neat piece of code you have put together!

I have changed the code to suit the workbook I am working on and I am
getting stuck at

cb.Left = 0

I changed this to

cb.Left = 1425.25

to correspond with the properties setting for Left, is this correct?
Either way I get an error with both.

ActiveWindow.ActiveCell.Column = 1

has be changed to

ActiveWindow.ActiveCell.Column = 32

which is column AF, is this correct?

regards and thanks!
Pat





---
Message posted from http://www.ExcelForum.com/

I have for some reason managed to sort out the earlier problem.

cb.Left = 1425.25

has been changed to

cb.Left = 1140

which is correct, why the change without me having changed th
column(s) widths I simply do not no.

Now when I click any cell in the range in column AF the combobox move
to the sixth row below the cell. When I select a value from th
combobox the value is then placed in the cell I origionally selected
rows above the combobox.

Where do you think have I gone wrong?

Pat

:confused

--
Message posted from http://www.ExcelForum.com

That sorted that out!

The following applies to row 1 to 700
(ActiveWindow.ActiveCell.Row <= 700)

I need it to apply only from row 57 to 700. I have made several
attempts at changing this without success. The solution is probabl
fairly simple.

regards and many thanks!

Pa

--
Message posted from http://www.ExcelForum.com

There is likely a better way, but this should work. change the IF
statement to read like below:

If (ActiveWindow.ActiveCell.Column = 32) And
((ActiveWindow.ActiveCell.Row = 57) And (ActiveWindow.ActiveCell.Row
<= 700)) Then


---
Message posted from http://www.ExcelForum.com/

I found something I like better for the IF statement. This function
returns true is the ActiveCell is within a given range. I got it from
this posting: http://tinyurl.com/3393z

It looks like 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


Then the If statement would read:

If InRange(ActiveCell, Range("AF57:AF700")) Then

Note: Add the new function above in the General Declaration


---
Message posted from http://www.ExcelForum.com/

I must say I am very impressed with the combobox and how it behaves.

Can it be made to lock to the column its assigned to?
I mean, if a column is adjusted elsewhere on the sheet can the combobo
adjust the LEFT setting automatically

--
Message posted from http://www.ExcelForum.com

If you use something like:

cb.Height = ActiveWindow.ActiveCell.Height
cb.Width = ActiveWindow.ActiveCell.Width
cb.Top = ActiveWindow.ActiveCell.Top
cb.Left = ActiveWindow.ActiveCell.Left

The Combobox will take on the size characteristics of the Active Cell
How if you are asking if you insert or delete a column before column A
what will happen? The ComboBox will stay with AF (column 32). Now i
you want something like this to happen: I insert one column in betwee
column C and D. How the ComboBox is tied to Column 33. If that is wha
you want, I will have to investigate

--
Message posted from http://www.ExcelForum.com

Yes that would be important, as I often insert and delete column
elsewhere on the sheet

--
Message posted from http://www.ExcelForum.com

Well Pat it's like this. What you are talking about is sometimes called
a Dynamic Range. I spent hours on it last night and what most people
are suggesting is using the Offset function. I have found little to
help me get a grasp on how to apply it in this case. You might start
looking for how it might be made to work for your specific case. If you
find anything that works let me know. I will try a little more, simply
because I don't like to give up on something that's got me stumped.


---
Message posted from http://www.ExcelForum.com/

You have certainly been busy, hope I have not taxed your time too much
This looks like a feature that is either not available to Excel or no
commonly requested. Maybe its one for Microsoft to include in futur
releases!

Please note, I have given this tread a rating of 5, as the quality o
advise and support is second to non.

Cheers

--
Message posted from http://www.ExcelForum.com

Hi Pat,

I must admit that I haven't followed the thread closely, but maybe this
suggestion will help. shrekut mentioned the use of a dynamic range, which
may come in handy here. I don't quite follow your intentions, but if you
want to do what you originally asked for, you could go this route.

1) Add a new name (Insert | Name -- Define)...let's call it test
2) In the Refers to box, enter the following formula:

=OFFSET(Sheet1!$A$1,Sheet1!$A$1-1,0,1,1)

3) Set the LinkedCell property of the ComboBox to "test" (without quotes)

Now, if you enter 10 into cell A1, the results of your ComboBox will display
in cell A10 when you select a new value. The drawback is that if you change
the value in A1 to something else, the current ComboBox value will not
appear in the new cell - it won't fill that new cell until you change the
ComboBox. This could be overcome by some code in the OnChange event of the
Worksheet that your cell A1 resides on.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


You have certainly been busy, hope I have not taxed your time too
much. This looks like a feature that is either not available to Excel
or not commonly requested. Maybe its one for Microsoft to include in
future releases!

Please note, I have given this tread a rating of 5, as the quality of
advise and support is second to non.

Cheers!


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com