Solved

dynamically add web browser to a form in Excel vba

Posted on 2012-04-08
4
1,538 Views
Last Modified: 2012-04-09
I am trying to dynamically creat a form with a label, a CommandButton and a WebBrowser, bat can't get the syntax for the webBrowser right.

My code:
Sub MakeForm()
' Yahoo WebBrowser problem
'
' References:
' Visual Basic for Applications
' Microsoft Excel 14.0 Object Library
' OLE Automation
' Microsoft Visual Basic for Applications Extensibility 5.3
' Microsoft Forms 2.0 Object Library (FM20.dll)
' Microsoft Internet Controls

  Dim YahooChart As Object ' VBComponent
  Dim FormName As String
  Dim cb As MSForms.CommandButton
  Dim lb As MSForms.Label
  Dim wb As WebBrowser
  Dim TextLocation As Integer
  Dim x As Integer

  'Locks Excel spreadsheet and speeds up form processing
  Application.VBE.MainWindow.Visible = False
  Application.ScreenUpdating = False
 
'   Create the UserForm
  Set YahooChart = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
 
  'Set Properties for Form YahooChart
  With YahooChart
    .Properties("Caption") = "YahooChart"
    .Properties("Width") = 900
    .Properties("Height") = 700
  End With
  FormName = YahooChart.Name
'   Add a Label
  Set lb = YahooChart.Designer.Controls.Add("forms.Label.1")
  With lb
   .Left = 5
   .Width = 600
   .Caption = "URL"
  End With
 
'   Add a CommandButton
  Set cb = YahooChart.Designer.Controls _
    .Add("forms.CommandButton.1")
  With cb
      .Caption = "Done"
      .Left = 5
      .Top = 5
  End With
'   Add an event-hander sub for the CommandButton
  With YahooChart.CodeModule
'   ** Add/change next 5 lines
'       This code adds the commands/event handlers to the form
      x = .CountOfLines
      .InsertLines x + 1, "Sub CommandButton1_Click()"
      .InsertLines x + 2, "MsgBox ""Done"""
      .InsertLines x + 3, "Unload Me"
      .InsertLines x + 4, "End Sub"
  End With

' Add a WebBrowser
Set wb = YahooChart.Controls.Add("Shell.Explorer.2", "x", True)
With wb
  .Top = 10
  .Width = 780
End With
'   Show the form
  VBA.UserForms.Add(FormName).Show
'
'   Delete the form
  ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=YahooChart
End Sub
0
Comment
Question by:janand1
  • 2
  • 2
4 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37821656
Why aren't you using Designer for the WebBrowser?

You use it when adding the other controls.
0
 

Author Comment

by:janand1
ID: 37822526
I tried that but could not get it to work. Pleas give me the correcet syntax.
The syntax I am trying I found by using Google.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37823106
I used basically the same syntax as you used but with Designer.
  Set WB = TempForm.Designer.Controls.Add("Shell.Explorer.2", "x", True)

Open in new window

Actually Just realised, I also declared wb as Object since I wasn't sure what type of object it would be.

It does appear to be a WebBrowser in the Watch windows, but when I run the code I get a catastrophic error if I've declared it as that.

I also tried MSForms.Control and that worked fine, that's what I sometimes use anyway when creating forms - it allows me to reuse the same variable for textboxes, labels etc.
0
 

Author Closing Comment

by:janand1
ID: 37824639
Thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

743 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

12 Experts available now in Live!

Get 1:1 Help Now