Posts by phrankndonna

We use Office 365, so we do have Access. And I do believe we also have SQL Server.

Hello. Our team is working with Office 365. We have a central workbook that gets created each day that has ~2000 records, and then everyone else has a personal workbook that pulls in that data, and they filter on their name to process their records. In the process, they enter notes into a 'user notes' column. During the update of their personal workbooks, their user notes are maintained and transferred to the newly updated data. The desire is to be able to have EVERYONE'S user notes maintained and pulled into the central workbook. This way, there can be much more efficient workload sharing. Please note, I've tried a shared workbook, which is fine for a .xlsx file, but the individual workbooks are .xlsb, which in my experience (as well as with .xlsm), has not worked well at all with shared workbooks.

Does anyone have a thought about how I might create a process to enable individual user workbooks see everyone else's user notes? Thank you!

Hi again. I posted a solution above that my son and I came up with, but Rollis13 came up with the more direct and elegant solution within the hyperlink formula itself. I just wasn't using enough quotation marks around the friendly name variable to account for the UPS string. Just posting the best solution here too. Thank you!

"=HYPERLINK(""https://wwwapps.ups.com/WebTracking/...Home&tracknum=" & TrackingNum & "&AgreeToTermsAndConditions=yes&track.x=17&track.y=4/trackdetails"",""" & TrackingNum & """)"

Sorry about that. There are obviously different users/viewers for each one, so is it best to post to one and then just post the link on the others, as you did? Or is it best to post both the link and the full text? Also looking to see how to show this as SOLVED.

Posting now to show the solution. The issue was the FedEx tracking number is a number and is apparently ok in the Hyperlink formula without the quotes for the friendly name. The UPS tracking number has letters, so it is a string, and it needs the quotes around the tracking number. So, since the full hyperlink formula wouldn't allow adding the extra quotes to the TrackingNum variable, the solution was to break the hyperlink formula in two parts and add the TrackingNum to another string variable, and then stich the two together in a separate variable that gets passed as the formula.

Sub testing2()

Dim TrackingNum As String
Dim TrackingNum2 As String
Dim x As Integer
Dim LastRow As Integer
Dim LinkString As String
Dim LinkString2 As String

LastRow = Range("A23").End(xlDown).Row
For x = 24 To LastRow
    TrackingNum = Range("F" & x).Value

    If Left(TrackingNum, 1) = "5" Then
        Range("F" & x).Formula = _
                "=HYPERLINK(""https://www.fedex.com/fedextrack/?trknbr=” & TrackingNum & "&trkqual=12021~" & TrackingNum & "~FDEG""," & TrackingNum & ")"
    Else
        If Left(TrackingNum, 1) = "1" Then
           TrackingNum2 = """" & TrackingNum & """)"
           LinkString = "=HYPERLINK(""https://wwwapps.ups.com/WebTracking/processInputRequest?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&tracknum=" & TrackingNum & "&AgreeToTermsAndConditions=yes&track.x=17&track.y=4/trackdetails"","
           LinkString2 = LinkString & TrackingNum2
           Range("F" & x).Formula = LinkString2
        End If
    End If
    Range("F" & x).Select
    With Selection
         .HorizontalAlignment = xlLeft
         .NumberFormat = "0"
         .Font.Name = "Cambria"
         .Font.Size = 12
         .Font.Color = -4165632
         .Font.Underline = xlUnderlineStyleNone
    End With

Next

End Sub

Hi. I've got the small module shown below that I'm working on to add a FedEx or UPS hyperlink to a list of tracking numbers. The FedEx formula works just fine, but the UPS formula is bugging out. From my perspective, the format is identical, but I'm obviously missing something. Can anyone suggest what might be out of place, please? Thank you!

Sub testing()
 
    Dim TrackingNum As String
    Dim x As Integer
    Dim LastRow As Integer
    Dim LinkString As String
   
 
    LastRow = Range("A23").End(xlDown).Row
    For x = 24 To LastRow
        TrackingNum = Range("F" & x).Value
        'if tracking number is FedEx
        If Left(TrackingNum, 1) = "5" Then
            Range("F" & x).Formula = _
            "=HYPERLINK(""https://www.fedex.com/fedextrack/?trknbr=" & TrackingNum & "&trkqual=12021~" & TrackingNum & "~FDEG""," & TrackingNum & ")"
        Else
            'if tracking number is UPS
            If Left(TrackingNum, 1) = "1" Then
            Range("F" & x).Formula = _
                "=HYPERLINK(""https://wwwapps.ups.com/WebTracking/...Home&tracknum=" & TrackingNum & "&AgreeToTermsAndConditions=yes&track.x=17&track.y=4/trackdetails""," & TrackingNum & ")"
           End If
        End If
       
        Range("F" & x).Select
        With Selection
            .HorizontalAlignment = xlLeft
            .NumberFormat = "0"
            .Font.Name = "Cambria"
            .Font.Size = 12
            .Font.Color = -4165632
            .Font.Underline = xlUnderlineStyleNone
        End With
    Next
 
End Sub