The Active X Text Box control can be linked to a cell, but I think it is
only available for use on a worksheet, not a userform. and everyone keeps
telling me to avoid Active X controls on worksheets like the plague, anyway.
To duplicate the linking behavior, you'll have to use a little VBA code.
For example, in your form code you could use:
Public TextBox1Cell As Range
Private Sub UserForm_Initialize()
Set TextBox1Cell = ActiveWorkbook.Sheets("Sheet1").Range("A1")
End Sub
Private Sub UserForm_Terminate()
Set TextBox1Cell = Nothing
End Sub
Private Sub UserForm_Activate()
TextBox1.Value = TextBox1Cell.Value2
End Sub
Private Sub TextBox1_Change()
TextBox1Cell.Value2 = TextBox1.Value
End Sub
If you also need the control on the form to update if the worksheet cell is
changed for some other reason while the form is still open (a calculation,
other code,.) then you can add code to your worksheet's Calculate and Change
events. If the cell will only contain a value and not a formula, you don't
need the calculate event.
Private Sub Worksheet_Change(ByVal Target As Range)
With UserForm1
If .Visible Then
If Not Application.Intersect(Target, .TextBox1Cell) Is Nothing
Then
.TextBox1.Value = .TextBox1Cell.Value2
End If
End If
End With
End Sub
Private Sub Worksheet_Calculate()
With UserForm1
If .Visible Then
If .TextBox1.Value <> .TextBox1Cell.Value2 Then
.TextBox1.Value = .TextBox1Cell.Value2
End If
End If
End With
End Sub
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Cab Boose
Sent: Saturday, February 04, 2012 4:27 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Text Box - Properties
Hi
I have a userform open with a 'text box' . Want to link with a cell in
sheet.
However the 'text box' properties does not show a 'linked cell' heading.
I am sure it use to. How do I restore the missing property or is there
another text box I should use.
Thks
Charlie
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
