Text box alter automatic by code

[PaulaM]

I have 12 wide sheets each containing 6 text boxes at intervals along the top with the date in, in "February 2005" format.
Every year, I have to alter the year as well as the headings. The cells are easy, but how do I access each text box to alter the contents of the text box from "2005" to "2006" within code so as to save loads of time?
I tried SelectAll and played with some Shapes and Shape Range ideas but the text boxes seem to be named quite eratically and it seems impossible to write anything that works. I am using Excel 2000 and Visual Basic 6.
Thanks
Paula

 

[stdz]

Hello Paula,

Welcome to the board !

What dates are in the TextBoxes currently and how do they receive their dates ? (in design mode try right clicking one of them and select View Code)

Is the Year part (2005) always the current year ? If so, it would be possible to fill that part in programatically.

When TextBoxes are initially created on a sheet, they generally have names like: TextBox1, TextBox2, ....TextBoxn

The code below will put the current Month & Year ("November 2005") in TextBox1.

To test it, open a new workbook, go into Design Mode (click the icon with a Set Square, Ruler and Pencil, if not visible select View==>Toolbars==>Visual Basic) and drag a Textbox onto the sheet (it's name should be automatically TextBox1)

Right Click the Sheet Tab and select View Code and paste the code below:

Private Sub Worksheet_Activate()

TextBox1.Value = Format(Date, "mmmm yyyy")

End Sub

Everytime the worksheet is activated (switch between worksheets to test this) it will put the current Month & Year in TextBox1. Maybe you can use this as a starting point.

Hope that helps a bit !

Ciao,

Don.