Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

dynamically add web browser to a form in Excel vba

Posted on 2012-04-08
4
Medium Priority
?
1,813 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
[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
  • 2
  • 2
4 Comments
 
LVL 34

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 34

Accepted Solution

by:
Norie earned 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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