Use combobox for universal purpose

P

Pat

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
 
B

Bob Phillips

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)
 
P

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
 
B

Bob Phillips

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)
 
P

Pat

Not sure what to do with this?

Pat

Bob Phillips said:
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)
Click to expand...
 
S

shrekut

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. :cool:
 
P

Pat

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.

:confused:

Pat
 
S

shrekut

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
 
P

Pat

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


:confused:
 
P

Pat

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
 
S

shrekut

Sorry it was me. I was calculating the cb.Top wrong (it only worked if
the Cell Height was set to 17.25. Use the below code in place of the
one where:
"cb.Top = (ActiveWindow.ActiveCell.Row *
ActiveWindow.ActiveCell.Height) - ActiveWindow.ActiveCell.Height"
was before:

cb.Height = ActiveWindow.ActiveCell.Height
cb.Top = ActiveWindow.ActiveCell.Top
 
P

Pat

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
 
S

shrekut

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
 
S

shrekut

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
 
P

Pat

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
 
S

shrekut

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
 
P

Pat

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

shrekut

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.
 
P

Pat

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
 
J

Jake Marx

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]
 
Top