Insert "more controls" in excel developer tab sometimes doesn't work.
Excel developer tab allows me to "insert" worksheet objects like command boxes and buttons. The "insert" submenu has 11 "standard" Active X Control icons and a 12th icon that reveals dozens of "more controls".
Many of those extra controls (like ButtonBar Class) work just fine, but many, such as "Microsoft Outlook Body Control" result in an error message: "can't insert object". How can I get those to also work?
Here are more details about my specific needs, but I will award points for even small progress towards my final goal.
---- details -----
Excel's standard textbox has properties like mybox.TextEffect.Text which contain unformatted version of the textbox contents.
Today's question is how to give my Excel worksheet a "better textbox" where vba can easily access the formatted text. In other words a vba property like mybox.HTMLText or .HTMLBody.
(My final goal is to send an outlook email, but I already thoroughly understand those steps. (something like myMailItem.HTMLBody = myVbaMagic.HtmlText)
So can anybody tell me how to use Excel Developers tab's "more controls" to create a control that has an HTML property. My first guess was to try an Microsoft Outlook Body Control, but that does not seem to work. I am not wedded to the Outlook Body Control -- ANY control with an HTMLProperty would probably work. For instance, I note that MSForms library contains classes like htmltext and htmltextarea. If I could get those to appear on my worksheet, they might work just as well.
My current attempt using Excel's Developer Tab goes like this
1) Controls Group > Insert > Active X submenu > More Controls (which is at the bottom right of the submenu)
2) scroll down to Microsoft Outlook Body Control
3) click OK then draw a box on my spreadsheet.
When I release the mouse, I get a message "cannot insert control". How do I get around that?
My workbook already has a reference to Microsoft Outlook Objects, so I don't think that is the problem
Many of those extra controls (like ButtonBar Class) work just fine, but many, such as "Microsoft Outlook Body Control" result in an error message: "can't insert object". How can I get those to also work?
Here are more details about my specific needs, but I will award points for even small progress towards my final goal.
---- details -----
Excel's standard textbox has properties like mybox.TextEffect.Text which contain unformatted version of the textbox contents.
Today's question is how to give my Excel worksheet a "better textbox" where vba can easily access the formatted text. In other words a vba property like mybox.HTMLText or .HTMLBody.
(My final goal is to send an outlook email, but I already thoroughly understand those steps. (something like myMailItem.HTMLBody = myVbaMagic.HtmlText)
So can anybody tell me how to use Excel Developers tab's "more controls" to create a control that has an HTML property. My first guess was to try an Microsoft Outlook Body Control, but that does not seem to work. I am not wedded to the Outlook Body Control -- ANY control with an HTMLProperty would probably work. For instance, I note that MSForms library contains classes like htmltext and htmltextarea. If I could get those to appear on my worksheet, they might work just as well.
My current attempt using Excel's Developer Tab goes like this
1) Controls Group > Insert > Active X submenu > More Controls (which is at the bottom right of the submenu)
2) scroll down to Microsoft Outlook Body Control
3) click OK then draw a box on my spreadsheet.
When I release the mouse, I get a message "cannot insert control". How do I get around that?
My workbook already has a reference to Microsoft Outlook Objects, so I don't think that is the problem
Martin Liss
Some controls including MSForms library are not meant for end-user development, and I don't believe they are distributable. To do what you want with a textbox I think you'll need to create a custom control. If you've never created a custom control I can supply the code for one that adds some extra properties to a textbox. Here's a link to an article of mine that describes one that I created. Note that apparently the link in that article to the code doesn't work anymore.
ASKER
Some controls including MSForms library are not meant for end-user development
Ok, but, I already said I am not restricting my solution to just Microsoft Outlook Body Control. ANY solution will be acceptable.
Your idea of using a custom control is good, but where can I find a repository for previously create custom controls?
In theory, I could create my own control, but my client would not pay for the seriously heavy lifting needed to create such a control.
bob(rberke)
P.S. You said
apparently the link in that article to the code doesn't work anymore. In reality the article points to a dead link URL: http://speedy.sh/cHef2/NumberBox-V1.16.zip
I found a alternate version of NumberBox-V1.16.zip on the web and you are correct, it does not work.
I extracted it to c:\mytest\numberbox\
When I use "more controls" > register custom > & browse to "Numberbox.ocx" I get an
error message ==> Cannot register this control.
But, even if it did work, it would take a whole bunch of work to convert a number box control into a control that allowed formatted text to be converted to HTML code.
ActiveX controls aren't meant for use on worksheets. However some work OK, some work bizarrely (see this article and some don't work at all. Would using a userform be an option for you? I ask because the Microsoft Rich Textbox control does offer text formatting and you and you can use it in a userform but not on a worksheet.
ASKER
Yes, a userform would be OK. When user opened workbook, application_workbook would open the form, and initialize the richtextbox from cell A1 in a hidden worksheet.
So the form would contain a fully formatted sample of text, that the user could modify as desired. After every modification, the rich text would be stored into a1 of the hidden worksheet.
I can handle most of the details, but if you could supply a sample form with rich text it would be most helpful.
bob.
P.s. You say "ActiveX controls aren't meant for use on worksheets." I often wonder why Microsoft would show so many things under "more controls" when they cannot be use.
And of course there are 11 "standard" active x controls which are alternatives to the older form controls. I prefer the older controls, but there are lots of people that prefer the Active X.
So the form would contain a fully formatted sample of text, that the user could modify as desired. After every modification, the rich text would be stored into a1 of the hidden worksheet.
I can handle most of the details, but if you could supply a sample form with rich text it would be most helpful.
bob.
P.s. You say "ActiveX controls aren't meant for use on worksheets." I often wonder why Microsoft would show so many things under "more controls" when they cannot be use.
And of course there are 11 "standard" active x controls which are alternatives to the older form controls. I prefer the older controls, but there are lots of people that prefer the Active X.
ASKER
I just read your excellent article and see you are one of those that prefer Active X. That is good stuff.
Thanks. Feel free to mark one (or more) as useful.
I had to rebuild my hard drive recently and now unfortunately I don't seem to be able to use a RTB in a userform anymore and I know I have. Here's a video of one in use.
ASKER
I am getting closer to my goal with a Microsoft InkEdit Control. It "sort of" generates rich text but, I do not know how to convert it to outlook. I thought I already thoroughly understood the outlook steps, but I am missing something.
Open Outlook, then Open the attached Excel file. Double click in the template and you will get.
You will get the error -1837039617 (9280ffff) the operation failed.
This occurs at line2: olitem.rtfbody = myInkEdit.textbody
Open Outlook, then Open the attached Excel file. Double click in the template and you will get.
You will get the error -1837039617 (9280ffff) the operation failed.
This occurs at line2: olitem.rtfbody = myInkEdit.textbody
' this code is in Sheet1
Private Sub InkEdit1_DblClick()
Call MassSendBody(Me.InkEdit1)
End Sub
' this code is in a standard module
Sub MassSendBody(myInkEdit As Object)
Dim olApp As Object
Dim olItem As Outlook.MailItem, subj As String
Set olApp = outlookApp
If olApp Is Nothing Then
MsgBox "please open outlook before running this routine"
Exit Sub
End If
' for debugging only: to prevent "clutter" delete the previously created email
Dim oInspector As Inspector
Set oInspector = olApp.ActiveInspector
If Not oInspector Is Nothing Then oInspector.CurrentItem.Delete
Set olItem = olApp.CreateItem(olMailItem)
With olItem
.Subject = "test " & Now()
.BodyFormat = olFormatRichText
Debug.Print myInkEdit.TextRTF
line2: .RTFBody = myInkEdit.TextRTF
.Display
Stop
End With
exit_routine:
' olItem.send ' <==== this line is commented out to assist in debugging
AppActivate "Test" ' "bring to top" on the email
End SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, I'll be a monkey's uncle !!!! That worked !. (I was on the verge of giving up)
There are still a few hurdles to overcome, but now I can move forward.
My application will present the user with a standard text box which allows many (but not all) formats to be chosen. When the use clicks "Send", I will do something like
Thanks for your help.
There are still a few hurdles to overcome, but now I can move forward.
My application will present the user with a standard text box which allows many (but not all) formats to be chosen. When the use clicks "Send", I will do something like
application.shapes("StandardTextbox").interior.copy
application.shapes("InkEditTextbox").interior.paste
.Body = StrConv(myInkEdit.TextRTF, vbFromUnicode).Thanks for your help.
ASKER
Couldn't have done it without Martin Liss !!!
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015