Prev: CAN YOU SOLVE THIS!!!!!!!
Next: Get Picture from Access OLE Object Field
From: Souny on 12 Jan 2010 13:18
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.

From: Rick Rothstein on 12 Jan 2010 13:36
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" <Souny(a)discussions.microsoft.com> wrote in message
news:E6D5EA82-BEA7-4B08-95A8-2091DDCF5E18(a)microsoft.com...
> 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.
>

From: Souny on 12 Jan 2010 14:51
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" <Souny(a)discussions.microsoft.com> wrote in message
> news:E6D5EA82-BEA7-4B08-95A8-2091DDCF5E18(a)microsoft.com...
> > 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.
> >
>
> .
>
From: Rick Rothstein on 12 Jan 2010 15:18
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" <Souny(a)discussions.microsoft.com> wrote in message
news:CF546584-4D95-40EA-8E92-16D021B24B7E(a)microsoft.com...
> 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" <Souny(a)discussions.microsoft.com> wrote in message
>> news:E6D5EA82-BEA7-4B08-95A8-2091DDCF5E18(a)microsoft.com...
>> > 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.
>> >
>>
>> .
>>

From: michdenis on 12 Jan 2010 18:26
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
'------------------------------------------------
 |  Next  |  Last
Pages: 1 2
Prev: CAN YOU SOLVE THIS!!!!!!!
Next: Get Picture from Access OLE Object Field