Use dropdown list to open a URL in a worksheet

[jmcconnell]

I've currently got an Active X combobox contained in a spreadsheet.

• Combobox is called SiteList
• Combobox is poulated via listfill - The list of data is in column A of a worksheet called 'website'
• The combobox itself is contained within a worksheet called' SiteShortcuts'
• there is a button below the combobox called 'GotoSite'
• Default web browser is Chrome

the combobox currently contains around 30 options in a dropdown list.


At the moment the code behind the button 'GotoSite' uses a load of IF statements where the URLs are listed in the code itself as shown in the example below (URLs have been changed for privacy so are just examples)


Private Sub GotoSite_Click()
ChromeLocation = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" 'Location of Chrome.exe in your PC

If SiteList = "BBC" Then
Shell (ChromeLocation & " -url BBC - Homepage")

ElseIf SiteList = "Amazon" Then
Shell (ChromeLocation & " -url Amazon.com: Online Shopping for Electronics, Apparel, Computers, Books, DVDs & more")

ElseIf SiteList = "Mr Excel" Then
Shell (ChromeLocation & " -url Amazon.com: Online Shopping for Electronics, Apparel, Computers, Books, DVDs & more")

End If
End Sub

Rather than have the URL within the code along with a load of IF statements, is it possible to simplify this and to have the URLs listed within the 'Website' worksheet in column B - alongside the corresponding data in column A?

Thank you in advance!

 

[davesexcel]

Test this out

Private Sub ComboBox1_Click()
    Dim sh  As Worksheet
    Dim fnd As Range, uRl As String
   
    ChromeLocation = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"        'Location of Chrome.exe in your PC
   
    Set sh = Me
    With sh
        Set fnd = .Columns(1).Find(Me.ComboBox1)
       
        If Not fnd Is Nothing Then
            uRl = fnd.Offset(, 1).Value
        End If
       
        Shell (ChromeLocation & " -url " & uRl)
    End With
   
End Sub

My test layout.

 

[jmcconnell]

@davesexcel thank you for the above. I'll have a go and let you know how I get on.

 

[jmcconnell]

Hi @davesexcel excel, your code above works perfectly. The problem it my project has a few buttons that rely on the drop down menu. Therefore I need the code to sit behind a button rather running from the drop down menu itself. Is that possible? So i'd chose for instance BBC from the drop down menu then press a button called 'GotoSite'

 

[jmcconnell]

Sorry ignore me. I just copied the same code and attached it to a button. Thanks again.

 

[jmcconnell]

Sorry to be a pain @davesexcel. I'm having a little bit of bother adapting your code. Basically the URL's are kept on a different worksheet to the Combobox and button.

Combobox is on a sheet called 'WindEscalations'
GoTo button is on the sheet 'WindEscalations' also
URL's are in a sheet called 'SiteList'

Is there a way to modify your code so that you can select an option from the combobox but it references the URL in the 'SiteList' sheet?

Again, thank you so much for your help!

Kind regards,
James.

 

[jmcconnell]

Sorry I should also mention that although the combobox is on the 'WindEscalations' sheet, the ListFill it uses for its contents is also on the 'SiteList' sheet.

I hope that makes sense?

 

[davesexcel]

Set sh = Me

Change the sheet to the appropriate sheet
set sh=sheets("SiteList")

 

[jmcconnell]

Thanks Dave. I did try this originally but i think the issue is

Set fnd = .Columns(1).Find(Me.ComboBox1)

its looking for the combo box in the 'SiteList' sheet but the combobox itself is located on a different sheet called 'WindEscalations'.

The combobox contents are populated using ListFill - This list is located on the 'SiteList' sheet.

To recap:
Combobox is on 'WindEscalations' sheet
Combobox contents are filled using a list on the 'SiteList' sheet
URL's are listed on the 'SiteList' sheet

Im unsure how to link what I select from the combobox to the URLs contained in a different sheet.

Apologies for being a pain and thanks again for all your help.

 

[jmcconnell]

I think I've got it now. Apologies for all the posts and thank you once again for your assistance.