Show Activecell.value when in vbmodeless?

[richardcarter]

I have a userform which contains a text box which is used for data entry purposes. The form is always open (i.e. running in vbmodeless) and I am trying to get the text box to display the 'active cell value' (using Text = ActiveCell.Value), However, whenever I move the cursor to a new cell, the textbox on the userform doesn't update. I can only assume this is because the userform is running in vbmodeless and needs to be closed and then re-opened again to update itself. Anyone know a way to get around this?

 

[JoeMo]

Try using a Thisworkbook module like this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With UserForm1
    .TextBox1.Text = ActiveCell.Value
End With
End Sub

 

[richardcarter]

Thanks for the reply.. but I'm struggling to get it to work. My userform is called "TP_InputBox" and the Textbox is called "TP1". Using your example, I've got:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Show_TP_InputBox
.TP1.Text = ActiveCell.Value
End With
End Sub


Not sure if it has anything to do with it, but the code which Im using to bring the userform up initially is as follows:
Sub Show_TP_InputBox()
TP_InputBox.Show (vbModeless)
End Sub

 

[richardcarter]

Just an addendum to my last post, I also have some code which intitilaizes a lookup list when the userform is opened, and the following code is what I was using originally.. so i think its the location of the new code which is causing me the problems..

Private Sub UserForm_Initialize()
StockCode.List = Range("Ticker2").Value
TP1.Text = ActiveCell.Value
End Sub

 

[richardcarter]

Ah.. I figured it out.. I didn't put the code in a workbook module... Thanks, works perfect now!

 

[JoeMo]

Ah.. I figured it out.. I didn't put the code in a workbook module... Thanks, works perfect now!

You are welcome - thanks for your reply and making the effort to sort it out.