MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Coding textboxes

  Asked By: Ernesta    Date: Nov 18    Category: MS Office    Views: 910

I have a section of a spreadsheet set up with labels and textboxes to
be printed as a letter sized form. I need to code the textboxes such
that if a corresponding cell has information the textbox will = the
cell value. I think I have this right:
here is an example
TextBox1_Change() = Worksheets("Sheet1").Range("A1").Value

I also have some that will act like checkboxes:
If Worksheets("Sheet1").Range("CL2").Value = 3 Then
TextBox24_Change() = "X"
TextBox24_Change() = ""

Are these coded correctly?

The code is currently stored in module 3 in the vba project. How do I
load this so that the data will show in the proper textboxes.

Sorry if this is an easy one, I am confused.



4 Answers Found

Answer #1    Answered By: Sherrie Thomas     Answered On: Nov 18

Assuming you have a textbox  called textbox1 which should be equal to cell  A1
and that it is a control (rather than a drawing object).

TextBox1_Change() is the name of a macro which is run each time the user
makes a change to the text box.
TextBox1.Value is the content of the box

So if you want to make the text box equal to cell A1 you would use

TextBox1.Value= Worksheets("Sheet1").Range("A1").Value

You might put that line in a macro called when the sheet is changed.
That macro, in the Sheet1 object starts
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

You would then need to test the target to see if its address was "A1" before
running the code.

However there is an easier way
In the properties of the text box there is a setting for LinkedCell. If
that is set  to A1 the text box will always contain the same value as A1.
Change the text box and the cell will change, change the cell and the
textbox will change.

Answer #2    Answered By: Anselma Schmidt     Answered On: Nov 18

With the second part, I would use a checkbox control rather than a text box.
I would link it to a cell  somewhere and in the linked cell put the formula
=CL2=3. This cell will show  true or false depending on the value of CL3 and
the checkbox will be ticked or clear accordingly. In this case you must
disable the checkbox as, if it is clicked the formula in the linked cell
will be overwritten.

Answer #3    Answered By: Dang Tran     Answered On: Nov 18

I am a bit confused  about using the checkbox. I have a paper form  that I need to
fill out based on information  I collect in the field. I am using a palm pilot to
gather information in the field. I then have to import a tab delimited file into
For some of the questions, I use checkboxes  on the palm pilot, the return value
in the imported file is 1, 2, 3, 4, 5 etc..., the number corresponds to the
checkbox that is chosen on the palm pilot. If the imported file gives the cell  a
value of 1, 2, 3, 4, or 5; can I just put the formula (=cl2=1 etc...) in the
corresponding checkbox in excel? I have tried it to no avail. Can I format the
checkbox (in excel) to be checked if the corresponding cell has a certain
number? ie. checkbox 1 is true if CL2=1, checkbox 2 is true if CL2=2, checkbox 3
is true if CL2=3, etc... .
Thanks for the help with the textboxes, they work just fine.

Answer #4    Answered By: Jamie Roberts     Answered On: Nov 18

If you are using an activex checkbox (from the controls toolbar In XLXP/2003
or from developer/insert/ActiveX Controls on the ribbon in 2007)
right click it to see properties.
Look for the property Linked Cell.
Link this to a blank cell  in your spreadsheet
Put the formula =cl2=1 in the blank cell
Repeat this for each checkbox using a different cell for each checkbox

You will have a group of cells which have the value true or false - each
cell drives one  checkbox.

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