Unprotecting Macro Button In A Protected Sheet |
Unprotecting Macro Button In A Protected Sheet - Excel |
|
I have made a sheet that I have to give access to other users as well. But I have protected cells in the sheet so that the formulas can not be altered. But when I do this, macro button stop responding. In fact, they become unclickable. I tried moving the buttons to the unprotected cells but as the macro is working on the part of sheet which is protected, so it is not working.
I want user to be able to click on macro button and have access to some cells of the sheet but the rest of sheet should stay protected.
Can somebody help me please?
I want user to be able to click on macro button and have access to some cells of the sheet but the rest of sheet should stay protected.
Can somebody help me please?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 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.)
(80% Discount Ends Soon!)
Similar Topics
This is weird. The Merge functionality is not working, the Button is grayed down.
The sheet/workbook are not protected, nor shared, there is no VBA code in the file...
Does anyone knows what else can it be? Everything else is working fine!!!
thanks
The sheet/workbook are not protected, nor shared, there is no VBA code in the file...
Does anyone knows what else can it be? Everything else is working fine!!!
thanks
Hello,
i've got the following problem:
I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.
When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event.
After the code is executed Excel shows a message that the cell that was clicked was protected etc etc.
How can I prevent this message from popping up?
I've already tried
Code:
but that didn't work
Thanks
i've got the following problem:
I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.
When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event.
After the code is executed Excel shows a message that the cell that was clicked was protected etc etc.
How can I prevent this message from popping up?
I've already tried
Code:
application.displaywarnings = false
but that didn't work
Thanks
I have protected a worksheet as it has several formulae on that I do not wat anyone else to mistakingly delete. I then have spent time on setting up a sheet to track changes (just in case this has any bearing on the problem). This is the only thing that I think I have changed since protecting this morning and now I cannot unprotect the sheet. I have not protected the workbook and the unprotect sheet function is now greyed out.
Any ideas?
Any ideas?
I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
Hi Excel experts,
I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.
Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.
Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.
Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.
Can anyone assist?
Thanks!
I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.
Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.
Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.
Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.
Can anyone assist?
Thanks!
Hi all,
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.
I'm new to macros and would appreciate any help this board can offer. Thanks...
I'm new to macros and would appreciate any help this board can offer. Thanks...
Corporate edict.
I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".
If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).
What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."
I think I am close, but I am getting an "End If without block If" error on the If Clause.
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".
If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).
What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."
I think I am close, but I am getting an "End If without block If" error on the If Clause.
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
Hi
I need to open a password protected workbook using VBA.
I've tried the code below but I still get prompted for a password.
Code:
How do I get it to open automatically???
I need to open a password protected workbook using VBA.
I've tried the code below but I still get prompted for a password.
Code:
Workbooks.Open Filename:="\\HOME\Working\Report.xls" _
, Password:="xxxx"
How do I get it to open automatically???
Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:
We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.
So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.
The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.
I ran a search on the forums and found something similar, but not quite what I was looking for.
Any help?
We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.
So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.
The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.
I ran a search on the forums and found something similar, but not quite what I was looking for.
Any help?
I have a excel spreedsheet that contain external data. I would like to put a
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
Hi there,
I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!
In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:
=Sheet1!A3 (or whichever cell it is)
That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).
If anyone could point me in the right direction, I'd be very very grateful. Thanks.
I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!
In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:
=Sheet1!A3 (or whichever cell it is)
That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).
If anyone could point me in the right direction, I'd be very very grateful. Thanks.
Is there any way to stop a macro from running when it gets hung up and my excel is "Not Responding"? Now I have to blow it up which is not a desirable method especially when I have made changes that I wanted to save.
Hello everyone!!!!
Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO
We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.
How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.
Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)
Thanks so much!!!!! Muchly appriciated.
Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO
We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.
How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.
Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)
Thanks so much!!!!! Muchly appriciated.
We have a large (4,000+ rows) excel worksheet from a prior employee that I
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks
Hi! I am new to Excel and I am creating fillable forms in excel which are protected so that users canonly input data in certain cells. I am using Excel 2003. The users told me that they do not want text to go beyond the edge of the page when they are inputting data. They also want the words to wrap when they reach the end of the sentence. I am also using check boxes(yes/no) and Ifind it difficult to make the adjacent cells fillable only Thanks for your help and correct me if I am wrong as I am new to this board.
Some time ago I created a spreadsheet and greyed out the areas that weren't needed. Now I need to expand the spreadsheet and use more columns. Trouble is I've forgotten how to unhide those columns. I didn't write any macros and usings the right-click unhide method is proven futile. The sheet isn't protected which is puzzling since I can't get the mouse to even highlight any of the greyed out areas.
Any suggestions would be fantastic!
thanks
Any suggestions would be fantastic!
thanks
Hello,
I'm pretty new at VBA and was wondering if you could help me out on this:
I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?
MS Access is not an option for me so I was wondering if you could help me do this in excel.
thank you so much and would really appreciate to hear from anyone soon.
I'm pretty new at VBA and was wondering if you could help me out on this:
I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?
MS Access is not an option for me so I was wondering if you could help me do this in excel.
thank you so much and would really appreciate to hear from anyone soon.
Hi All,
Random question i have a large Excel Workbook (which is protected) and has over five sheets on it - however one sheet has randomly decided not to scroll... yes i know sounds random! If i use the cursor and down arrows the selection just disappears off the screen.
The page will scroll if i filter by one field, but not if i select (All) for all filters.
All other worksheets scroll fine.
Any suggestions?
Thanks in advance.
Cheers Kaite
Random question i have a large Excel Workbook (which is protected) and has over five sheets on it - however one sheet has randomly decided not to scroll... yes i know sounds random! If i use the cursor and down arrows the selection just disappears off the screen.
The page will scroll if i filter by one field, but not if i select (All) for all filters.
All other worksheets scroll fine.
Any suggestions?
Thanks in advance.
Cheers Kaite
I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.
Help
Help