Text box question

Hello Guys:

I need some help.

I have Excel 2000 & have a workbook in which it has various worksheets with a text box where I write the same name & address in all sheets. How could I go about writting it in the first sheet & then it writes it automatically in the others?

Any HELP would be greatly appreciated.

THANKS

Hello Macaco,

Do you want the text in the one text box to automatically fill the other text boxes with the same data? If so, couldn't the text just be copied from the first text box to the correct cells in the other sheets?

Sincerely,
Leith Ross

Hello Leith:

What you are proposing is exactly what I am doing now, but I was just wondering if there is a way to do it automatically after you put it in the 1st.

I do know how to do it if it is from a single cell to another cell, but I have not been able to figure out how to do it from a TEXT BOX to another TEXT BOX thou.

THANKS

Hello Macaco,

The following code will automatically change all the other text boxes as you type in the main text box. In this code the main sheet is "Sheet1" and the main text box is named "TextBox1". You can change these to match your code. Place the following code in a Standard VBA module.

Please Login or Register  to view this content.

Place this code on "Sheet1" for "TextBox1"

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hello Leith:

First of all THANK YOU for your replies, I do appreciate them.

Second, my knowledge of Excel is limited to the very BASIC of things, so I would need a little bit more explanation on how to go about what you are telling me to do. I don't have any idea of what you are telling me to do.

THANKS again.

Hello Macaco,

Here are the directions. The first part is for the Standard Module code "Update TextBoxes". The second part is for the "TextBox1_Change()" code.

Adding the Macro UpdateTextBoxes
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

TextBox1_Change() code
How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Hello Leith:

THANKS again for your HELP.
I did everything as per last email & I am getting a Compile error, User Defined type not defined.

The 1st line is YELLOW
The 5th line is all BLUE

My text boxes are all TEST BOX 1
Sheets are Sheet 1, 2 & so forth

I don't know where to go from here. I hate to be you know what, but if you could guide me a little bit more & think we could get it done.

AGAIN THANKS

Hi Macaco,
I copied Leith's code directly as he instructed and it seems to be working fine (see attachment).
Are you sure about your textbox name (Click on it and the name appears where the Cell ID usually does)?

I am using Excel 2000 also

ChemistB

Hi Chemist:

Obviously I am doing something wrong. My text boxes somehow are staying open, by that I mean when I click on them I still can resize & the outlines show. Yours are closed or locked or whatever.

As I said earlier, I just know the very basics & would LOVE to have it work as the example that you sent me (Chemist). What am I doing wrong???

I know that we can get it if you all help me & THANKS to all.

Go to View>Toolbars>Control Toolbox and if the first icon (the one with the ruler, triangle and pencil) is depressed, click it so that it is not depressed. I believe that is your problem.

ChemistB

Hi Chemist:

I knew we could do it

It is working now, but that was not the problem thou. As I was doing what you told me, I found that I was using the WRONG Text box. I was doing it all along with the Drawing panel text box & apparently it doesn't work with that one. So when I did it with Controlbox it works.

I do have a question, is there any way to put a colored background in it?

CHEMIST, THANK YOU for your input, I do appreciate it.

I do have a question, is there any way to put a colored background in it?

Yes, first get that control toolbox showing again and click on the first icon (Design mode), then select your textbox. Go back to the toolbar and click on Properties icon (the second icon). Find "Background Color" and click on the dropdown which opens a menu with two tabs, choose the palette tab and choose your background color.

ChemistB