Appalachian Trail Userform-VBA Creation

We will start by showing you the first snippet of VBA code and proceed with an explanation.

Sub novloc()
    Dim offnbr, offnbr2 As Variant
    currow = ActiveCell.Row
    nbr = Range("AG" & currow).Value
    Sheets(3).Activate
    offnbr = Application.Match(nbr, Range("a5:a1515"), 1)
    Range("a5").Offset(offnbr, 0).Activate
    WhereAmI.Label4.Caption = ActiveCell.Offset(0, 1).Value
    ctyst = ActiveCell.Offset(0, 4).Value + " - "
    WhereAmI.Label6.Caption = ActiveCell.Offset(0, 2).Value
    Sheets(4).Activate
    offnbr2 = Application.Match(nbr, Range("a2:a292"), 1)
    Range("a2").Offset(offnbr2, 0).Activate
    WhereAmI.Label5.Caption = ctyst + ActiveCell.Offset(0, 5).Value
    WhereAmI.Label9.Caption = Str(ActiveCell.Offset(0, 6).Value) + " /" + Str(ActiveCell.Offset(0, 7).Value)
    Sheets(3).Activate
    'If ActiveCell.Offset(0, 1).Hyperlinks(1).Address <> "" Then
    If ActiveCell.Offset(0, 1).Hyperlinks.Count > 0 Then
        WhereAmI.Label7.Caption = ActiveCell.Offset(0, 1).Hyperlinks(1).Address
        WhereAmI.CommandButton2.Visible = True
    End If
    Sheets(1).Activate
    WhereAmI.Show
End Sub

You should have downloaded walktheat.xlsm and explored the usage of the “Location” and “LongLat” sheets. We have two other sheets named “Log2021” and “Log2022” which have the layout previously illustrated. These sheets are designed to track walking, jogging, cycling, or other mileage for an entire year. As previously discussed, the “Total” column will display a running total (February used in this example) using the SUM(B3:AF3) formula. This same formula could be used on the other rows as long as you do not want a running total. We accomplish that feat by adding the cell above to the sum, such as AG3+SUM(B4:AF4). This formula can be copied down with the proper cell references maintained.

Have you noticed the “Where am I?” button placed in cell A1? That loads the form that we have already discussed and populates it based on the “Total” column of the currently selected row. When the user clicks this button it will call the associated VBA macro (“novloc” in this case) and process that code. You will see two extremely similar subroutines. These are necessary since we have two sheets to accommodate the 2021 and 2022 years. Your end project may have five years covered, in which case you would have five slightly different subroutines. The subroutine executed for the 2022 year is shown below and both subroutines are explained and their differences explored.

Sub decloc()
    Dim offnbr, offnbr2 As Variant
    currow = ActiveCell.Row
    nbr = Range("AG" & currow).Value
    Sheets(3).Activate
    offnbr = Application.Match(nbr, Range("a5:a1515"), 1)
    Range("a5").Offset(offnbr, 0).Activate
    WhereAmI.Label4.Caption = ActiveCell.Offset(0, 1).Value
    ctyst = ActiveCell.Offset(0, 4).Value + " - "
    WhereAmI.Label6.Caption = ActiveCell.Offset(0, 2).Value
    Sheets(4).Activate
    offnbr2 = Application.Match(nbr, Range("a2:a292"), 1)
    Range("a2").Offset(offnbr2, 0).Activate
    WhereAmI.Label5.Caption = ctyst + ActiveCell.Offset(0, 5).Value
    WhereAmI.Label9.Caption = Str(ActiveCell.Offset(0, 6).Value) + " /" + Str(ActiveCell.Offset(0, 7).Value)
    Sheets(3).Activate
    'If ActiveCell.Offset(0, 1).Hyperlinks(1).Address <> "" Then
    If ActiveCell.Offset(0, 1).Hyperlinks.Count > 0 Then
        WhereAmI.Label7.Caption = ActiveCell.Offset(0, 1).Hyperlinks(1).Address
        WhereAmI.CommandButton2.Visible = True
    End If
    Sheets(2).Activate
    WhereAmI.Show
End Sub

The first line sets up our two offset variables as the Variant type. This means that it can be used as a string, number, Boolean, or whatever type we choose to assign to it. Next we set the currently selected row to a variable named “currow.” Our next line sets the variable named “nbr” to the current mileage total (column AG of the currently selected row). The Activate command is then used to activate sheet number 3. This number is one of the modifications made based on the number of years included in your application.

Now that we have activated the “Location” sheet (sheet 3 in our example), we can use the “Application.Match” function to find the current mileage within the list that returns location, county, elevation and link (if available) and set the “offnbr” variable to the location offset. We again use the Activate command to select the corresponding row for our current mileage. The location, county, elevation, and URL link are retrieved from the appropriate columns using the Offset command (column offsets of 1,4 and 2 using the same row – 0 offset). Now we activate sheet4 to get the remaining information used to populate the form. We once again use “Application.Match” to select the current mileage in the “LongLat” sheet and activate that row. Just as before, we use offsets to retrieve and populate the state, latitude, and longitude. Our code now activates sheet 3 to determine if there is a URL address attached to the location. If so, the hidden “label7” is populated and btnView is displayed. The final two lines of code within this subroutine activate sheet1 and show (transfer control to) the WhereAmI form.

In order to accommodate another year the only code change is the sheet that is activated in the line before showing the form. Let’s say that you have decided to add three more years of sheets. In that case, sheets 3,4 and 5 will be utilized for those three years and the three sheets(?).Activate lines will use 6,7 and 6 (respectively) in addition to the final Activate returning to the correct sheet.

You have now created your own exercise tracking Excel sheet which will allow viewing pictures of nature’s beauty along the Appalachian Trail spanning from Springer Mountain in Georgia to Baxter Peak in Maine. As the trail changes due to fallen trees and other natural barriers, the mileage will vary. The supporting web sites and pictures will also vary as changes are made by the hosts of that information. See below for credits.