Get row number via combo1.change event instantly using idx = combo1.ListIndex

[NimishK]

Hi
Anyone
Can you get the Row Number on basis of Index of Combolist ?


The reason is that when in Combo1.Change() event
when i type the data in combo change i get respective values of its data in text boxes but what happens is
the same value gets overwritten in the Defined First row of the Worksheet. Which i dont want to happen.


What i want is when i search data in combobox it should goto that row number of the worksheet where that respective data is there in respective columns
so how to get the row number

Private Sub UserForm_Initialize()
Dim myArray
Dim intRow As Long
Dim lastRow As Long


curRec = 1
curRow = 2


With Sheets("Sheet1")
    .Activate
     userfrm1.text1.Text .Text = .Cells(2, 2).Value
     userfrm1.text2.Text Text = .Cells(2, 1).Value
     userfrm1.text3.Text .Text = .Cells(2, 9).Value
     
     intRow = .Cells(Rows.Count, 1).End(xlUp).Row
     myArray = .Range("A2:J" & intRow).Value
    userfrm1combo1.List() = .Range(Cells(2, 2), Cells(intRow, 2)).Value
     
End With
End sub

How to get row number with below combo1.change by which i type the values in combobox and respective values get updated in text boxes and in that particular row for which i was searching the value

Private Sub combo1_Change()
Dim IdWs As Worksheet
Set IdWs = Sheets("Sheet1")


Dim myRanges As Range
Set myRanges = Sheets("Sheet1").Range("a2:J25")   


Dim idx As Long
      idx = combo1.ListIndex

          If idx <> -1 Then
          
          With Worksheets("Sheet1")
              userfrm1.text1.Text = .Range("A" & idx + 2).Value
              userfrm1.text2.Text = .Range("B" & idx + 2).Value
              userfrm1.text3.Text = .Range("C" & idx + 2).Value
           End with
        End if
End Sub

Thanks
NimishK

 

[Fluff]

What is not working?
If you select the third item in combo1 you should get the data from row 4.

 

[Norie]

In the posted code nothing is written to the sheet, is there other code that could be doing that? Or perhaps the textboxes are linked to cells?

 

[NimishK]

Fluff

What is not working?
If you select the third item in combo1 you should get the data from row 4.

Clarity:
At present When selecting 1st item in combobox selects the 2nd row data as it is defined at first row. Selection of data in combobox is properly selected and updated in respective textboxes but somehow does not retain in its original row position
So selecting 3rd item will select data of 4th row but then my 2nd row is overwritten by data of 4th row. How to avoid this ?

 

[Fluff]

As Norie pointed out in post#3, you have not provided any code that writes to the sheet.

 

[NimishK]

Norie

In the posted code nothing is written to the sheet, is there other code that could be doing that? Or perhaps the textboxes are linked to cells?

by the way sheet1 contains data and everything is linked to textboxes and cells of worksheet. pls. refer to code posted above and paste in VBE enviormennt to check
Basically want the Row No of the selected value. Should FLASH the row no. like clicking a Photo. because this is written in change event of combobox

Option Explicit
Dim a$()
Dim b$()
Dim c$()
Dim curRec As Integer, curRow As Integer

thats it. No code written in Worksheet programming Enviornment

 

[Norie]

Norie
everything is linked to textboxes and cells of worksheet

Do you mean you have set the ControlSource property of the textboxes to refer to cells on a worksheet?

If you do then anything entered in them will automatically be written to the linked cells.

 

[NimishK]

Do you mean you have set the ControlSource property of the textboxes to refer to cells on a worksheet?

If you do then anything entered in them will automatically be written to the linked cells.

Dear Norie
even ControlSourceProperty of textboxes is not set.
Am sorry will be using ControlSourceProperty for first time. if you insist. Let me check.
What have i to write in ControlSourceProperty to link cells. An E.g would be better for me to move on.

 

[Norie]

I'm not suggesting you use ControlSource, I thought that might be what was causing the problem.

 

[NimishK]

Ok. I checked my textboxes with controlSource. Its Blank. And this is it what i've coded which is in front of you.