0

I am working on a small excel project where I have a user form. The user form has a combo box that has a list of company names retrieved from column (A) in an excel worksheet (This works as expected).

The form has a text box, that depending on the selection from the drop down box returns the stock ticker from column B (Works as expected).

The next step is where it breaks down. The stock ticker value is then passed to a web query that connects to yahoo finance, and retrieves data from the site.

Problem 1: The web query does not return data until the form is closed. I want it to return the values "instantly."

Problem 2: Each time I run the query, a new query table is built, even though I have coded my script to delete query tables.

Private Sub cb_Stock_Name_Change()

Set ws = Worksheets("Stock_Info")

With Me
    .tb_ticker.Value = ws.cells(.cb_stock_name.ListIndex + 2, 2)
    '.TextBox3.Value = Format(Sheet1.cells(.ComboBox1.ListIndex + 7, 9), "0%")
    '.TextBox2.Value = Format(Sheet1.cells(.ComboBox1.ListIndex + 7, 10), "0%")
End With

Dim ticker As String
Dim conn As String

Set ws_query = Worksheets("Stock_Query")
ticker = tb_ticker.Value
conn = "URL;http://finance.yahoo.com/q?s=" & ticker
Dim qt As QueryTable

For Each qt In ws_query.QueryTables
    qt.Delete
Next qt

Set qt = ws_query.QueryTables.Add _
(Connection:=conn, Destination:=Range("A1"))

With qt
    '.Connection = conn
    '.Destination = Range("A1")
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "2"
    .Refresh
End With

With Me
    .tb_previous_close.Value = ws_query.cells(1, 2)
End With

End Sub

Questions: What is wrong with my code that is a) doesn't return until my form is closed b) doesn't delete the previous querytable?

Community
  • 1
  • 1
cquadrini
  • 769
  • 2
  • 12
  • 25
  • Possible solution to Problem 1 - Assuming that the form is `Modal` ... you would have to make it `ModeLess` ... `Userfomr1.Show VBModeless` – Pankaj Jaju Dec 18 '13 at 21:27
  • Hi Pankaj...here is other code: Open workbook `Private Sub Workbook_Open() UserForm1.Show End Sub` Form Initialize `Private Sub UserForm_Initialize() Dim rng_Stock_Name As Range Dim rng_Ticker As Range Set ws = Worksheets("Stock_Info") For Each rng_Stock_Name In ws.Range("Company_Name") Me.cb_stock_name.AddItem rng_Stock_Name.Value Next rng_Stock_Name End Sub` It is a UserForm and not a Module. Does this help? – cquadrini Dec 18 '13 at 21:29
  • As I mentioned... replace `UserForm1.Show` with `UserForm1.Show VBModeless` in `Workbook_Open()` – Pankaj Jaju Dec 18 '13 at 21:33
  • Hi Pankaj, I did add that piece of code as you suggested, but it still doesn't close the connection and return values until I close the form. – cquadrini Dec 18 '13 at 21:42
  • Hmm ... Remove the `userform1.show` from `workbook_open` and try to put `userform1.show VBModeless` in the `userform_initialize()`. – Pankaj Jaju Dec 18 '13 at 21:46
  • Pankaj, while that does fix problem 1, it creates problem 3. Now the form doesn't load when the workbook is opened. Can't do .Show in separate areas, that is a problem. The project is a gift for my nephew, so I want him to be able to open the excel and have the form show and be ready to go. – cquadrini Dec 18 '13 at 21:54
  • In that case, set the `ShowModal` property of the form as `False` in design mode and call `form.show` in `workbook_open` – Pankaj Jaju Dec 18 '13 at 21:59
  • Thanks Pankaj, problems 1 & 3 fixed. Thank you. Any good resources on Modal? I have never encountered this aspect of excel vba before. – cquadrini Dec 19 '13 at 01:24

1 Answers1

1

Problem 1 - resolved as per the comments (need to put form property as Modeless). You can check MSDN for details on form's mode properties. FYI, by default the forms are modal.

Problem 2 - You need to specify qt's .Name property. Sample this

With qt
    .Name = "StockWatch"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = "2"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = False
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

Let me know if this works for you

Pankaj Jaju
  • 5,371
  • 2
  • 25
  • 41