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

 

[Norie]

There is nothing in that code that will put values in cells on a worksheet.

 

[NimishK]

There is nothing in that code that will put values in cells on a worksheet.

Are you telling me to put values in cells on the worksheet ?

What i ve done is to put values from Sheet1 Cells of column B into Combo1
The below code: all the data in column B will display in Combo1. As already Data there in sheet1

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  = .Cells(2, 2).Value
     userfrm1.text2.Text = .Cells(2, 1).Value
     userfrm1.text3.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

so when selecting/typing/clicking the item from combobox i want its row number.
Are you telling me to write the code of Sheet1 prog.eviornment in Worksheet_change event or some other event.
if yes then your guidance to coding will be appreciated

Have incorporated userfrm1.textRowNumber.Text in below combo1_change event

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
userfrm1.textRowNumber.Text = 'To ge the Row number dispalyed in this textbox of above range
        End if
End Sub

 

[Norie]

No I'm not suggesting anything like that.

All I'm saying is that the code you originally posted does not put any values in cells.

In your original post you said this,

value gets overwritten in the Defined First row of the Worksheet

regarding the change event of ComboBox1.

What exactly do you mean?

 

[NimishK]

value gets overwritten in the Defined First row of the Worksheet

Extremely Sorry my mistake. Wanted to get First row nos. only

 

[NimishK]

i added the following code to get the row number which is in bold

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

[B]Dim srchRange As Range[/B]
[B]Dim fndRow As Range[/B]
[B]Set srchhRange = Range("B2", Range("A65536").End(xlUp))[/B]
[B]Set fndRow = srchRange.Find(combo1.Text, LookIn:=xlValues, lookat:=xlWhole)[/B]
[B]    On Local Error Resume Next[/B]
[B]    userfrm1.txtRowNo.Text = Val(fndRow.Row)[/B]

End Sub

I get the Row number.