Excel VBA: Current Row?

Question

I managed to get a userform to auto-populate its fields with data already in the worksheet when you click the "next" or "previous" buttons.

i.e.

Private Sub cmdNext_Click()

'increment row number:
lCurrentRow = lCurrentRow + 1

'POPULATE FORM FIELDS
cmbRecords.Text = Cells(lCurrentRow, 7).Value
end sub

Instead of using "next" or "previous"... I placed a combox to the left. The source is a range in the work sheet, as the worksheet populates, it pops up in the combobx.... (incase there are 100+ records, no one wants to click 100 times looking for something they recently entered).

I want for the form to populate itself based on the record selected in the record combobox.. meaning :


This is the work sheet:
First name Last name Phone Number
John Smith 555-555-5555

if on the combobox "smith" is selected, i want his first name, last name and phone number to populate.

The combobox's source is what ever pops up in the last name column because i pre-set it to that range.

How do i tell excel to navigate to the specific record selected in the combobox? do i have to designate the row as where ever the value currently selected in the combox is? (if so, HOW!?)

I'm probably asking this in the worst most confusing way possible. lol

I know how to tell it what to populate, but how do i tell it, where to go? so it knows what record i want? kind of like a link?... is Smith is selected in the combobox, then the fields should auto update / fill his info in the userform. it'll do the same thing as the "next" "Previous" button, but less clicking, just select one of the records previously entered.

Answer 1

Edit: Here is a macro that will do as you ask, assuming First Name in column A, Last Name in column B and Phone Number in column C:



Private Sub ComboBox1_Change()

Dim i, LastRow

LastRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Cells(i, "B").Value = Me.ComboBox1.Value Then

curRow = Cells(i, "B").Row

Me.TextBox1.Value = Cells(i, "A").Value

Me.TextBox2.Value = Cells(i, "C").Value

End If

Next

End Sub



Change column references to suit.

Answer 2

it truly is a elementary macro which will do the pastime, you could make it more desirable sophisticated to analyze if the subsequent row has archives and stop even if it truly is sparkling, yet this solutions your question. Sub Macro1() Dim i As Integer For i = 3 To 50000 Step 3 'create a loop to do the deletion each third row Rows(i).opt for 'opt for the row for deletion determination.Delete i = i - a million ' because deleting a row flow all rows up the subsequent unique row is one a lot less next i end Sub

Answer 3

hmm, i think that i understood what you mean and what you need as result... I guess that the selected name in the combobox will call some function, it receives the name as argument an pass it directly to a For-Next Loop, something like ( pseudo-code):



For i=0 to totalCells Step 0

i=i+1

If ( name_came_from_argument = cell("A" + i) then

'do you work here

Exit Function

end if



Next







...hope that it may help you





[]'s