Warning

 

Close
Confirm Action

Are you sure you wish to do this?

Cancel Confirm
AR15.COM
3/26/2013 5:42:48 PM EDT
I don't know where else to post this but I'm running into an issue on Excel.



I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges.




IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied.




Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data.




I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet.




Any ideas?
3/26/2013 5:49:17 PM EDT
[#1]
Just put a big warning in a nearby cell that using cut/copy/paste will FUCK UP THE SPREADSHEET AND CAUSE GLOBAL THERMONUCLEAR WAR.

That's what I do.  Seriously.
3/26/2013 5:59:25 PM EDT
[#2]



Quoted:


Just put a big warning in a nearby cell that using cut/copy/paste will FUCK UP THE SPREADSHEET AND CAUSE GLOBAL THERMONUCLEAR WAR.



That's what I do.  Seriously.


Hah! Yeah. Except they still won't read it.

 



It seems common responses to my question are to link two spreadsheets where one is for data input and the other is protected but has the formatting. Makes sense. Was just wondering if there was a way to protect it all on one spreadsheet.
3/27/2013 12:30:22 AM EDT
[#3]
Quoted:
It seems common responses to my question are to link two spreadsheets where one is for data input and the other is protected but has the formatting. Makes sense. Was just wondering if there was a way to protect it all on one spreadsheet.

Similar to the two spreadsheets would be one spreadsheet with two pages, one for data entry and one for pretty viewing.
3/27/2013 1:03:59 AM EDT
[#4]
Quoted:

Quoted:
Just put a big warning in a nearby cell that using cut/copy/paste will FUCK UP THE SPREADSHEET AND CAUSE GLOBAL THERMONUCLEAR WAR.

That's what I do.  Seriously.

Hah! Yeah. Except they still won't read it.  

It seems common responses to my question are to link two spreadsheets where one is for data input and the other is protected but has the formatting. Makes sense. Was just wondering if there was a way to protect it all on one spreadsheet.


You could do it all in one spreadsheet, just protect the part you don't want them to touch.

You will run into issues with multiple people trying to edit the same thing though. It works better to let them enter the data in their own sheet, then pull the data into where you need it and format it how you want to format it.
3/27/2013 3:55:43 PM EDT
[#5]
Alright. I've spent the the last 24 hours (minus maybe about 7 hours of sleep) reading and practicing VBA code.









After a while of mastering INDIRECT functions across two spreadsheets (one for editing, and one for protected viewing), I then moved on to command buttons that caused a very handy sort function (hides all rows that do not contain a highlighted cell date, IE yellow cell for 60 days out and red cell for 30 days out and earlier), and one button to simply unhide all cells. Took a while to get the code right so it would be instant (at first, I was forcing it to perform multiple IF checks cell by cell which took FOREVER).










Then once that was done, I wasn't satisfied with what I accomplished. And heeding advice from someone in here earlier who mentioned to force formatting via a macro on the one page: I played around with it until... badabing, badaboom! Users can now manipulate the data as much as they want without fear of screwing anything up. And I don't have to have a separate linked page or any protected page nonsense.










As a non-desk sitting .mil guy, this is almost like Prometheus delivering fire to mankind. Now I can keep track of my troops' shit easily and pass this along our unit so we aren't re-inventing the wheel every Ops meeting. It's amazing how Office illiterate we are when this could help cut our admin-wasting-time in half.










For anyone interested in what the code is:



EDIT: The reason the hide rows code uses the dates versus color of cell is due to the fact that I couldn't get it to hide based on color back when I was using conditional formatting. It may or may not be possible now that I opted out of conditional formatting for the ChangeEvent highlighting.













Private Sub Worksheet_Change(ByVal Target As Range)
   Dim icolor As Integer
   Dim cell As Range

   If Intersect(Target, Range("C3:T65")) Is Nothing Then Exit Sub

   For Each cell In Target
       icolor = 0
       Select Case cell
           Case "": icolor = 2
           Case Is <= Date + 30: icolor = 3
           Case Is <= Date + 60: icolor = 6
       End Select
       If icolor <> 0 Then cell.Interior.ColorIndex = icolor
   Next cell
End Sub
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Sort"
Rows("3:65").Select
   Selection.EntireRow.Hidden = True
For Each cell In Range("C3:T65")
If cell.Value <= Date + 60 And cell.Value > 0 Then
cell.EntireRow.Hidden = False
End If
Next
End Sub

Private Sub CommandButton2_Click()
CommandButton2.Caption = "Show All Rows"
Rows("3:65").Select
   Selection.EntireRow.Hidden = False
End Sub