Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro On Protected Sheet

0

Hi,

Kindly view the attached file it has macro to copy INVOICE details to INVOICE MAIN sheet and INVOICE INVENTORY SHEET, when i run a macro it shows the sheet (inventory sheet)  need to be copied and comes back to active sheet ( invoice sheet) but

what i notice is it stays in active sheet (invoice sheet) without showing any sheets ( inventory & main ) if unprotected (i just remove the code and unprotect the sheets and checked )

So ift the sheets are protected is that the way macro run ?( showing copying sheets and comes back to active sheet) or i have done anything incorrect?

Pls advise

Password "1234"

Post Edited
Title Not Appropriate: Title was not appropriate. (could be all caps, annoying punctuation, etc.)
Answer
Discuss

Answers

0

Ramzein, you are proceeding on Mission Impossible. Please start anew.

  1. Set up your code until it really works well, exactly the way you want.
  2. Then add protection to your project.

Your mission is impossible because you can't complete your programming because of restraints imposed by the protection, and you can't fix the protection because it's interwoven with the project's programming.

As for help from this forum, we can help you fix your program or we can help you fix your protection but we can't do both in the same thread.

Discuss

Discussion

Sound advice @Variatus! 

That is definitely the best approach for Ramzein to follow. Accordingly I've deleted my earlier "Answer" (made in error, very early in my day) 
John_Ru (rep: 6102) Nov 26, '20 at 3:44 am
Hi,

i just cant understand what i have done as you stated above
what i asked is the macro works on both mode (protected & unprotected)

but in the protected mode it shows the sheet where i copy invoice details than comes back to invoice sheet as i click OK on msg box (macro unprotect & protect)

IF no code for unprotect and protect than the msg box appears in the same  acrive sheet
(invoice sheet) 

so it works in both mode with no error or any problem so far

my question is why when the code is enabled to macro to unprotect and protect the sheets it shows that sheet (inventory sheet) as i click Ok to go back to Active sheet (invoice sheet)

pls correct me if im wrong again

Ramzein (rep: 8) Nov 26, '20 at 3:48 am
In your code protection is switched on and off continuously which is the wrong approach. You find that this method interferes with the smooth running of your code.Therefore, we should remove all your protection and show you how to add protection to your project.
But even the suggestion that the solution might not be in your code but in your approach already raises your hackles. And you are 100% right: your question isn't about how to apply protection. Therefore we don't want to touch your code for fear of offending you. Then, how might we help you?
Give us code without any protection and ask how to apply protection to it, describing exactly what you wanrt. This may not be possible in this thread because it's too different. Therefore abandon it and ask a new question.
Variatus (rep: 4889) Nov 26, '20 at 4:06 am
Oops! Sorry @Variatus, hadn't seen this
John_Ru (rep: 6102) Nov 26, '20 at 4:44 am
Well,
i quite understand, ill remove protection codes and ask post a new question 

Thanks
Ramzein (rep: 8) Nov 26, '20 at 4:53 am
Add to Discussion
0

Ramzein

I just tried it and Module 5 (though incomplete) works the same if you first remove password protection for sheets 9. 11 and 13 then comment out the .unprotect and .protect parts of your code (You'd get an error if you ran "unprotected" Module 5 but left the sheets initially protected since you'd be trying to write to protected sheets).

That's to say the MsgBox saying "Copied invoice..." will display with sheet 11 displayed (since the code is in the section where that sheet is activated). 

Your macro will always return to sheet13 (Invoice) since in the code you have sheet activation staements in this sequence: 

Sheet9.Activate
....
Sheet11.Activate
....
Sheet13.Activate
....

(Tip: to find where a word like Activate is used your code, in VB Explorer click menu Edit/ Find... then type "Activate" in Find What and set Direction as "Down" then FindNext)

Discuss

Discussion

Apart from Module 5 answer above, these are points for your investigation:    1) in the code for Sheet 11, did you mean to test a cell from Sheet 13?
With Sheet11
 
For ItemCount = 34 To 43
 
    If Sheet13.Cells(ItemCount, 4) <> "" Then
      2) In new sheet 9, you need to format cells in column D like D1 and in column G like G1 (since they are currently General and Date- which give the wrong view) 
John_Ru (rep: 6102) Nov 26, '20 at 4:43 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login