VBA: Set Shockwave Flash Property as Variable using VLookup

[OneCurtis]

Hi, I'm creating a youtube video player with an embedded Shockwave Flash player, dropdown list and a range of video names and urls. However, I can't seem to Set the Shockwave Flash, having tried a number of methods.

Here's a picture of the sheet to help those with a visual preference - http://i.imgur.com/wLXah.png. The Vlookup range is hidden in two columns to the left.

I know I've probably made a small oversight (I don't have any experience manipulating these kind of objects) but here's the code I've got so far:

Sub PlaySamsMemes()

Dim sChosenTitle As String
Dim rngVideoData As Range
Dim MemePlayer As ShockwaveFlash
Dim lFinalRow As Long
Dim sLookupValue As String

lFinalRow = Cells(Rows.Count, 3).End(xlUp).Row
sChosenTitle = Range("F4").Value
Set rngVideoData = Range("B2:C62" & lFinalRow)
sLookupValue = Application.VLookup(sChosenTitle, rngVideoData, 2, False)

MemePlayer.SetVariable "Movie", sLookupValue

End Sub

It's returning Error 91 in Ex2010 and 424 in Ex2007 - Object variable or With block not set.

I've obviously tried the traditional 'Set' (obviously having modified the code from it's present state) to no avail and the macro recorder doesn't display anything when you change an Objects properties in the Ex interface, so I'm not sure what to do. Any help will be greatly appreciated. Thanks.

 

[OneCurtis]

Fixed it myself yesterday. Pretty simple really.

Set MemePlayer = Sheets(1).MemePlayer
MemePlayer.Movie = sLookupValue