Dear Team
please help me in VBA code to restrict saving of Excelfile if there is any value in column A and no data in corresponding cell in column B,C,E,F,G,H,I,J,K,N,O,P,V,AA is left blank
Also i want to restric copy pasting of data in the excel sheet(or on perticual cells), so that evry team member should enter the data manually
If your columns are perfectly balanced (no extra entries in column A, or any of the other columns) then you can use this in the code module of the Thisworkbook object in your project:
To prevent pasting on a specific sheet, right-click the sheet tab, select "View Code" and paste this code into the window that appears
Please Login or Register to view this content.
To prevent specific ranges from being pasted over, usePlease Login or Register to view this content.
Please Login or Register to view this content.
Bernie Deitrick
Excel MVP 2000-2010
Hye Bernie
thanks for helping but the code is not working
The scenario i am speaking, would have data in A column for sure but the coresponding cells in other columns like B,C,E,F,G,H,I,J,K,N,O,P,V,AA is occasionaly not filled by users
I required to see if there is any data in A1 to A2000 (example the rows could vary)the corresponding cells of columns B,C,E,F,G,H,I,J,K,N,O,P,V,AA (1 to 2000) to also be filled in accrodance or sheeet should not be saved
Try it this way:
Please Login or Register to view this content.
Hey Bernie
Wow that was quick, i can check if the code is working or not as i am getting the error
Run Time error "1004" No Cells were found
Is A filled with values, or formulas? You should be able to just change
For Each rngC In Intersect(ActiveSheet.UsedRange, Range("A:A")).SpecialCells(xlCellTypeConstants)
to
For Each rngC In Intersect(ActiveSheet.UsedRange, Range("A:A"))
Hey bernie
All the cells in column A would have values no formula
Hey Bernie your code works like a charm
but i have one other issue now in the excel i have 2 VB codes which i need to put on eis yours and the other code is for checking if there is a validation in the sheet if there is and any user tries to copy paste on the cell which has validation excel would not allow to do so
the code isattached is the filePlease Login or Register to view this content.
the problem is that the sheet goes in loop and gives run time error 28 out of stack space
Also i can i stop user to copy paste complete row in the sheet from other source
Last edited by anuj_sethi1043; 08-13-2014 at 07:20 AM.
Though this would work to prevent the pasting...Please Login or Register to view this content.
Please Login or Register to view this content.
Hey bernie thanks for the code it works bang on
but user can still copy and paste entire row from another workbook and paste on this workbook bypassing the restrictions we had put
Doesn't your Worksheet_Change event stop the entire row pasting?
no it does not
Try using this change event for the sheet with the data validation.
Please Login or Register to view this content.
hey bernie..if i copy and paste an entire row from different worksheet it allows to do that
When I tested it, the change in the cells in ValidationRange triggered the undo...
When you say Worksheet, do you mean another workBOOK? We could use the workbook activation event to set the cutcopymode to false.....
hey yes i meant another workbook..
Then put this into the Thisworkbook object's codemodule:
Private Sub Workbook_Activate()
Application.CutCopyMode = False
End Sub
Re: VBA to prevent saving of excel if cell is blank
Hey bernie
tried putting the code in thisworkbook but it did notwork
can you place the same in sample workbook or in the one i attached would really appreciate that
Private Sub Workbook_Activate()
Application.CutCopyMode = False
End Sub
Here you go....
Business MIS Format.xlsm
This is the code now, which should disable all pasting
Please Login or Register to view this content.
hey this code disabled the copy paste option itself
can we disable copy paste only on the range defined or specific column require..
is it possible?
Try being more specific.
Please Login or Register to view this content.
Hey bernie..sorry was out of town so could not reply, i would try to explain in detail
the VB code works like charm and stops user to copy and paste in the excel
but if user tries to copy and paste..a complete row from other excel workbook, it is easy possible which i wanted to restrict
Hey bernie..sorry was out of town so could not reply, i would try to explain in detail
the VB code works like charm and stops user to copy and paste in the excel
but if user tries to copy and paste..a complete row from other excel workbook, it is easy possible which i wanted to restrict
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks