Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

dynamically add web browser to a form in Excel vba

Posted on 2012-04-08
4
Medium Priority
?
1,876 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 35

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 35

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

885 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