Create a Macro to look at one cell and then open File

[mlott3528]

Hello,

I am working on creating a Macro to look in column D for the word "Monday". If the cell says Monday then I want it to open the link in the cell beside it. Is this possible?

Example

Column C Column D
(Link to file) Monday
(Link to file) Monday
(Link to file) Tuesday

[cubangt]

should be possible, can you provide a sample link that you would be opening? Are you opening files that are local to your pc or network?
Also in your image above, you have 2 monday's listed, so are you wanting to open both at the same time..

[mlott3528]

Yes the files are on my network. Yes I want it to open all files with the corresponding day. So if it says Monday, I want the macro to open all files labeled for Monday.

[cubangt]

Another few questions..
How do you normally do this now without the macro? Do you simply click each link to open them?

Does this list change in size? Will you ever have more than 15 files?
(This one is just to see if the locations will change or if the ranges can be specifically set in the code)

Are you wanting to simply chose a weekday and have it open the files? or you want it to automatically open the files based on "todays" date when you open the workbook that has the links in it?
(So in other words, since today is Wednesday, when you open your file, the macro runs and determines today is wed and automatically opens the files for Wed.)

[Marc L]

According to your attachment a VBA demonstration as a beginner starter :

Sub Demo1() 
         Dim oH As Hyperlink 
    For Each oH In Hyperlinks 
          If oH.Range.Cells(1, 2).Text = "Monday" Then If Dir(oH.Address) > "" Then Workbooks.Open oH.Address 
    Next 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[mlott3528]

Another few questions..
How do you normally do this now without the macro? Do you simply click each link to open them?

Yes

Does this list change in size? Will you ever have more than 15 files?
(This one is just to see if the locations will change or if the ranges can be specifically set in the code)

Yes it could be more

Are you wanting to simply chose a weekday and have it open the files? or you want it to automatically open the files based on "todays" date when you open the workbook that has the links in it?
(So in other words, since today is Wednesday, when you open your file, the macro runs and determines today is wed and automatically opens the files for Wed.)

Yes choosing a weekday would be great. No I don't want it to open when I open workbook as I am not always working on this tab.

[mlott3528]

This did not work, runtime error.

[cubangt]

Give this a try, i setup and got working the Monday Button, you can update the others accordingly

[Marc L]

This did not work, runtime error.

As it well works on my side but the code must be located into the worksheet module …

[mlott3528]

Thank you,

I added it to my original workbook and updated it. I am still getting a runtime error, can you review my attachment and code.

[Marc L]

Obviously as the cell C2 does not contain a valid workbook reference …

[mlott3528]

I am sorry, I do not understand. What do I need to change it too?

[cubangt]

Thank you,

I added it to my original workbook and updated it. I am still getting a runtime error, can you review my attachment and code.

That module should be removed, sorry about that..
Everything is within the "Sheet1" object

You can remove the Module1 all together.

[Marc L]

I am sorry, I do not understand. What do I need to change it too?

Just paste my code as it is (as your mod was very not a great idea !) to the Sheet1 (Account Info) worksheet module …

[mlott3528]

Marc,

Ok it worked in the template I gave you however in my original workbook it is not working and I know it is because my data is in different columns. Please see my attached template and advise.

[Marc L]

As the day is still in the next column of the hyperlink so there is nothing to change …

[mlott3528]

Then why is it not working?

[Marc L]

If you click on the hyperlink in D3, does it open the expected workbook ?

[mlott3528]

Yes I can. But when I click run on the macro nothing happens.

[Marc L]

It seems an issue between your links on a network and the VBA Dir function so remove the If Dir(… part
in order to directly open the workbook without any verification if it exists …

[mlott3528]

It just does not work.

Anyone who can help?