MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Geeske    Date: Feb 11    Category: MS Office    Views: 984

I'm having great difficulty doing something I feel ought to be quite simple!
I want to use a series of textboxes within a userform to display the
contents of about a dozen particular cells on a particular worksheet. The
is for a user to then alter the data (if necessary) and, on clicking a
command button, for the new data to be written back to the worksheet.

1 If I use the ControlSource property of the textBox control to specify
the appropriate cells, the userform correctly displays the data. However, any
changes that are made to the userform are automatically transferred to the
worksheet on hitting either the tab or enter keys. I only want the changes to
be made to the worksheet on the click of a command button.

2 If I don't use the ControlSource property, the userform displays
whatever values are held in (for example) TextBox1.Text (and Textbox1.Value),
what is on the worksheet. I have tried writing code such as Textbox1.Text =
Cells (1,2) but, although it does not generate an error, it is simply ignored.

In short, I effectively want to read a small number of cells, display the
values, and then give a user the opportunity to alter the values but for any
changes to be implemented to the worksheet only on the pressing of a command
button. As I am relatively new to VBA, I may be barking up the wrong tree
completely but any pointers anyone is willing to give are greatly appreciated.



4 Answers Found

Answer #1    Answered By: Haru Tanaka     Answered On: Feb 11

In option 1, that is by design.

In option 2, What happens if you use the UserForm.Repaint method in the
affected error handlers for these text boxes immediately after setting their

Answer #2    Answered By: Jacob Evans     Answered On: Feb 11

Your option 2 seems to work. I tried putting the code below in the
userform and it worked. It takes cell B5 of the active sheet and
places it in the text box.

Private Sub UserForm_Initialize()
TextBox1.Text = Cells(1, 2)
End Sub

Other things...
If the sheet is not active use:
TextBox1.Text = Worksheets("Sheet1").Cells(1, 2)
If you want to see the cell's formula (e.g. =1+1) use:
TextBox1.Text = Worksheets("Sheet1").Cells(1, 2).Formula

Answer #3    Answered By: Chaths Massri     Answered On: Feb 11

(1) is working as designed.

(2) should work fine, so you're getting something wrong. Post the entire code
module (or the relevant parts if it's too big).

Answer #4    Answered By: Dale Jones     Answered On: Feb 11

Regarding the problem below - thanks for your replies. In the end I used
Brad's suggestion re placing code under 'Private Sub Userform_Intialize'. To be
honest, I didn't know this existed but it worked. I am only a 'new' VBA user
and tend to jump in and locate those bits I need on an 'as and when' basis. I
use J Walkenbach's VBA for Dummies and a programmers reference manual but
perhaps someone could recommend a good introduction to structured programming.

Once again, thank you for your time in helping to solve my problem

Didn't find what you were looking for? Find more on Userforms Or get search suggestion and latest updates.