Solved

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

Posted on 2016-09-16
12
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 48

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 48

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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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 48

Expert Comment

by:Martin Liss
ID: 41803199
Thanks. Feel free to mark one (or more) as useful.
0
 
LVL 48

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 48

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 48

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

635 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