• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automate a Hyperlink

Hello everyone, I would appreciate a little help with this issue. I like to automatically execute a URL in cell V19 on the 21st of every month. I don't know how to do this, could I obtain some assistance please.
Thank you.
 
Hello everyone, I would appreciate a little help with this issue. I like to automatically execute a URL in cell V19 on the 21st of every month. I don't know how to do this, could I obtain some assistance please.
Thank you.
Hi, @derek davis,
I think you want something like the attachment. There is a small VBA code attached, which code will run every time you open the workbook. If your system date is 21st of any month then the desired hyperlink (which given in cell V19 of Sheet1) will be opened in your default browser.

The code is as below:
Code:
Private Sub Workbook_Open() 
    Dim sWebsite As String
    
    sWebsite = Sheet1.Range("V19") 'Change as per your requirement    
    If (Left(Date, 2)) = 21 Then   
    ActiveWorkbook.FollowHyperlink Address:=sWebsite, NewWindow:=True 'Open Website
    
End If
End Sub
 

Attachments

  • Open Hyperlink on 21st.xlsm
    14.1 KB · Views: 13
Hello again, I cant get the macro to run that you offered automatically. I loaded the macro into Modulel1 along with other macros that I have there. For testing I changed the date of execution to the current date and am able to run the macro without issue. I am wondering why I cannot see the macro name appear in the macro info box. My other macros are listed but not this new one. This could be where the problem exists. how would I get this listed. One last thing I noticed that the create button is greyed out. Hope this helps
 
Hi Derek, This macro should be put in "ThisWorkbook" module under Microsoft Excel Objects. As this code will check the current date is 21st or not whenever you open the workbook. Please refer to the attached workbook above.
 
... There is a small VBA code attached, which code will run every time you open the workbook. If your system date is 21st of any month then the desired hyperlink (which given in cell V19 of Sheet1) will be opened in your default browser.

The code is as below:
Code:
Private Sub Workbook_Open()
Dim sWebsite As String
sWebsite = Sheet1.Range("V19") 'Change as per your requirement  
If (Left(Date, 2)) = 21 Then 
  ActiveWorkbook.FollowHyperlink Address:=sWebsite, NewWindow:=True 'Open Website
End If
End Sub

Hi S.Das,
your code seems to work if the desired date (day) is a two digit number, but with single digit numbers (day 1 to 9) it fails.
Also, if the user system date format differs, it will fail

If current date is e.g. "11/6/2020" your code returns "11" which is right, as the day is "11th", but if the date is e.g. "5/6/2020" your code would return "5/" which is no valid day.
If the user hast the date format set up as "MM/dd/yyyy" (like me), your code would return the current month instead of the current day

It's much safer to use the DAY() function:
Code:
If Day(Date) = 21 Then
 
Back
Top