Hi Asa and Sam Thanks for all the info. It will take me a couple of days to comprehend it all and then I will get back to you.
I really appreciate your help Regards Charlie On Tue, Feb 7, 2012 at 12:10 PM, Asa Rossoff <a...@lovetour.info> wrote: > Hi Charlie,**** > > I don't have a lot of practicle experience in all areas of Excel, so I > just realized there is an easier way to link controls to the worksheet!*** > * > > ** ** > > Simply use the ControlSource property of the control and provide a cell > reference or named range. See > http://www.ozgrid.com/Excel/free-training/ExcelVBA2/excelvba2lesson15.htm. > **** > > ** ** > > The downside to this method (and also to the exact solution I provided > below) is that the cell is updated immediately upon editing the value of > the control. Sometimes you want to wait until an entire form is completed > to update data on the worksheet, in which case you will have to use some > variation on the VBA method I described below. (And for posterity, like I > said in a separate thread, you might want to use ThisWorkbook instead of > the ActiveWorkbook that I used in Userform_Initialize if the linked cell > will always be in the same workbook as the userform).**** > > ** ** > > Asa**** > > ** ** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *Asa Rossoff > *Sent:* Saturday, February 04, 2012 5:38 PM > *To:* excel-macros@googlegroups.com > *Subject:* RE: $$Excel-Macros$$ Text Box - Properties**** > > ** ** > > 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 <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 > -- 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
