Hello all,

In every worksheet of my Excel file, I have Form type Text Boxes. I need
help with activating the Locked Text of those text boxes when I click on the
command button. Could you please help me?

Below is the code I currently have, and it's not working.

Dim objT As OLEObject
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
For Each objT In sh.OLEObjects
If objT.OLEType = xlOLEEmbed Then
objT.LockedText = True
End If
Next objT
Next sh

Thanks.

You are going to have to clarify this statement for us...

"I have Form type Text Boxes"

There are no TextBoxes on the Forms toolbar... only the Controls Toolbox
toolbar or the Drawing toolbar. So, where did you get your TextBox from?

--
Rick (MVP - Excel)


"Souny" wrote in message
...
Hello all,

In every worksheet of my Excel file, I have Form type Text Boxes. I need
help with activating the Locked Text of those text boxes when I click on
the
command button. Could you please help me?

Below is the code I currently have, and it's not working.

Dim objT As OLEObject
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
For Each objT In sh.OLEObjects
If objT.OLEType = xlOLEEmbed Then
objT.LockedText = True
End If
Next objT
Next sh

Thanks.

Rick,

Thanks for your response. I have this Excel file for years. I remember
correctly that I got those Text Boxes from the Forms toolbar. Now when I
look for it on the Forms toolbar, I do not find it. Those Text Boxes are not
from the Control Toolbox toolbar.

One of the differences between Forms Text Box and Control Toolbox Text Box
is Forms has a feather for Assign Macro and Control has a feather for View
Code on the right-click menu.

In any case, could you help me with the code?

When I click on the command button, I would like to have the Lock Text
activated. In the Format Text Box property window, there is a checkbox "Lock
Text". I would like that box to be checked when I click on the command
button.

Thanks.

"Rick Rothstein" wrote:

You are going to have to clarify this statement for us...

"I have Form type Text Boxes"

There are no TextBoxes on the Forms toolbar... only the Controls Toolbox
toolbar or the Drawing toolbar. So, where did you get your TextBox from?

--
Rick (MVP - Excel)


"Souny" wrote in message
...
Hello all,

In every worksheet of my Excel file, I have Form type Text Boxes. I need
help with activating the Locked Text of those text boxes when I click on
the
command button. Could you please help me?

Below is the code I currently have, and it's not working.

Dim objT As OLEObject
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
For Each objT In sh.OLEObjects
If objT.OLEType = xlOLEEmbed Then
objT.LockedText = True
End If
Next objT
Next sh

Thanks.


.

I have no idea how to reproduce your "Forms" TextBoxes in my copy of XL2003,
so (since you said they were not from the Control Toolbox) I used TextBoxes
from the Drawing toolbar and here is the code I came up with to change their
LockedText properties. If this doesn't work straightaway on your particular
TextBoxes, perhaps you can use it as a guide...

Sub TurnLockedTextOn()
Dim SH As Worksheet, TB As Shape
For Each SH In ActiveWorkbook.Worksheets
If SH.Shapes.Count Then
For Each TB In SH.Shapes
If TB.Type = msoTextBox Then
TB.ControlFormat.LockedText = True
End If
Next
End If
Next
End Sub

--
Rick (MVP - Excel)


"Souny" wrote in message
...
Rick,

Thanks for your response. I have this Excel file for years. I remember
correctly that I got those Text Boxes from the Forms toolbar. Now when I
look for it on the Forms toolbar, I do not find it. Those Text Boxes are
not
from the Control Toolbox toolbar.

One of the differences between Forms Text Box and Control Toolbox Text Box
is Forms has a feather for Assign Macro and Control has a feather for View
Code on the right-click menu.

In any case, could you help me with the code?

When I click on the command button, I would like to have the Lock Text
activated. In the Format Text Box property window, there is a checkbox
"Lock
Text". I would like that box to be checked when I click on the command
button.

Thanks.

"Rick Rothstein" wrote:

You are going to have to clarify this statement for us...

"I have Form type Text Boxes"

There are no TextBoxes on the Forms toolbar... only the Controls Toolbox
toolbar or the Drawing toolbar. So, where did you get your TextBox from?

--
Rick (MVP - Excel)


"Souny" wrote in message
...
Hello all,

In every worksheet of my Excel file, I have Form type Text Boxes. I
need
help with activating the Locked Text of those text boxes when I click
on
the
command button. Could you please help me?

Below is the code I currently have, and it's not working.

Dim objT As OLEObject
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
For Each objT In sh.OLEObjects
If objT.OLEType = xlOLEEmbed Then
objT.LockedText = True
End If
Next objT
Next sh

Thanks.


.

Rick,

Thank you very much for continuing to help me and for your code. Your code
works perfectly.

"Rick Rothstein" wrote:

I have no idea how to reproduce your "Forms" TextBoxes in my copy of XL2003,
so (since you said they were not from the Control Toolbox) I used TextBoxes
from the Drawing toolbar and here is the code I came up with to change their
LockedText properties. If this doesn't work straightaway on your particular
TextBoxes, perhaps you can use it as a guide...

Sub TurnLockedTextOn()
Dim SH As Worksheet, TB As Shape
For Each SH In ActiveWorkbook.Worksheets
If SH.Shapes.Count Then
For Each TB In SH.Shapes
If TB.Type = msoTextBox Then
TB.ControlFormat.LockedText = True
End If
Next
End If
Next
End Sub

--
Rick (MVP - Excel)


"Souny" wrote in message
...
Rick,

Thanks for your response. I have this Excel file for years. I remember
correctly that I got those Text Boxes from the Forms toolbar. Now when I
look for it on the Forms toolbar, I do not find it. Those Text Boxes are
not
from the Control Toolbox toolbar.

One of the differences between Forms Text Box and Control Toolbox Text Box
is Forms has a feather for Assign Macro and Control has a feather for View
Code on the right-click menu.

In any case, could you help me with the code?

When I click on the command button, I would like to have the Lock Text
activated. In the Format Text Box property window, there is a checkbox
"Lock
Text". I would like that box to be checked when I click on the command
button.

Thanks.

"Rick Rothstein" wrote:

You are going to have to clarify this statement for us...

"I have Form type Text Boxes"

There are no TextBoxes on the Forms toolbar... only the Controls Toolbox
toolbar or the Drawing toolbar. So, where did you get your TextBox from?

--
Rick (MVP - Excel)


"Souny" wrote in message
...
Hello all,

In every worksheet of my Excel file, I have Form type Text Boxes. I
need
help with activating the Locked Text of those text boxes when I click
on
the
command button. Could you please help me?

Below is the code I currently have, and it's not working.

Dim objT As OLEObject
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
For Each objT In sh.OLEObjects
If objT.OLEType = xlOLEEmbed Then
objT.LockedText = True
End If
Next objT
Next sh

Thanks.


.


.

Hi,

You are doing reference to "Microsoft Excel Dialog sheet 5"
On those sheets, it is possible to add Textboxes using
form tools bar.

Unfortunately, i do not know how to deal programmatically
with these objects. I may just give you some tips.

if you want to see their tab in your workbook
'----------------------------------
Dim Dial As DialogSheet
For Each Dial In DialogSheets
Dial.Visible = True
Next
'----------------------------------

Example : Suppose a dialog sheet having "Dialogue1" as caption

Here some lines of code that may help you !
'------------------------------------------------
Sub test()

Dim X As DialogSheet
Dim Sh As Shape

Set X = DialogSheets("Dialogue1")

X.Unprotect True
'to give a title to the dialog sheet
X.DialogFrame.Caption = "What a day!"

'Loop through all objects on this dialogsheet
For Each Sh In X.Shapes
'to affect only textbox (EditBox)
If TypeName(Sh.OLEFormat.Object) = "EditBox" Then
'if necessary
Sh.OLEFormat.Object.MultiLine = True
'affect creation mode only
'you can still modify text when showed
Sh.OLEFormat.Object.Locked = False
Sh.OLEFormat.Object.LockedText = False
'Add some text...
Sh.OLEFormat.Object.Text = "it works"
'affect dialogsheet when showed
Sh.ControlFormat.Enabled = True
End If
Next
X.Protect , DrawingObjects:=True, contents:=True
X.Show
'------------------------------------------------

Hi michdenis,

Thanks for your response. I am not familiar with "Microsoft Excel Dialog
sheet 5". Is it one of the references in VBA? How can I activate that?

Thanks.

"michdenis" wrote:

Hi,

You are doing reference to "Microsoft Excel Dialog sheet 5"
On those sheets, it is possible to add Textboxes using
form tools bar.

Unfortunately, i do not know how to deal programmatically
with these objects. I may just give you some tips.

if you want to see their tab in your workbook
'----------------------------------
Dim Dial As DialogSheet
For Each Dial In DialogSheets
Dial.Visible = True
Next
'----------------------------------

Example : Suppose a dialog sheet having "Dialogue1" as caption

Here some lines of code that may help you !
'------------------------------------------------
Sub test()

Dim X As DialogSheet
Dim Sh As Shape

Set X = DialogSheets("Dialogue1")

X.Unprotect True
'to give a title to the dialog sheet
X.DialogFrame.Caption = "What a day!"

'Loop through all objects on this dialogsheet
For Each Sh In X.Shapes
'to affect only textbox (EditBox)
If TypeName(Sh.OLEFormat.Object) = "EditBox" Then
'if necessary
Sh.OLEFormat.Object.MultiLine = True
'affect creation mode only
'you can still modify text when showed
Sh.OLEFormat.Object.Locked = False
Sh.OLEFormat.Object.LockedText = False
'Add some text...
Sh.OLEFormat.Object.Text = "it works"
'affect dialogsheet when showed
Sh.ControlFormat.Enabled = True
End If
Next
X.Protect , DrawingObjects:=True, contents:=True
X.Show
'------------------------------------------------

I proposed this explanation when i read this ;

"I remember correctly that I got those Text Boxes from
the Forms toolbar. Now when I look for it on the
Forms toolbar, I do not find it."

I really do not know if you have a Microsoft Excel Dialog sheet5
in your workbook... but if you add one of these sheets to your
workbook, you will find a "textbox control" in your form tools bar
to add in your sheet. In other circonstances, there is no textbox
available in Excel form tools bar.

Before Userform, old excel version used Microsoft Excel Dialog sheet 5.
For a reason of compatibility, there are still available.

A right clic on a tab of worksheet, choose "insert" command, and
in the opening window, you will have the opportunity to add
one of these sheets.

If your workbook has one of them, this sheet is probably hidden.
if so, you can unhide it with this macro :

As i said, i did a guess based of your comment.

'-----------------------------------
Sub Test()
Dim Dial As DialogSheet
For Each Dial In DialogSheets
Dial.Visible = True
Next
End Sub
'-----------------------------------



"Souny" a écrit dans le message de groupe de discussion
: ...
Hi michdenis,

Thanks for your response. I am not familiar with "Microsoft Excel Dialog
sheet 5". Is it one of the references in VBA? How can I activate that?

Thanks.

"michdenis" wrote:

Hi,

You are doing reference to "Microsoft Excel Dialog sheet 5"
On those sheets, it is possible to add Textboxes using
form tools bar.

Unfortunately, i do not know how to deal programmatically
with these objects. I may just give you some tips.

if you want to see their tab in your workbook
'----------------------------------
Dim Dial As DialogSheet
For Each Dial In DialogSheets
Dial.Visible = True
Next
'----------------------------------

Example : Suppose a dialog sheet having "Dialogue1" as caption

Here some lines of code that may help you !
'------------------------------------------------
Sub test()

Dim X As DialogSheet
Dim Sh As Shape

Set X = DialogSheets("Dialogue1")

X.Unprotect True
'to give a title to the dialog sheet
X.DialogFrame.Caption = "What a day!"

'Loop through all objects on this dialogsheet
For Each Sh In X.Shapes
'to affect only textbox (EditBox)
If TypeName(Sh.OLEFormat.Object) = "EditBox" Then
'if necessary
Sh.OLEFormat.Object.MultiLine = True
'affect creation mode only
'you can still modify text when showed
Sh.OLEFormat.Object.Locked = False
Sh.OLEFormat.Object.LockedText = False
'Add some text...
Sh.OLEFormat.Object.Text = "it works"
'affect dialogsheet when showed
Sh.ControlFormat.Enabled = True
End If
Next
X.Protect , DrawingObjects:=True, contents:=True
X.Show
'------------------------------------------------