I am not a person who has been educated in VBA (but intend to). My users wanted a multi-select dropdown list in the Excel form I provided, which I was able to accomplish, but I had to protect the worksheet...and it would not work!!!! Your good instruction has made it possible for me to give them what they wanted...and for me to finally get a good night's sleep! Thank you, Thank you.
Worked nicely for me; I had an input sheet and and a separate output sheet. I kept the command buttons on the input sheet, inserted your code lines you showed, and got the output sheet protected. Many thanks Dinesh!
Thank you Sir... This was the simpliest solution for me. I have zero knowledge in VBA and it worked like a charm first time I tried it unlike other solutions I had come across online.
What formula can I use: I have employee schedule: "Rosa" this person have a daily position where she will work. That work position is another sheet. / cell"A" Rosa / cell "B" 8am-5pm / cell "C" drop box asking what position she will be. Now by selection from de drop box X position. "Sheet 2" can take that information in put her name "Rosa" into the right position. Remember in sheet 2 there15 difference position I by clicking drop box dishwasher "Rosa" will be dishwater on sheet 2.
Hello Sir, I watched your video.Great video & with good presentation. Sir,I want your advice.I always like to learn a new thing like Excel,word access,VBA. (Still Learning) But i dont know how to use this knowledge in professional life. I want to become Trainer but most of recruiter want experience trainer.Well,i need your precious advice that what are other options? Sorry to ask you question like this but i would be grateful to you if you help me.
It doesnt work if you have mumltiple sheets connected to your macro. Ive a lot of calls on some modules and unpro and pro is not reliable. Still looking something to work.
Dear sir, Is there a way through VBA to prevent a particular cell from being changed by a user? this particular cell contains an auto increment invoice number (by vba function) and don't want users to change this to any other number.
Hi Dinesh, I have not set any password for my sheet, i have just protected the sheet using Format cells option under Home tab in Office 2013, so how to write a script for that.
Sir I have a problem coding in Excel VBA, which needs your guidance. When executing a macro with the command button in Excel, an error occurs while copying and pasting the formula via VB code. I want the VB code to run and the previous line formula to be pasted into the next line and the formula to be hidden and protected without any errors. While the rest of the cells remain unprotected for data entry. Can you guide me in this regard?
Unprotect the worksheet using the password. This VBA tutorial will help: www.exceltrainingvideos.com/run-vba-code-macro-on-protected-sheet/ Or search www.exceltrainingvideos.com
Hello Dinesh,I have a VBA which runs automatically when the file is oppened. Now I need to password protect the whole file/workbook, which is easily done. Although the issue is that next time I open the protected file, I got a pop up window saying that my macros have been disabled and can not run them. Would you happen to have any workaround. So far I didnt find anything usable. Thank you, Joe
Hi sir, I am new to VBA, I tried searching a lot about if I could extract data from website which is supported on Mozilla Firefox into excel by macro . I could not find anything relevant. Also the specific site has a login. Please guide me in this. It will be really helpful. Thank you.
@@Exceltrainingvideos Sir.. Let suppose i am having 4 sheets and in first sheet i have user form and connected with rest three sheets through macro. Now i want to protect 2nd ,3rd,4th sheet. And also want can access these three sheet from user from , means from first sheet. Tq
Hi Sir, How should I protect the file after copy the data: Below are the scenarios, Where I have created a commandbutton to copy a data from sheet A to Sheet B; Hence I would like that after I clicking the commandbutton then the data is transfer to Sheet B then the data at Sheet B will lock for editing. Could Sir assist in this concern.?
HI thank you so much for your videos . I just would like to ask. I am currently using Excel 2016 on my macbook but the toolbar and the userform features do not appear. Toolbar had been greyed and there is no userform open on the insert option. I have a macbook air late 2015 on Sierra. Please help. thank you.
Dear Sir, I need to make 3 users for a single excel file like "admin", "manager", "data entry" each has different permission if so how I can do that. Admin full access, Manager allowed to make few calculation, data entry is allowed to enter the data.I was trying to make use of User forms. Will you be kind enough to give me some light on this area. Thanks in advance.
Mathews Jacob The links below may provide a direction:@protect-specific-excel-worksheets-using-vba/@protect-worksheet-using-excel-vba/@how-to-hide-excel-vba-code-using-password/@compare-userform-data-with-worksheet-data/
Hello Sir, I tried your code, it worked. However I am using pivot charts and in it after applying your code my slicers stopped functioning. Can you suggest a way in which the sheet remains protected after running the vba code but the slicer (time filter) associated with the pivot tablets keep on working
it is great help. thank you. it's work with me but my sheet has another codes where has some dropdown list ,whenever I change the answer from the list that protection never work. What do I need to do sir?
Good evening sir, I created an attendance report sheet with a column header called "student status" with a drop down box that includes "Dropped", "Completed" and "Continuing". On another sheet is the following month's attendance report, that I have added a command button to add any continuing students from the prior month. This saves my end users the time and trouble of copying and pasting the data onto the other sheet. It works beautifully, however, I needed to protect some of the cells, to keep instructors (end users) from accidentally deleting any formulas, I find that the command button does not work while in protection mode. Is there a special code that I can use for to allow the button command to work even in protection mode so that my end users can still click on the button to paste the "continued" students?
What if sheet 1 and sheet 2 are protect and you wanted to copy and paste special values from sheet 1 into sheet 2 . But both tabs were protect how would unprotect and protect both sheets in this scenario.
thank you I figured it out the coding is slightly different tho, Instead of Activesheet. you use Sheet1. This is where I was having difficulties hen using the code in this video.
hi Mr Dinesh first i want to thank u for the video i was searching for , i already used it on macro buttons , but it didn't work on combobox made by developer and macro , so i need a code to make vba combobox work with a protect worksheet . and thank you again.
this is the code Private Sub ComboBox1_DropButtonClick() ComboBox1.ListFillRange = "newaccounts" Me.ComboBox1.DropDown ActiveSheet.Unprotect Password:="asdf" End Sub Private Sub ComboBox1_LostFocus() ActiveSheet.Protect Password:="asdf" End Sub
this is the code Private Sub ComboBox1_DropButtonClick() ComboBox1.ListFillRange = "newaccounts" Me.ComboBox1.DropDown ActiveSheet.Unprotect Password:="asdf" End Sub Private Sub ComboBox1_LostFocus() ActiveSheet.Protect Password:="asdf" End Sub
Hi Sir I am new to VBA, but is it possible to run vba code on the protected sheet data , when data is already linked to other protected sheet(s). for example in your video , if I assume that the data for sorting is linked to some other protected worksheet, then what would be the VBA code. THANKS...
First unprotect the file, do your actions and then protect it again before closing as shown in the video. For more solutions search www.exceltrainingvideos.com/
😂 you really confused your student and you haven’t solved his problem. Your student wanted to know how to have sort button with protected sheet without error and you explained here how to password protect a sheet in vba. Lol 😂 you solved the problem by removing his fancy button. Poor student!
I am not a person who has been educated in VBA (but intend to). My users wanted a multi-select dropdown list in the Excel form I provided, which I was able to accomplish, but I had to protect the worksheet...and it would not work!!!! Your good instruction has made it possible for me to give them what they wanted...and for me to finally get a good night's sleep! Thank you, Thank you.
Why can't everyone make it as straight forward as you!? Thank you!!
Wow! Please share my CZcams channel on Excel with your friends too.
Worked nicely for me; I had an input sheet and and a separate output sheet. I kept the command buttons on the input sheet, inserted your code lines you showed, and got the output sheet protected. Many thanks Dinesh!
Thank you Sir... This was the simpliest solution for me. I have zero knowledge in VBA and it worked like a charm first time I tried it unlike other solutions I had come across online.
Some one please give this man a cookie!! You just helped me so much ch thank you!!!!!
Thank you so much for this video Dinesh. This is exactly what I was looking for.
You are welcome. Please share with others and spread the knowledge.
Thank you Sir ! You just helped me too much ! Be blessed 🙏
🤝 From Burkina Faso 🇧🇫
Glad to hear that. Please share with your friends too.
Thankyou so much!! in any sheet, For Any command button vba code, just adding activesheet. Unprotect and activesheet.protect worked perfect!!
You are welcome. Please share with your mates also on social media.
Very Nice Guidance !! Great one!!!
Good morning Sir..your voice and method of explanation is owesome.
Hi Sirji
This is the first time I've used your help and it has worked easily
Thanks thank you very much
You are welcome. Share with your friends to spread the knowledge.
Thank you so much, exactly what I was looking for. Very helpful!
Great to hear!
Thank you very much, it was great help!
Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
So many good projects 👍
Thanks!
Thank you so much.... Exactly i m looking for...,
Most welcome 😊 Please share my Excel VBA CZcams channel with your friends too. Thanks.
Highly valuable inputs
Welcome!
This has been a huge help to me. Thank you for the video!!
Worked like magic! Thanks a ton!
You are welcome. Share with your friends also on social media to spread the knowledge.
Thank you very much!
Your video was xtremely helpfull
You are welcome. Please share with your friends also.
Excellent! Thank you.
Hello Dinesh Thank you for sharing this.
THANKS SIR
REALLY USEFUL VIDEO
So nice of you! Please share this VBA tutorial in Excel with your friends also on social media like Facebook, etc.
@@Exceltrainingvideos ok sir
This is Amazing. Thank you so much..
Glad you like it!
Thanks for the video sir..This thing was screwng me since 3 days
Glad I could help. Please share my VBA tutorials with all your friends also on social media.
This works sweet! thank you very much! :)
Don't forget to share with your friends! Thanks.
Thanks 👍
Thankyou Sirji
Welcome! Please share the VBA tutorials with your friends on social media.
Thank you Sir
Welcome! Please share the Excel VBA tutorials with your friends.
You can also add the code in any sheet to call any other sheet. Like writing code for sheet2 : "sheet1.unprotect" & "sheet1.protect".
Can you share your code?
Thanks, it worked.
nice work sir
Good man, Dinesh!
THANK YOU
Thanks
Welcome! Sharing is caring!
What formula can I use: I have employee schedule: "Rosa" this person have a daily position where she will work. That work position is another sheet. / cell"A" Rosa / cell "B" 8am-5pm / cell "C" drop box asking what position she will be. Now by selection from de drop box X position. "Sheet 2" can take that information in put her name "Rosa" into the right position. Remember in sheet 2 there15 difference position I by clicking drop box dishwasher "Rosa" will be dishwater on sheet 2.
Hello Sir,
I watched your video.Great video & with good presentation. Sir,I want your advice.I always like to learn a new thing like Excel,word access,VBA. (Still Learning) But i dont know how to use this knowledge in professional life.
I want to become Trainer but most of recruiter want experience trainer.Well,i need your precious advice that what are other options? Sorry to ask you question like this but i would be grateful to you if you help me.
It doesnt work if you have mumltiple sheets connected to your macro. Ive a lot of calls on some modules and unpro and pro is not reliable. Still looking something to work.
What's your background?
sir can I get coding for getting values of multiple workbooks in a single work sheet/form. Beacuse i have data in different work books
Dear sir,
Is there a way through VBA to prevent a particular cell from being changed by a user? this particular cell contains an auto increment invoice number (by vba function) and don't want users to change this to any other number.
Sir, very good knowledge from I got this video.. but can I open password protected another workbook on my current sheet..
Why did you delete the button?
Hi Dinesh, I have not set any password for my sheet, i have just protected the sheet using Format cells option under Home tab in Office 2013, so how to write a script for that.
Sorry for late reply. I am Bcom Graduate and working now in BPO.
Sir I have a problem coding in Excel VBA, which needs your guidance.
When executing a macro with the command button in Excel, an error occurs while copying and pasting the formula via VB code.
I want the VB code to run and the previous line formula to be pasted into the next line and the formula to be hidden and protected without any errors. While the rest of the cells remain unprotected for data entry.
Can you guide me in this regard?
This link will guide: czcams.com/video/e0defRXXpdk/video.html
I want to do same process with sharad workbook. How to do?
Hello sir , I have a macro protected excel sheet, I want to see the formulas . How to remove the protection.
Unprotect the worksheet using the password. This VBA tutorial will help: www.exceltrainingvideos.com/run-vba-code-macro-on-protected-sheet/
Or search www.exceltrainingvideos.com
Hello Dinesh,I have a VBA which runs automatically when the file is oppened. Now I need to password protect the whole file/workbook, which is easily done. Although the issue is that next time I open the protected file, I got a pop up window saying that my macros have been disabled and can not run them. Would you happen to have any workaround. So far I didnt find anything usable. Thank you, Joe
Hi sir, I am new to VBA, I tried searching a lot about if I could extract data from website which is supported on Mozilla Firefox into excel by macro . I could not find anything relevant. Also the specific site has a login. Please guide me in this. It will be really helpful. Thank you.
Your video is very useful..sir Is it possible to run macro in inactive protected sheet from active sheet..? Pls help..
Can you elaborate a bit more?
@@Exceltrainingvideos Sir.. Let suppose i am having 4 sheets and in first sheet i have user form and connected with rest three sheets through macro. Now i want to protect 2nd ,3rd,4th sheet. And also want can access these three sheet from user from , means from first sheet.
Tq
Sir, is there any way to protect my excel workbook reselling to other person by who got my excel workbook from me. Thank you in advance
You can purchase special software at a price.
Hi Sir, How should I protect the file after copy the data:
Below are the scenarios,
Where I have created a commandbutton to copy a data from sheet A to Sheet B; Hence I would like that after I clicking the commandbutton then the data is transfer to Sheet B then the data at Sheet B will lock for editing.
Could Sir assist in this concern.?
The link to this VBA lesson will guide: www.exceltrainingvideos.com/tag/automatically-lock-worksheet-cells-after-data-entry-using-vba/
Hi Sir,
I tried; Nonetheless, it shows compiled errors.
Could Sir walk me through again?
HI thank you so much for your videos
. I just would like to ask. I am currently using Excel 2016 on my macbook but the toolbar and the userform features do not appear. Toolbar had been greyed and there is no userform open on the insert option.
I have a macbook air late 2015 on Sierra. Please help. thank you.
www.rondebruin.nl/
I seems some features are not enabled when in Mac Office 2016 =0(
Dear Sir,
I need to make 3 users for a single excel file like "admin", "manager", "data entry" each has different permission if so how I can do that. Admin full access, Manager allowed to make few calculation, data entry is allowed to enter the data.I was trying to make use of User forms. Will you be kind enough to give me some light on this area. Thanks in advance.
Mathews Jacob The links below may provide a direction:@protect-specific-excel-worksheets-using-vba/@protect-worksheet-using-excel-vba/@how-to-hide-excel-vba-code-using-password/@compare-userform-data-with-worksheet-data/
Hello Sir, I tried your code, it worked. However I am using pivot charts and in it after applying your code my slicers stopped functioning. Can you suggest a way in which the sheet remains protected after running the vba code but the slicer (time filter) associated with the pivot tablets keep on working
Try again
it is great help. thank you.
it's work with me but my sheet has another codes where has some dropdown list ,whenever I change the answer from the list that protection never work.
What do I need to do sir?
Search www.exceltrainingvideos.com and work harder!
Good evening sir, I created an attendance report sheet with a column header called "student status" with a drop down box that includes "Dropped", "Completed" and "Continuing". On another sheet is the following month's attendance report, that I have added a command button to add any continuing students from the prior month. This saves my end users the time and trouble of copying and pasting the data onto the other sheet. It works beautifully, however, I needed to protect some of the cells, to keep instructors (end users) from accidentally deleting any formulas, I find that the command button does not work while in protection mode. Is there a special code that I can use for to allow the button command to work even in protection mode so that my end users can still click on the button to paste the "continued" students?
This link might help: www.exceltrainingvideos.com/allow-data-entry-specific-worksheet-cells-in-excel/
Or search www.exceltrainingvideos.com
Dear Sir,
I want to apply protection to only one column and rest of the file must be editable.
These Excel learning tutorials will help:
www.exceltrainingvideos.com/protect-worksheet-using-excel-vba/
www.exceltrainingvideos.com/allow-editing-in-protected-worksheet-with-vba/
www.exceltrainingvideos.com/automatically-password-protect-excel-worksheet-and-lock-cells-after-data-entry-using-vba/
www.exceltrainingvideos.com/protect-worksheet/
www.exceltrainingvideos.com/allow-data-entry-specific-worksheet-cells-in-excel/
Dear, is possible to entry the data by using user form in hided worksheet so no one can see the data ,only allow to enter the data
Try it out and share it!
What if sheet 1 and sheet 2 are protect and you wanted to copy and paste special values from sheet 1 into sheet 2 . But both tabs were protect how would unprotect and protect both sheets in this scenario.
Unprotect both the sheets as you unprotect one! Also, vice-versa.
thank you I figured it out the coding is slightly different tho, Instead of Activesheet. you use Sheet1. This is where I was having difficulties hen using the code in this video.
Where is the button?
This link will help: www.exceltrainingvideos.com/how-to-assign-recorded-macro-to-command-button-in-excel/
hi Mr Dinesh
first i want to thank u for the video i was searching for , i already
used it on macro buttons , but it didn't work on combobox made by developer and macro , so i need a code to make vba combobox work with a protect worksheet .
and thank you again.
What VBA code have you written?
this is the code
Private Sub ComboBox1_DropButtonClick()
ComboBox1.ListFillRange = "newaccounts"
Me.ComboBox1.DropDown
ActiveSheet.Unprotect Password:="asdf"
End Sub
Private Sub ComboBox1_LostFocus()
ActiveSheet.Protect Password:="asdf"
End Sub
this is the code
Private Sub ComboBox1_DropButtonClick()
ComboBox1.ListFillRange = "newaccounts"
Me.ComboBox1.DropDown
ActiveSheet.Unprotect Password:="asdf"
End Sub
Private Sub ComboBox1_LostFocus()
ActiveSheet.Protect Password:="asdf"
End Sub
Works fine. The combo-box is an ActiveX control on your worksheet.
how to run macro in shared workbook?
You can run existing macros.
Hi Sir
I am new to VBA, but is it possible to run vba code on the protected sheet data , when data is already linked to other protected sheet(s).
for example in your video , if I assume that the data for sorting is linked to some other protected worksheet, then what would be the VBA code.
THANKS...
You'll have to 'unprotect' the sheets.
Thanks sir
Can u please share the vba code to unprotect any sheet in workbook from the active sheet
www.familycomputerclub.com/run-macro-protected-sheet.html
Thanks for your guidance....
🙏
Sir how I run macro in structure protected workbook
www.exceltrainingvideos.com/run-vba-code-macro-on-protected-sheet/
Hello Sir,
I want to Run The VBA Code With a Protect Workbook..
Sanat Pattnaik www.exceltrainingvideos.com/run-vba-code-macro-on-protected-sheet/ czcams.com/video/wv1AgZR9G2c/video.html
This code use in workbook password protected
How to run VBA Code on Protected File
First unprotect the file, do your actions and then protect it again before closing as shown in the video. For more solutions search www.exceltrainingvideos.com/
😂 you really confused your student and you haven’t solved his problem. Your student wanted to know how to have sort button with protected sheet without error and you explained here how to password protect a sheet in vba. Lol 😂 you solved the problem by removing his fancy button. Poor student!
You have a point.
Thank you sir
All the best