Macro to Disable Hyperlink when file is ReadOnly

[JJFletcher]

Hello All,

I have a sheet that has six images and each has a Hyperlink assigned to each.

The Need:

When the User does not enter the correct Password to access the sheet, functionality of the sheet is turned ReadOnly.

That being said, the Hyperlink assigned to each Image still functions and the user can access material that is only for those who have entered the correct password.

Needing to discuss what may be put into the worksheet that if the User status is set to ReadOnly the Hyperlinks become inactive!

Any thoughts??

Best Regards,

John

[Kenneth Hobson]

One way would be to hide all but the "Safe" sheets at workbook close. On open, If ThisWorkbook.ReadOnly Then, show just sheets that you want. Else, show all or Write status sheets.

[JJFletcher]

Hi Kenneth,

Thanks for you input - I really appreciate that!

My apologies - I should have explained in more detail... The User that has ReadOnly access are able to read the data that is on the sheet... Hiding that data would defeat the purpose of them being able to read the information.. I discovered that when the User has ReadOnly access they cannot interact with some portions of the file but they were able to select the image and launch a Publisher file which is intended only for those with complete access

Regards,

John

[Kenneth Hobson]

Right, duplicate the sheet and remove the hyperlink for the readonly version. e.g.

Please Login or Register  to view this content.

[/CODE
Of course any such file can be hacked. Be sure to protect the vbaproject as well. Mainly, this just provides a modicum of protection.

[JJFletcher]

Thanks Kenneth,

I'll make a version of that and see how it functions

Regards

John

[JJFletcher]

It's been suggested I do the following... what would that look like in code?

Set a public variable when checking the password that exits the Click-event of the image when in readonly –mode.

Regards,

John

[bakerman2]

Put this in ThisWorkbook.

Please Login or Register  to view this content.

and this in a Standard module.

Please Login or Register  to view this content.

[JJFletcher]

Hi backerman2,

I tried the code suggested and it did not stop the hyperlink... when in ReadOnly status - could still select the hyperlink

Kenneth,

I have not created your suggestion yet - will get to it though!

Regards,

John

[bakerman2]

I'm assuming the images are in ActiveX ImageObjects and the hyperlinks are activated by using the Click-event of the object and the FollowHyperlink-method ?

[JJFletcher]

Hi backerman2

Thanks for your reply...

there are three images in one... dark image, light image these are active X... There is a square shape added between the two Active X Images - and that shape has the hyperlink attached using the insert link process and entering the Publisher file

Regards,

John

[bakerman2]

Instead of assigning a hyperlink, assign each shape to 1 general peace of code with a Select Case. Depending on which Shape is calling the code assign the correct path to the Publisher-file to a variable and use that variable in the FollowHyperlink-method. Doing it like this you could set the public variable on opening the workbook and checking the password. Then checking the state of the public variable on top of the general code should give you the possibility to exit or push forward with the code.
Something like.

In this workbook.

Please Login or Register  to view this content.

In a standard module.

Please Login or Register  to view this content.

Now assign to each shape the code Open_PubFile instead of the hyperlink and make sure the ShapeNames are correct.