Hi Asa
Thankyou very much for the code. Magic. Will be using the info you
provided. Will include in ;my library.
Another similiar question being posted shortly re vlookup in text box,
another little project.
Thanks
Charlie
On Sun, Feb 5, 2012 at 2:37 PM, Asa Rossoff <a...@lovetour.info> wrote:
> 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
>
--
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
