Solved

Insert "more controls" in excel developer tab sometimes doesn't work.

Posted on 2016-09-16
12
56 Views
Last Modified: 2016-09-21
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
0
Comment
Question by:rberke
  • 6
  • 6
12 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802570
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.
0
 
LVL 5

Author Comment

by:rberke
ID: 41803101
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41803121
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.
0
 
LVL 5

Author Comment

by:rberke
ID: 41803162
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.
0
 
LVL 5

Author Comment

by:rberke
ID: 41803165
I just read your excellent  article and see you are one of those that prefer Active X.  That is good stuff.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41803199
Thanks. Feel free to mark one (or more) as useful.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 41803323
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.
0
 
LVL 5

Author Comment

by:rberke
ID: 41809483
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

' 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 Sub

Open in new window

Mass-Mail-Template-v4-inedit.xlsm
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41809613
I commented out line 19 and replaced it with line 20.
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
   '.RTFBody = myInkEdit.TextRTF
   .Body = StrConv(myInkEdit.TextRTF, vbFromUnicode)
   
         .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 Sub

Open in new window

0
 
LVL 5

Author Comment

by:rberke
ID: 41809739
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
application.shapes("StandardTextbox").interior.copy
application.shapes("InkEditTextbox").interior.paste
.Body = StrConv(myInkEdit.TextRTF, vbFromUnicode).

Open in new window

I have not yet figure out the exact syntax, but I will post a separate question for that.

Thanks for your help.
0
 
LVL 5

Author Closing Comment

by:rberke
ID: 41809743
Couldn't have done it without Martin Liss  !!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41809746
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Use email signature images to promote corporate certifications and industry awards.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now