Show Activecell.value when in vbmodeless?

richardcarter

Board Regular
Joined
Dec 10, 2003
Messages
77
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,753
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try using a Thisworkbook module like this:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With UserForm1
    .TextBox1.Text = ActiveCell.Value
End With
End Sub
 

richardcarter

Board Regular
Joined
Dec 10, 2003
Messages
77
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

Board Regular
Joined
Dec 10, 2003
Messages
77
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

Board Regular
Joined
Dec 10, 2003
Messages
77
Ah.. I figured it out.. I didn't put the code in a workbook module... Thanks, works perfect now!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,753
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,586
Messages
5,765,298
Members
425,271
Latest member
kristyfinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top