Part 3: Navigate to Selected Page (Written Steps)

1. Add Combo Box and Button

First, add the button. If you have gone through this entire tutorial and prior tutorials, you know how to add buttons. You may not have gone through the repetition of adding buttons over and over again so I will tell you how just in case. You go to the “Developer” tab, you move your mouse over to the “Insert” drop-down and find the “Command Button” under “ActiveX Controls.”

Now you need the combo box. You do pretty much the same thing as the button, except that in the “Insert” drop-down you select “Combo Box.”

Be sure to rename the combo box and button using “Properties.” For the button I chose “NavigateButton” as the name. The combo box has a name of “WebNameList.” Feel free to use whatever names you want, just remember that the code I show you will use those names.

After placing the buttons, it should look something like the below image:

2. Alter Functions Created in Part 2, Change them to Public

You may have noticed that your subroutines and functions have all said “Private” in front of them. This means that they are only accessible or can be called from within the sheet. We are going to make our own module, and the functions that you created in part 2 will need to be accessible by that module. In order to do that, we have to change the word “Private” to “Public.”

The functions you created would have looked like this:

I want you to make them look like this:

That is it for this step, just change “Private” to “Public” and move on to step 3.

3. Create a Module and Refresh Subroutine Set to Public

Excel does this quirky thing with combo boxes where it doesn’t populate them even if they are populated when you save. So what we want to do is when you add a new URL we want that combo box to have all of the URL display names available, including the new one you just added. We also want to make sure that Excel populates that combo box every time you open the file. We don’t want to have to write the code twice; so we create a subroutine that we can call twice.

We include this subroutine in its own module so that it is easier for Excel to access when it loads. It treats it a lot like an automated macro. Excel upon loading will call this subroutine (once we create another subroutine that will tell it to do that in step 4).

On the developer panel, you will see menu items at the top. Select “Insert” and from the drop-down choose “Module.”

This will create a module named “Module1” and place it in a folder called “Modules,” I recommend you keep the module name “Module1” as that is how I reference it in the code that I show you.

Click on “Module1” and you should have a blank page to code in. On that blank page, you will want to start with your subroutine declaration, we will name the subroutine “RefreshWebNameList.” Type the following code:

Public Sub RefreshWebNameList() End Sub

The first thing we are going to want to do is to think about what we want to do. We need this subroutine to scan the display names on the “Source List” sheet and then put them in the combo box. Sounds like a job for a loop. Like our earlier loop in part 2, we need a counter (the loop we are about to create is almost exactly identical to the loop in part 2).

So we need to declare a variable for counting. Within your “RefreshWebNameList” subroutine add the following:

Dim rowCounter As Integer 'Counter used to help find get our entered items
 rowCounter = 2 'First available row on the source list

The next quirk you have to deal with, when you run a loop to load in each item, that loop will start at the beginning each time. If you add one item it will work fine. If you then go and add a second item, it will add the first item again, then the second item. A third item will add the first item a third time, the second item a second time… and I think you can see the problem, you end up with a combo box that looks like this:

So we need to do something to make sure that doesn’t happen. The easiest way is to just clear the list each time. There are other ways, but we will just clear the list. So after your rowCounter variable declaration, add the following line that will clear the list:

Sheet1.WebNameList.Clear

Because we are in a module, we need to access the combo box located in the “Favorite List” sheet, or “Sheet1.” So we start with “Sheet1” then we place a period. This tells the code to look at “Sheet1” and after the period we tell it what we want to access. We want to access the combo box named “WebNameList.” We add a period after “WebNameList” then tell it what we want to do. We want to clear it.

Now it is time for our loop.

While Not ThisWorkbook.Sheets("Source List").Range(Sheet1.DisplayNameRange(rowCounter)).Value = ""
 Wend

This is very similar to the loop we made in part 2 of this tutorial. The only difference is in the underlined area. We are in “Module1” and we need to tell it to look at “Sheet1.” We are accessing the “DisplayNameRange” function created in part 2, and modified earlier in this part to be public. This is why we made it Public, so we could access it from “Module1.”

Within that loop, between “While Not” and “Wend” you will need to add the following code:

Sheet1.WebNameList.AddItem ThisWorkbook.Sheets("Source List").Range(Sheet1.DisplayNameRange(rowCounter)).Value
rowCounter = rowCounter + 1

As before, the rowCounter variable is used to keep track of the rows and drive the row switching. The line above rowCounter is a little more complicated. “Sheet1” is telling it where to look, “WebNameList” is our combo box and we want to add an item to it so we type “AddItem.” The item(s) we want to add is in the “Source List” sheet. So we reference “ThisWorkbook.Sheets(“Source List”)” as the where we want to go, “Range(Sheet1.DisplayNameRange(rowCounter))” as what we want to interact with, and we want to secure the “Value.”

Your total finished code should resemble the images below:

'Part 3 Code

Public Sub RefreshWebNameList()
    Dim rowCounter As Integer 'Counter used to help find get our entered items
    rowCounter = 2 'First available row on the source list

    Sheet1.WebNameList.Clear 'Clear the combo box on Sheet 1

    While Not ThisWorkbook.Sheets("Source List").Range(Sheet1.DisplayNameRange(rowCounter)).Value = "”
        Sheet1.WebNameList.AddItem ThisWorkbook.Sheets("Source List").Range(Sheet1.DisplayNameRange(rowCounter)).Value
        rowCounter = rowCounter + 1
    Wend
End Sub

Be sure that it is placed in “Module1”

4. Create the Workbook_Open Subroutine

Workbook_Open() is a special subroutine. It will execute when Excel opens automatically. We need to create it and have it call the “RefreshWebNameList” subroutine created in step 3 so that the combo box is populated everytime you load this file into Excel.

In your developer panel, locate “ThisWorkbook” on the left-hand side.

Click it with your mouse and you should have a blank page to work with. In that blank page, you will create the “Workbook_Open” subroutine. The code for that is below.

'When the Excel Workbook opens Private Sub Workbook_Open()
     Application.OnTime Now + TimeValue("00:00:01"), "RefreshWebNameList"
 End Sub

The code above is a small timer. We give it 1 second before calling the “RefreshWebNameList.” Calling a function that interacts with your cell at startup is not always reliable, so we give it a timer to help ensure things have loaded. If you find that your application is not loading the contents of the combo box reliably you can increase the “TimeValue” by changing the “00:00:01.” Sometimes programming is about playing around to get things right.

5. Make a Small Addition To Your Add_Fave_Click Subroutine

Your “Add_Fave_Click” subroutine was created in part 2 of this tutorial. It is responsible for taking the values you put in the display name and URL field and storing them in the “Source List” sheet. What we want to happen, is that every time you add a new item to your list, it also updates your combo box. In order to do that we need to call the “RefreshWebNameList” subroutine that you created earlier inside of the Module1 page.

On your developer pane, move your mouse to “Sheet1 (Favorite List)” and click it.

You want to add the line “Call Module1.RefreshWebNameList"as shown below.

'When the Add to Favorites Button is Clicked Private Sub Add_Fave_Click()
      Dim DisplayName As String 'The display Name for the URL
      Dim UrlLocation As String 'The location you want to navigate to
      Dim rowCounter As Integer 'Counter used to help find the first empty row in the souce list
      rowCounter = 2 'First available row on the source list
      
       'Set the variable names to the values in the textboxes for display and url
       DisplayName = New_DisplayName.Value
       UrlLocation = New_URL.Value

       'Make sure the textboxes contained values
       If DisplayName = "" Or UrlLocation = "" Then
            MsgBox "One of the required values is missing!"
       Else
            'Clear the textboxes
             New_DisplayName.Value = ""
             New_URL.Value = ""
             While Not ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = ""
                  rowCounter = rowCounter + 1
             Wend
            ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = DisplayName
             ThisWorkbook.Sheets("Source List").Range(URLRange(rowCounter)).Value = UrlLocation
             
             'Refresh WebNameList Combobox
             Call Module1.RefreshWebNameList
      End If End Sub

You have now practiced the art of code reuse. You created a subroutine called “RefreshWebNameList” and you use it in two different locations. This means you didn’t need to write the same code to do the exact same thing. Can you think of another way to create code reuse within this application? (One possible answer: the “DisplayNameRange” function and the “URLRange” function created in Part 2 could be combined with an additional parameter to give it the “A” or “B” column value. Public Function TextRange(col As String, rowNum as Integer) As String)

6. Give it the Ability to Open Your Selected Web Page

If you haven’t already done so, go back to your main Excel sheet on the “Favorite List” tab and double-click the navigate button.

This should create an empty subroutine for you automatically. 

We have to think about this again. What do we want to do? We have a combo box with the display name. Our “Source List” is organized as display name in column A and our URL in column B. We have to get the URL in order to use that to open up a web page. So if we could match the value in the combo box to something in the display name column on the “Source List” tab we could find the URL. We just may need another loop to compare the values in our combo box to the values in the display name column.

So, declare your counter variables inside of the NavigateButton_Click subroutine:

Dim rowCounter As Integer 'Counter used to help find the first name match
rowCounter = 2 'First available row on the source list

And now we will prepare the loop. It is very similar to the prior loops, except we aren’t looking for a blank column. We are looking for the value contained in the WebNameList combo box.

While Not ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = WebNameList.Value
       rowCounter = rowCounter + 1
Wend

Now we want to open up your default web browser and feed it a link. A warning here, DO NOT put this inside of the above loop. Put this after the loop. If you put it in the loop, you may end up with more pages opened than you intended.

ActiveWorkbook.FollowHyperlink Address:=ThisWorkbook.Sheets("Source List").Range(URLRange(rowCounter)).Value, NewWindow:=True

There is kind of a lot of code there. What you are doing is setting up a default web browser using “FollowHyperLink.” You giving that browser an address to follow with “ThisWorkbook.Sheets("Source List").Range(URLRange(rowCounter)).Value"  and then telling it to open a new Window with “NewWindow:=True"

The total code for the “NavigateButton_Click” subroutine should resemble the below code.

Private Sub NavigateButton_Click()
      Dim rowCounter As Integer 'Counter used to help find the first name match
      rowCounter = 2 'First available row on the source list
      
       While Not ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = WebNameList.Value
            rowCounter = rowCounter + 1
      Wend

      ActiveWorkbook.FollowHyperlink Address:=ThisWorkbook.Sheets("Source List").Range(URLRange(rowCounter)).Value, NewWindow:=True
 End Sub

And…. you are done. Test it out. Make sure it works, then try some of the homework on the next page. Before you go, here is all of the code together that should be in your project.

Sheet1 Code:

'---------------------PART 2 CODE------------------------------------------------------
 Public Function DisplayNameRange(rowNum As Integer) As String
      DisplayNameRange = "A" & CStr(rowNum)
 End Function
 Public Function URLRange(rowNum As Integer) As String
      URLRange = "B" & CStr(rowNum)
 End Function

 'When the Add to Favorites Button is Clicked
 Private Sub Add_Fave_Click()
      Dim DisplayName As String 'The display Name for the URL
      Dim UrlLocation As String 'The location you want to navigate to
      Dim rowCounter As Integer 'Counter used to help find the first empty row in the souce list
      rowCounter = 2 'First available row on the source list
      'Set the variable names to the values in the textboxes for display and url
      DisplayName = New_DisplayName.Value
      UrlLocation = New_URL.Value
     'Make sure the textboxes contained values
     If DisplayName = "" Or UrlLocation = "" Then
           MsgBox "One of the required values is missing!"
     Else
           'Clear the textboxes
            New_DisplayName.Value = ""
            New_URL.Value = ""
            
             While Not ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = ""
                  rowCounter = rowCounter + 1
            Wend
            ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = DisplayName
            ThisWorkbook.Sheets("Source List").Range(URLRange(rowCounter)).Value = UrlLocation
            
            'Refresh WebNameList Combobox
            Call Module1.RefreshWebNameList
      End If
End Sub
 '-----------------------------PART 3 CODE-------------------------------------------------
 Private Sub NavigateButton_Click()
      Dim rowCounter As Integer 'Counter used to help find the first name match
      rowCounter = 2 'First available row on the source list

      While Not ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = WebNameList.Value
           rowCounter = rowCounter + 1
      Wend
      ActiveWorkbook.FollowHyperlink Address:=ThisWorkbook.Sheets("Source List").Range(URLRange(rowCounter)).Value, NewWindow:=True
 End Sub

Next Page: Homework