Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

combo box

  Asked By: Loretta    Date: Dec 09    Category: MS Office    Views: 1064
  

If you know how to make a selected item in a combobox to activate the
active cell in the datasource I would be very happy.

Share: 

 

9 Answers Found

 
Answer #1    Answered By: Casey Montgomery     Answered On: Dec 09

It depends how the comboBox is populated. If it is populated from a named
range on the worksheet (say, it is called MyRange) then you could use
something like:

MyRange.offset (cboMyBox.ListIndex,0)

ListIndex gives the position in the list of the selected  item starting from 0.
This assumes the range is a vertical range.

 
Answer #2    Answered By: Jonathan Brown     Answered On: Dec 09

Suppose I have a user form with a combo  box. The combo box  is populated from a
data range on the worksheet. It's a database with records. I want for exemple to
edit a certain record which I select with the combo box. To be able to edit a
certain record I have to activate  the row where that record is. If for exemple
from the user form, I select an item  from the combo box and I put a button named
"Edit this record" how to I make the form to open that record, that is I have
somehow make select that row by means of a code. Did you catch what I mean? For
instance, it's a database with birthdates. Suppose I enter a new record and
pressed the OK button. I added a new record to my database which is on a
worksheet named DataSource. Lately I mentioned a mistake in a record and I want
to edit that record. I select the record with the combo and press the button
"Edit this record". Now you see what I mean?

 
Answer #3    Answered By: Zobebah Mizrachi     Answered On: Dec 09

Try

MyRange.offset (cboMyBox.ListIndex ,0).activate

You would put this in the click event of the edit button

 
Answer #4    Answered By: Chuong Tran     Answered On: Dec 09

I did that a while ago using a userform with textboxes. Each textbox
was linked to one of the different columns on the worksheet. I used a
macro to search for a record then set that cell  to active and used :



Range("E" & ActiveCell.row)=commentstextbox.text



I used similar lines of code to populate the textboxes on the userform
and then I was able to modify them as I desired. Afterwords I used a
command button to use another macro to update that record to what was in
the userform. I would give more help with specific code but the form
was very large and had a lot of code for a lot of other things in there
as well.

 
Answer #5    Answered By: Indie Williams     Answered On: Dec 09

Look what I did, but the code fails.

Private Sub cmdEdit_Click()
Manager.Offset(lstData.ListIndex, 0).Activate
End Sub

Private Sub UserForm_Initialize()
Dim lRow As Range
Dim ws As Worksheet
Set ws = Worksheets("DataSource")
For Each lRow In ws.Range("Manager")
With Me.lstData
.AddItem lRow.Value
.List(.ListCount - 1, 1) = lRow.Offset(0, 1).Value
End With
Next lRow
End Sub

 
Answer #6    Answered By: Kuhlbert Schmidt     Answered On: Dec 09

Try

Worksheets("DataSource").Range("Manager").Offset(lstData.ListIndex,
0).Activate

While you are in the editor click Debug/CompileVBA Project. If this throws
errors you need to correct them. Then try to run it and let me know what
happens. Don't say "It Fails". I need to know what error message there is or
if nothing at all happens. When you look at the spreadsheet what cell  is
active?

 
Answer #7    Answered By: Maria Hughes     Answered On: Dec 09

Actually I need a line of code which will link the active cell  on the worksheet
with the selected  item from the list box.

 
Answer #8    Answered By: Damian Jones     Answered On: Dec 09

but to tell you the truth i didn't quite understand. David
sent me a line of code which would help, but I don't know why it fails. Maybe
you can help. The bold line should active the row in the worksheet with the item
selected in the listbox. I don't know why it generates error.

Private Sub cmdEdit_Click()
Manager.Offset(lstData.ListIndex, 0).Activate
End Sub

Private Sub UserForm_Initialize()
Dim lRow As Range
Dim ws As Worksheet
Set ws = Worksheets("DataSource")
For Each lRow In ws.Range("Manager")
With Me.lstData
.AddItem lRow.Value
.List(.ListCount - 1, 1) = lRow.Offset(0, 1).Value
End With
Next lRow
End Sub

 
Answer #9    Answered By: Eamon Jones     Answered On: Dec 09

it works perfectly, except it select the range as well. In
fact, it doesn't bother me.

 
Didn't find what you were looking for? Find more on combo box Or get search suggestion and latest updates.




Tagged: