Getting the cell address of a sheet cell from the Change Event of a Listbox1's code n = Listbox1.Listindex

[chazrab]

This code [TABLE] [TR] [TD]Private Sub ListBox1_Change()[/TD] [TD][/TD] [/TR] [TR] [TD]Dim n As Long[/TD] [TD][/TD] [/TR] [TR] [TD]n = ListBox1.ListIndex[/TD] [TD][/TD] [/TR] [TR] [TD]Me.TextBox1.Value = Me.ListBox1.List(n, 2) ---> n and 2, the column number will change. Rowsource extends to column Q[/TD] [TD][/TD] [/TR] [TR] [TD]End Sub selects Beans and displays that item in Textbox1 From the sheet, we know this cell address is $C$2 I want to be able to make notes in Textobx1 of the userform, save it and write it back to the correct cell, in this case, C2. This will change with each change in column A for any row in column A selected. In order to do that, I need to be able to read the cell address of the item selected in the listbox. This would involve using the Listbox1 value of n(which gives the row number) to get the cell address each time. Rowsource = Sheet4!A1:R100 --->This is a dynamic Rowsource and will change with additions to rows and columns Seems really simple but I'm stuck here and can't figure out how to just get the cell address of any text displayed in Textbox1 using n = Listbox1.List(n,2) or Listbox1.List(n,4) , etc. How would I do that - i.e., get the cell address of each and any item displayed in Textbox1? The two images show exactly what I'd like to do with any cell in any row and column of the underlying sheet Would very much appreciate anyone's help. Thanks cr

[/TD]
[/TR]
[/TABLE]

 

[Legacy 143009]

You can make a check on each character typing:

Private Sub TextBox1_Change()
    With Application
    If Not IsError(.Match(TextBox1.Text, .Index(Worksheets("Sheet1").UsedRange, .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)) Then
      MsgBox Cells(.Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), .Match(TextBox1.Text, .Index(Worksheets("Sheet1").UsedRange, .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)).Address
    End If
  End With
End Sub

 

[chazrab]

You can make a check on each character typing:

Private Sub TextBox1_Change()
   
With Application
    If Not IsError(.Match(TextBox1.Text, .Index(Worksheets("[B]PROPHLINKS[/B]").UsedRange, .Match(ListBox1.List(ListBox1.TopIndex), Columns("[B]B[/B]"), 0), 0), 0)) Then
      MsgBox Cells(.Match(ListBox1.List(ListBox1.TopIndex), Columns("[B]B[/B]"), 0), .Match(TextBox1.Text, .Index(Worksheets("[B]PROPHLINKS[/B]").UsedRange, .Match(ListBox1.List(ListBox1.TopIndex), Columns("[B]B[/B]"), 0), 0), 0)).Address
    End If
  End With
End Sub

Hi Flashbond - thanks for helping. I copied your code and changed "Sheet1"(was just a simple example with the generic code and sheet items to keep it simple.
The code if correct, should save any changes to Textbox1 on Userform8 and write it back to the correct cell location it was taken from - i.e., B1
of the underlying sheet. It does not add these changes.

I don't know what I could be doing wrong - all I did was just copy your mATCH code and substitute the correct sheet name (PROPHLINKS) in place of Sheet1 of the example I submitted. and changed the column to B Here's the code in the Change event of Textbox1
of the userform7:

Private Sub TextBox1_Change()
 With Application
    If Not IsError(.Match(TextBox1.Text, .Index(Worksheets("PROPHLINKS").UsedRange, .Match(ListBox1.List(ListBox1.TopIndex), Columns("B"), 0), 0), 0)) Then
      MsgBox Cells(.Match(ListBox1.List(ListBox1.TopIndex), Columns("B"), 0), .Match(TextBox1.Text, .Index(Worksheets("PROPHLINKS").UsedRange, .Match(ListBox1.List(ListBox1.TopIndex), Columns("B"), 0), 0), 0)).Address
    End If
  End With
End Sub

The images below explain what resulted when tried to run the code. As you can see, no changes were made in cell B1.
Thanks for trying to help. Please Let me know what is not working with this code so we can get it to work correctly.
cr

 

[Legacy 143009]

This is the full solution that I came up wtih. It works like this:
First type in Textbox1. As you type it will search for B, Be, Bea, Bean, Beans
Once it auto-locates Beans, it will start to modify that cell as you continue to type.

Dim myAddress As Range

Private Sub TextBox1_Change()
  If Not myAddress Is Nothing Then
    myAddress.Value = TextBox1.Text
  End If
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  With Application
    If Not IsError(.Match(TextBox1.Text, .Index(Worksheets("Sheet1").Range("A1:D3"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)) Then
      Set myAddress = Worksheets("Sheet1").Range(Cells(.Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), .Match(TextBox1.Text, .Index(Worksheets("Sheet1").Range("A1:D3"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)).Address)
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  ListBox1.List = Range("A1:A3").Value
End Sub

Please find the sample file below to play with:

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

 

[chazrab]

This is the full solution that I came up wtih. It works like this:
First type in Textbox1. As you type it will search for B, Be, Bea, Bean, Beans
Once it auto-locates Beans, it will start to modify that cell as you continue to type.

Dim myAddress As Range

Private Sub TextBox1_Change()
  If Not myAddress Is Nothing Then
    myAddress.Value = TextBox1.Text
  End If
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  With Application
    If Not IsError(.Match(TextBox1.Text, .Index(Worksheets("Sheet1").Range("A1:D3"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)) Then
      Set myAddress = Worksheets("Sheet1").Range(Cells(.Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), .Match(TextBox1.Text, .Index(Worksheets("Sheet1").Range("A1:D3"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)).Address)
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  ListBox1.List = Range("A1:A3").Value
End Sub

Please find the sample file below to play with:

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

Hi Flashbond, thank you again for taking the time to help me with this. The sample file and userform1 does work - except for one thing -
Your userform shows a blank textbox with no text already in it. There will always be text already present in Textbox1 of the userform.
This code gives a "Permission denied " error message when I run it, probably because text is already there - not sure. But here's the
code I copied and used:

Private Sub TextBox1_Change()
'  If Not myAddress Is Nothing Then
'    myAddress.Value = TextBox1.Text
'  End If
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'With Application
'    If Not IsError(.Match(TextBox1.Text, .Index(Worksheets("PROPHLINKS").Range("A1:T100"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)) Then
'      Set myAddress = Worksheets("PROPHLINKS").Range(Cells(.Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), .Match(TextBox1.Text, .Index(Worksheets("PROPHLINKS").Range("A1:T100"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)).Address)
'    End If
'  End With
End Sub

Private Sub UserForm_Initialize()
 ListBox1.List = Range("A1:A100").Value --->code runs when this is commented out, but Textbox1 is blank.  It should have already populated text in it.
TextBox2.AutoSize = True
'TextBox1.SetFocus
'TextBox1.SelStart = 0
'TextBox1.CurLine = 0
End Sub

All the code needs to do is to be able to know what cell in the sheet the text in Textbo1 comes from. That way, I can make notes
directly at the end of the text in Textbox1, and if the code works right, it is written back to the correct cell address. That's why I
keep wondering if there's some way to identify the cell address ($B$1) in this case in the code and say something like

Sheets("PROPHLINKS").Range("Bi"). value = Userform8.Tetxbox1.value

This just amounts to updating any sheet cell contents from a user form Textbox. Seems simple enough.
Will your code work in this situation ?

Flashbond, thanks again for your help in this.

cr

 

[Legacy 143009]

Ok this will behave like as you mentioned. I don't know how you can adapt this to your code:

Dim myAddress As Range

Private Sub TextBox1_Change()
  If Not myAddress Is Nothing Then
    myAddress.Value = TextBox1.Text
  End If
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  With Application
    If Not IsError(.Match(TextBox1.Text, .Index(Worksheets("Sheet1").Range("A1:D3"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)) Then
      Set myAddress = Worksheets("Sheet1").Range(Cells(.Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), .Match(TextBox1.Text, .Index(Worksheets("Sheet1").Range("A1:D3"), .Match(ListBox1.List(ListBox1.TopIndex), Columns("A"), 0), 0), 0)).Address)
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  ListBox1.List = Range("A1:A3").Value
  TextBox1.Text = "Beans"
End Sub