Format a Date with Words in a Hyperlink

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
439
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance and will try to give feedback on whether it works or not.

I am trying to format a date in a hyperlink. I want the date and add phrases with it. Such as "2020-07-22, Morning, Yes" I attempted to use concatenate, but it would not work. Here is the simplified code.

Code:
'*****************************************************************************************************
Sub DateF()


    '____________________________________________________________________________________________
    'Turn off alerts, screen updates, and automatic calculation
        'Turn off Display Alerts
            Application.DisplayAlerts = False

        'Turn off Screen Update
            Application.ScreenUpdating = False
            
        'Calculate to ensure all values are updated
            Calculate

        'Turn off Automatic Calculations
            Application.Calculation = xlManual



    '____________________________________________________________________________________________
    'Dimenisoning/Declaring Variables
        Dim Date1 As Date
        Dim When1 As String
        Dim Confirm1 As String
    
    '____________________________________________________________________________________________
    'Main Code in this section
        
        Sheets(Sheet1).Activate
        Date1 = Range("A1").Value
        When1 = Range("B1").Value
        Confirm1 = Range("C1").Value
        
        
        'Cell D1 has a hyperlink to be renamed
            Range("D1").Select
            Selection.Hyperlinks(1).TextToDisplay = _
                Date1 & ", " & When1 & ", " & Confirm1
                'Would like the format to be like this: "2020-07-14, Morning, Yes"
                'not including the quotes though
                'I tried concatenate
                '=CONCATENATE(Text(EarningsDate, "YYYY-MM-DD DDD"), ", ", When, ", ", Confirmation)
       


    '_______________________________________________________________________________________________
    'Turn on alerts, screen updates, and calculate
        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Calculate to ensure all values are updated
            Calculate

        'Turn off Automatic Calculations
            Application.Calculation = xlManual
                
End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Maybe:
VBA Code:
Sub Example()
'Assumes cell D1 on active sheet contains a hyperlink to be renamed
Dim Date1 As Date
Dim When1 As String
Dim Confirm1 As String
Date1 = Range("A1").Value
When1 = Range("B1").Value
Confirm1 = Range("C1").Value
Range("D1").Hyperlinks(1).TextToDisplay = Format(Date1, "yyyy-mm-dd") & ", " & When1 & ", " & Confirm1
End Sub
 
Upvote 0
Thanks @JoeMo

I tested the code and it gave the error:

"Run-time error '9':

Subscript out of range"
Did you read the comment in the code? Do you have a hyperlink in cell D1 of the activesheet before you run the code? If not, you will get that error? Your OP says 'Cell D1 has a hyperlink to be renamed - is that the case?
 
Upvote 0
@JoeMo As I indicated, I thought there was an issue with my code.

I was running a loop and there was a blank row and that was the issue. I put an If then to fix that.

Thanks so much as your solution worked perfectly.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top