UserForm as Reader

[Fred Holmes]

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.

 

[Per Jessen]

Hi Fred,

In userform properties set ShowModal to False.

To 'update' the userform, insert this code in the codesheet for your
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.TextBox1.Text = ActiveCell.Value
UserForm1.TextBox2.Text = ActiveCell.Formula
End Sub

Regards,
Per

 

[Fred Holmes]

The update UserForm on selection change code doesn't seem to work.
Nothing happens. The data from the original active cell when the
UserForm was shown remains showing. The UserForm1 (Renamed UserForm99
so there is no confusion with any other UserForm, active or not) is in
PERSONAL.XLS so that it will be available in any worksheet I'm using
without modifying the working (active) worksheet. However, the
selection chage (userform update) function doesn't even work for data
in PERSONAL.XLS. Nothing happens.

Thanks,

Fred Holmes

 

[Chip Pearson]

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]