Dear all,

I've found a way to change the text *next to* a single checkbox on a worksheet with this code:

Sub change()
ActiveSheet.Shapes.Range(Array("Check Box 12")).Select
Selection.Characters.Text = "any_new_text"
End Sub

But If there are many textboxes in a file, on different worksheets, there must be a way to address them all in one go. I also managed to read the 'name' and 'alternative text' of shapes with this code:

Dim c As Shape
For Each c In Worksheets(1).Shapes
Debug.Print c.Name & " " & c.AlternativeText
Next c
End Sub

I guess there must be a way to change the text (which appears not to be the properties 'Name' or 'Alternativetext') of all shapes, but can't get my head around it. Anyone who can?

Regards,
Dr

Ehm, 'test' in the subject line should have been 'text'.

Dr


On 29-1-2013 14:26, lid wrote:
Dear all,

I've found a way to change the text *next to* a single checkbox on a worksheet with this code:
Sub change()
ActiveSheet.Shapes.Range(Array("Check Box 12")).Select
Selection.Characters.Text = "any_new_text"
End Sub

But If there are many textboxes in a file, on different worksheets, there must be a way to address them all in one go. I also managed to read the 'name' and 'alternative text' of shapes with this code:

Dim c As Shape
For Each c In Worksheets(1).Shapes
Debug.Print c.Name & " " & c.AlternativeText
Next c
End Sub

I guess there must be a way to change the text (which appears not to be the properties 'Name' or 'Alternativetext') of all shapes, but can't get my head around it. Anyone who can?

Regards,
Dr

.... and I'm working in Excel 2010.

Dr


On 29-1-2013 15:56, lid wrote:
Ehm, 'test' in the subject line should have been 'text'.

Dr


On 29-1-2013 14:26, lid wrote:
Dear all,

I've found a way to change the text *next to* a single checkbox on a worksheet with this code:
Sub change()
ActiveSheet.Shapes.Range(Array("Check Box 12")).Select
Selection.Characters.Text = "any_new_text"
End Sub

But If there are many textboxes in a file, on different worksheets, there must be a way to address them all in one go. I also managed to read the 'name' and 'alternative text' of shapes with this code:

Dim c As Shape
For Each c In Worksheets(1).Shapes
Debug.Print c.Name & " " & c.AlternativeText
Next c
End Sub

I guess there must be a way to change the text (which appears not to be the properties 'Name' or 'Alternativetext') of all shapes, but can't get my head around it. Anyone who can?

Regards,
Dr

could this help?
If c.Type = msoTextBox Then
Debug.Print c.Name & " " & c.AlternativeText
End If

rgds

wrote in message
...
... and I'm working in Excel 2010.

Dr


On 29-1-2013 15:56, lid wrote:
Ehm, 'test' in the subject line should have been 'text'.

Dr


On 29-1-2013 14:26, lid wrote:
Dear all,

I've found a way to change the text *next to* a single checkbox on a
worksheet with this code:
Sub change()
ActiveSheet.Shapes.Range(Array("Check Box 12")).Select
Selection.Characters.Text = "any_new_text"
End Sub

But If there are many textboxes in a file, on different worksheets,
there must be a way to address them all in one go. I also managed to
read the 'name' and 'alternative text' of shapes with this code:

Dim c As Shape
For Each c In Worksheets(1).Shapes
Debug.Print c.Name & " " & c.AlternativeText
Next c
End Sub

I guess there must be a way to change the text (which appears not to be
the properties 'Name' or 'Alternativetext') of all shapes, but can't get
my head around it. Anyone who can?

Regards,
Dr




--- news://freenews.netfront.net/ - complaints: ---

I'm afraid that doesn't work, because the texts *next to* the CheckBoxes (= shapes) are not msoTextBoxes, nor .Name, nor .AlternativeText. The texts I want to change are 'elements' of a Shapes.Range(Array("xxx")) object and can be selected with the .Select method, as can be seen in this working code snippet:

Sub change()
ActiveSheet.Shapes.Range(Array("Check Box 12")).Select
Selection.Characters.Text = "any_new_text"
End Sub

My purpose was to change all these 'elements' in one pass, but I don't know how to do that with VBA. I was thinking of a For... Next loop and would need som help to write that routine.

Kind regards
Dr



On 30-1-2013 15:50, Sheriff wrote:
If c.Type = msoTextBox Then
Debug.Print c.Name & " " & c.AlternativeText
End If