Protection Stops Macro From Running

[Keith Burgess]

Hi All,

I have a very simple request. I want to make the worksheet protected, however, when I protect the worksheet the macro will not run. Does anyone have any useful tips. Many thanks in anticipation.

Keith

[Andy Pope]

You need to remove and re-apply protection within your code.

Please Login or Register  to view this content.

You might want to consider remove the attached file as the content looks as though it may be confidential.

[royUK]

You could possibly use Protect with UserInterface, see an example here

http://www.excel-it.com/vba_examples.htm

[Keith Burgess]

Hi Andy,

Many thanks for your prompt reply. I have cut and pasted your ammended macro but it doesn't seem to work as before, where each line containing the search criteria was highlighted in yellow. Can you help me further, please?

Best regards
Keith

[Andy Pope]

Works for me.

Run the macro and search for time.

EDIT: Confidential file removed

[Keith Burgess]

Hi Andy,

Sorry it doesn't work for me. When I run the nmacro and search for time it takes me to a line within the macro. Can you advise further, please.

Kind regards
Keith

[yawnzzzz]

Hi Andy,

Sorry it doesn't work for me. When I run the nmacro and search for time it takes me to a line within the macro. Can you advise further, please.

Kind regards
Keith

It would help you posted the line in the macro it takes you to.

[royUK]

It works fine. The code finds any row where a cell contains "time2 and highlights it.

[Keith Burgess]

Hi Guys,

Below is the line in the macro it takes me to when I run the macro and ask it to search for time. I am using MS Office Excel 2003?

Please Login or Register  to view this content.

Hope this helps.

Kind regards
Keith

[royUK]

Is the sheet you are searching active, i.e. the one open?

[Keith Burgess]

Hi Roy,

Many thanks for your reply. Yes! the sheet is active and open in fact it is the only worksheet in the workbook.

Kind regards
Keith

[royUK]

I ran andy's code in his example workbook with no errors.

Can you attach your workbook?

[Keith Burgess]

Hi Roy,

Here is the workbook in its original format.

Kind regards
Keith

[royUK]

The code runs perfectly in your workbook.

[Keith Burgess]

Hi Roy,

Attached is a protected worksheet (password to unprotect is password). The macro will not run on a protected sheet. What I am trying to do is share the spreadsheet with colleagues and allow them to search for different criteria, however, I want to protect the worksheet so that my colleagues cannot alter any of the data therein. I hope you can help.

Kind regards
Keith

[Andy Pope]

If your sheet already has a password applied then you need to include that in the code where protection is removed and applied.

Please Login or Register  to view this content.

[Keith Burgess]

Hi Guys,

Many thanks for working this out for me it now works like a charm. Special thanks go to Roy and Andy for their patience.

Best Wishes
Keith