Create a user form with two text boxes (tbxFormula and tbxValue) and
one command button (btnClose). Then paste in all of the following code
into the form's code module. The show the form modelessly with code
like

Sub ShowTheForm
UserForm1.Show vbModeless
End Sub


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''
Option Explicit
Private Declare Function FindWindowEx Lib "user32" Alias _
"FindWindowExA" ( _
ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA"
( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long
Private Const WM_SETFOCUS = &H7
Private WithEvents WS As Excel.Worksheet

Private Sub btnClose_Click()
Me.Hide
End Sub

Private Sub UserForm_Activate()
SetText
SetSheetFocus
End Sub

Private Sub UserForm_Initialize()
Set WS = ActiveSheet
SetText
SetSheetFocus
End Sub

Private Sub WS_SelectionChange(ByVal Target As Range)
SetText
End Sub

Private Sub SetText()
If ActiveCell.HasFormula = True Then
Me.tbxFormula.Text = ActiveCell.Formula
Else
Me.tbxFormula.Text = vbNullString
End If
Me.tbxValue.Text = CStr(ActiveCell.Value)
End Sub

Private Sub SetSheetFocus()\
Dim HWND_XLDesk As Long
Dim HWND_XLApp As Long
Dim HWND_XLSheet As Long
HWND_XLApp = Application.hwnd
HWND_XLDesk = FindWindowEx(HWND_XLApp, 0&, "XLDESK", vbNullString)
HWND_XLSheet = FindWindowEx(HWND_XLDesk, 0&, "EXCEL7",
ActiveWindow.Caption)
SendMessage HWND_XLSheet, WM_SETFOCUS, 0&, 0&
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]








On Fri, 12 Mar 2010 11:01:46 -0500, Fred Holmes wrote:

I'd like to create a UserForm to be used as a "reader" for the active
cell. I have created UserForm1 with two text boxes on it, with the
following code:

Private Sub UserForm_Initialize()
TextBox1.text = ActiveCell.Value
TextBox2.Text = ActiveCell.Formula
End Sub

But when the UserForm is shown, it has the focus, and the focus is in
text box 1. I want to keep the focus on the worksheet, and edit and
use the WorkSheet in the "usual" manner, with the UserForm only
showing the contents of the ActiveCell actively.

And once the above is accomplished, the Userform must update
automatically with change in the active cell -- either content or
location.

How might this be done?

TIA

Fred Holmes

The font/typface in the text boxes will be larger.

The text boxes will wrap text multiline in order to show verbose
content fully.

The text boxes will be wider (characterwise) than the column width of
the active cell, in many instances.

The text boxes may perform additional functions, as I get ideas. Or I
could add other objects to the UserForm.

The worksheet formula editing bar does some of the above, but it
doesn't magnify the text. The userform can be put anywhere, while the
formula editing bar will expand to block the view of the top line on
the worksheet.