PDA

View Full Version : Solved: controls combobox selection activating WebBrowser control.



gringo287
07-04-2012, 04:33 PM
Hi,

I know how to set up a WebBrowser and im "getting there" with comboboxes.

Im trying to combine them both to essentially take out the need for users to search through catagories of videos from youtube. Ill have a set list of video links (that will eventually increase/change) that will be in seperate catagories. my aim is the the user to select the e.g cooking sheet and then select from the combobox to view the desired video.

Ive tried a few variations but cant get it to work. the below is a "search" button selection after selecting the the desired vid.

Sub WebBrowser_Select()
If sheet5.ComboBox1 = ("blah") Then
link_name = Sheets("sheet5").Range("B66")
Call Sheets("sheet5").WebBrowser1.Navigate(link_name)
End If

End Sub


Any takers :dunno

gringo287
07-12-2012, 10:16 AM
anyone?

CodeNinja
07-12-2012, 12:43 PM
gringo287,
Not quite sure what you want exactly (it would help if you provided a sample workbook with a small data set), but I think if you are trying to open the website, the line:

Call Sheets("sheet5").WebBrowser1.Navigate(link_name)

might work better if it was:

ActiveWorkbook.FollowHyperlink (link_name)

If you provide a sample workbook, I (everyone else) may be able to see what you are doing and provide a more tailored solution for you.

gringo287
07-12-2012, 02:27 PM
Hi

Ive just knocked this together now. my copy is a bit all over the place and would probably confuse things at the moment. i want to take this one obstical at a time. im going to end up adding catagories etc but just getting this working for now will help me hopefully solve the reset myself. please note that only the first of the ten "op" buttons is a pop out link as i didnt have the time to get all ten. the links are random by the way.


in case its not clear from the file, my aim is essentially to use a combox to provide the selection of which link is played, instead of lots of individual buttons. As a bonus, it would be awesome if there was no need to even have a "Search" button and literally have jsut the selection its self start the clip playing.

hoped this helps and many thanks in advance :thumb

CodeNinja
07-13-2012, 07:20 AM
Gringo287,
Ok, I think I see what you are trying to do... A few recommendations:

First, you are using form controls not activeX controls. The activeX controls in my humble opinion are easier to use and more accessable (you can see properties and call them easier etc). I am not sure what version of excel you are using, but if it is 2007 and beyond, you are much better off using the activeX controls.

Second, you need to fill your combo box with choices. This can be done on workbook open or on sheet activation or by clicking an update button. I used workbook open to keep it the same design as what you had. Note: I am filling the data with column 1 which is op1 etc... this way you can have the name of the video not the url showing up in the combobox. I just search for it later and offset to the url. Anyway, the code looks like this:

Private Sub Workbook_Open()
Dim l As Long
Dim s As String

sheet1.combobox2.clear
For l = 4 To Sheet1.Range("A65536").End(xlUp).Row
s = Sheet1.Cells(l, 1)
Sheet1.ComboBox2.AddItem (s)
Next l

End Sub
Third: Now that you have the combobox filled, you need to add code to the command button to navigate to the website. I chose to use activeworkbook.followhyperlink because you dont need any references etc and it is easy to use. Again, note that I am offsetting the found selection so you can use a name and send the url. You could change this to suit your needs. That looks like this:

Private Sub Workbook_Open()
Dim l As Long
Dim s As String

For l = 4 To Sheet1.Range("B65536").End(xlUp).Row
s = Sheet1.Cells(l, 2)
Sheet1.ComboBox2.AddItem (s)
Next l

End Sub
To make everything a little more understandable, I have attached a working workbook that I think does what you want...

Good luck to you.

gringo287
07-13-2012, 10:14 AM
CodeNinja,
thank for the work you put in there, your code is useful in two pretty cool ways that i didnt even realise i needed :whistle:.

However im struggling to shoehorn a way to incorporate

link_name = Sheets("sheet1").Range("B4")
Call Sheets("sheet1").WebBrowser1.Navigate(link_name)

into

Sub test2()
Dim s As String
s = Sheet1.ComboBox2.Value
Dim rng As Range
Dim lastcell As Range

Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A65536").End(xlUp).Row)
Set lastcell = rng.Cells(rng.Cells.Count)

If s = "" Then
MsgBox ("You must select a video")
Else
s = rng.Find(what:=s, after:=lastcell).Offset(0, 1)
ActiveWorkbook.FollowHyperlink (s)
End If

End Sub

so that "test2" plays the clip thyough the WebBrowser control in the sheet, instead of navigating to a new browser

CodeNinja
07-13-2012, 01:03 PM
Gringo287,
Found a very odd problem... When I activate the Microsoft Web Browser control, it messes with your other controls on the sheet. I am not quite sure how to handle it. When I add the items onto the sheet everything works, but when I save it and re-open, I get all kinds of issues. I am not sure if I should save it as a different file type or what. Perhaps someone else here can help, but I am at a loss.

What I did to get it to work (before save and re-open) is as follows:

1- create controls and add to page... You need a combobox, a Web Browser control and a command button to select.

2- added code above, but changed line:

ActiveWorkbook.FollowHyperlink (s)

to:

Sheet1.WebBrowser1.Navigate s

3: I put the open workbook code in a separate macro to load the data into the combobox. I loaded the data in the combobox by running the macro and then tested by clicking on various combobox choices. They have the web browser control navigate correctly.


When I saved and loaded the program which was working, I get errors and it seems I cannot find the combobox, Command Button or web browser.

If anyone has any idea to the reason this is not working when saved and reloaded I would love to know.

Sorry, but that is the best I can do.

gringo287
07-13-2012, 01:50 PM
Oh my god, how annoying is that!. ive used the WebBrowser control before on a different project and also have it set up on my copy of that im working on for this project and have no issues when re opening. i really appreciate the work you have put in CodeNinja and sorry to puzzle you. Im going to have a tinker with it now, but hopefully someone else is feeling lucky

CodeNinja
07-13-2012, 01:55 PM
Well, maybe if you are not having trouble saving and opening it, it will work ok for you. Maybe it is some configuration in my computer...

Did you try changing the line

ActiveWorkbook.FollowHyperlink (s)

to:

Sheet1.WebBrowser1.Navigate s

???

gringo287
07-13-2012, 02:08 PM
no, im having the same issue as you. i think ive sussed whats causing it though (sort of). ive noticed that so long as you click "test" button to clear the combobox it works fine. the problem is, that leaves it open to user error and will cause them to ge the run time error if they forget to press it. it seems to be the below line thats causing it, as it just seems to keep reverting the combobox to show the url instead of the "op" after re opening, unless you click "test"??

s = rng.Find(what:=s, after:=lastcell).Offset(0, 1)

gringo287
07-13-2012, 02:09 PM
sorry is didnt answer your question. yes i used your suggestion


Did you try changing the line

ActiveWorkbook.FollowHyperlink (s)

to:

Sheet1.WebBrowser1.Navigate s

???

CodeNinja
07-13-2012, 02:15 PM
no, im having the same issue as you. i think ive sussed whats causing it though (sort of). ive noticed that so long as you click "test" button to clear the combobox it works fine. the problem is, that leaves it open to user error and will cause them to ge the run time error if they forget to press it. it seems to be the below line thats causing it, as it just seems to keep reverting the combobox to show the url instead of the "op" after re opening, unless you click "test"??

s = rng.Find(what:=s, after:=lastcell).Offset(0, 1)

I am not sure what you are saying. Did you create a "Test" button that resets the combobox? Can this be solved with error handling?

Ie:
on error goto FIX

FIX:
call macro for test button
resume

gringo287
07-13-2012, 02:23 PM
To be honest, im going to have to show (if its not already really obvious) inexperience now and admit that the code in your module 1, did thow me a little, but i just added it to a second button. so i have one that selects the clip "test 2" and one that clears the combobox "test". am i being really dense here :doh:


Sub test()
Dim l As Long
Dim s As String
Sheet1.ComboBox2.Clear
For l = 4 To Sheet1.Range("B65536").End(xlUp).Row
s = Sheet1.Cells(l, 1)
Sheet1.ComboBox2.AddItem (s)
Next l

End Sub

CodeNinja
07-13-2012, 02:59 PM
What runtime error are you getting and exactly where is it breaking?

I put in: If s = "" Then
MsgBox ("You must select a video")
to avoid those errors.

If you really want to be sure not to get an error, try using this modification to sub test2():

Sub test2()
Dim s As String
s = Sheet1.ComboBox2.Value
Dim rng As Range
Dim lastcell As Range

Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A65536").End(xlUp).Row)
Set lastcell = rng.Cells(rng.Cells.Count)

If s = "" Then
MsgBox ("You must select a video")
Else
If Not rng.Find(what:=s, after:=lastcell) Is Nothing Then
s = rng.Find(what:=s, after:=lastcell).Offset(0, 1)
Sheet1.WebBrowser1.Navigate s
End If
End If

End Sub

gringo287
07-13-2012, 03:18 PM
What runtime error are you getting and exactly where is it breaking?

Its not so much a matter of where its breaking. basically when the sheet is reopened, the combobox doesnt offset, so it shows the url and not the "op", which means that "test 2" click fails.

CodeNinja
07-13-2012, 03:21 PM
OH!!!! thats an easy fix... look in workbook_open()

Change it to this:

Private Sub Workbook_Open()
Dim l As Long
Dim s As String

For l = 4 To Sheet1.Range("A65536").End(xlUp).Row
s = Sheet1.Cells(l, 1)
Sheet1.ComboBox2.AddItem (s)
Next l

End Sub

That was just a dumb mistake I didn't correct as I was playing with this while doing other work.

I'm out for the weekend, but that should fix that problem for you. Have a good weekend.

gringo287
07-13-2012, 03:32 PM
you the man!, thank you CodeNinja. Enjoy your weekend