Avatar of lhutton
lhutton asked on

List box to open URL with parameter in Excel 2007

How can I create a list box (form control) containing values "blank" and A-Z that will open a URL with the selected value appended as a parameter?
Microsoft Excel

Avatar of undefined
Last Comment
SiddharthRout

8/22/2022 - Mon
SiddharthRout

Can you give an example of the link that you want to open... which requires Blank or A-Z

Sid

SiddharthRout

A very basic example

'~~> Example TextBox Contains www.mywebsite\myhtml.html=
Private Sub CommandButton1_Click()
    Set browser = CreateObject("InternetExplorer.Application")
    
    browser.Visible = True
    
    browser.Navigate (TextBox1.Text & ComboBox1.Text)
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.AddItem " "
    For i = 65 To 90
        ComboBox1.AddItem Chr(i)
    Next i
End Sub

Open in new window

ASKER
lhutton

The URL to be opened will be in either of these formats:
- blank: http://www.domain.com/folder/var?l=1234567
- A-Z: http://www.domain.com/folder/var?l=1234567&f=A
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SiddharthRout

'- blank: http://www.domain.com/folder/var?l=1234567
'- A-Z: http://www.domain.com/folder/var?l=1234567&f=A
Private Sub CommandButton1_Click()
    Dim StrURL As String
    
    On Error GoTo Err
    
    Set browser = CreateObject("InternetExplorer.Application")
    
    browser.Visible = True
    
    If Len(Trim(ComboBox1.Text)) = 0 Then
        StrURL = "http://www.domain.com/folder/var?l=1234567"
    Else
        StrURL = "http://www.domain.com/folder/var?l=1234567&f=" & Trim(ComboBox1.Text)
    End If
    
    browser.Navigate StrURL
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Open in new window

SiddharthRout

Also this might be required to fill the combobox

Private Sub UserForm_Initialize()
    ComboBox1.AddItem " "
    For i = 65 To 90
        ComboBox1.AddItem Chr(i)
    Next i
End Sub

Open in new window

ASKER
lhutton

How do I set up the list box?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SiddharthRout

Replace ComboBox1 by Listbox1 above...
SiddharthRout

Here is the complete code.

Private Sub UserForm_Initialize()
    ListBox1.AddItem " "
    For i = 65 To 90
        ListBox1.AddItem Chr(i)
    Next i
End Sub
'- blank: http://www.domain.com/folder/var?l=1234567
'- A-Z: http://www.domain.com/folder/var?l=1234567&f=A
Private Sub CommandButton1_Click()
    Dim StrURL As String
    
    On Error GoTo Err
    
    Set browser = CreateObject("InternetExplorer.Application")
    
    browser.Visible = True
    
    If Len(Trim(ListBox1.Value)) = 0 Then
        StrURL = "http://www.domain.com/folder/var?l=1234567"
    Else
        StrURL = "http://www.domain.com/folder/var?l=1234567&f=" & Trim(ListBox1.Value)
    End If
    
    browser.Navigate StrURL
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Open in new window

ASKER
lhutton

I've drawn a list box, but I don't know how to link it to your code; am I supposed to label it somewhere with "ListBox1"?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SiddharthRout

Here is a sample

Sid
Sample.xls
ASKER
lhutton

OK; thank you. I was expecting just to insert a form control and attach code to that so I can open a list and click on a letter to open the URL. Does it have to be done with a form?
SiddharthRout

You want to put it in the worksheet?

If yes then see this...

Sample.xls
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
lhutton

Is it possible to have the values in the code, rather than in the worksheet?
Can it be the type of list box that shows just one value at a time, and that you click on one arrow to open the box?
Is it possible to have open the URL when the value is clicked on, rather than having to click a separate button?
SiddharthRout

Sure give me a moment
ASKER CERTIFIED SOLUTION
SiddharthRout

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
lhutton

That's perfect; thank you.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SiddharthRout

You are welcome :)

Sid