Prev: CAN YOU SOLVE THIS!!!!!!!
Next: Get Picture from Access OLE Object Field
From: Souny on 12 Jan 2010 22:33
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" <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: Souny on 12 Jan 2010 22:35
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
> '------------------------------------------------
From: michdenis on 12 Jan 2010 23:42
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" <Souny(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion
: A54640ED-48C9-4329-8C1F-5129F9CE9F98(a)microsoft.com...
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
> '------------------------------------------------

First  |  Prev  | 
Pages: 1 2
Prev: CAN YOU SOLVE THIS!!!!!!!
Next: Get Picture from Access OLE Object Field