Text box alter automatic by code

PaulaM

New Member
Joined
Nov 3, 2005
Messages
1
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,167
Messages
6,170,470
Members
452,330
Latest member
AFK_Matrix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top