• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Broken Macro and I don't know why?

Zach

Member
The attached file has 4 different marcos written on the file. each of the macros are assigned to the 4 buttons on the top left corner of the spreadsheet. Both the "Complete" buttons and the "Issue" button worked last week and now error out when they are pushed. I hit the debug button, but I don't understand enough to know why I'm getting errors.

Can someone please help?
 

Attachments

  • NEW Community Start File Copy.xlsm
    180.8 KB · Views: 2
Looks like you have locked the worksheet

Does the cell style "Bad" exist?

Try unlocking the worksheet and see if that helps
 
Hi, Zach!
Tried unprotecting the worksheet?
BTW this is the same as or related to this?
http://chandoo.org/forum/threads/dirty-maros.12913
You were answered there by Luke M and by me, and you didn't answer nor asked any more.
If so, don't split again topics and keep all the question regarding a subject within the same thread.
Regards!

EDITED

PS: He always writes faster... :mad:
 
Looks like you have locked the worksheet

Does the cell style "Bad" exist?

Try unlocking the worksheet and see if that helps

Ok I fixed it, it used to work when the cells were protected. And based on you guys suggestions I looked at how the sheet was protected and someone unchecked the "format cell" box.

I guess I'm going to have to create a macro to protect and unprotect the sheet so someone doesn't have to go into the "unprotect" button on the header bar.

And SirJB i will keep it my questions to the same string, my aplogies.
 
But maybe you guys can help me with another issue.

I have this fun macro Luke helped with, but I forgot a big piece to it. I need to first create a copy of the detail tab, rename it to MASTER SUMMARY, and then do as the following macro say and hide all the grey rows leaving only the Yellow and Blue rows. I missing something in this marco because i can get the copy of the tab but not the renaming or the hiding to work.

Sub FilterColor()
Dim lastRow As Long
Dim i As Long
'Define your password in 1 single spot
Const myPassword = "awbi"

'create a copy of tab
Sheets("MASTER-Detail").Select
Sheets("MASTER-Detail").Copy Before:=Sheets(2)
Sheets("MASTER-Detail (2)").Select
Sheets("MASTER-Detail (2)").Name = "MASTER SUMMARY"

'How far down do we look?
With ActiveSheet
lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With

Application.ScreenUpdating = False
ActiveSheet.Unprotect myPassword
For i = lastRow To 10 Step -1
With Cells(i, "D")
'Hide everything that's gray colored
.EntireRow.Hidden = (.Interior.Color = RGB(128, 128, 128))
End With
Next i
ActiveSheet.Protect myPassword
Application.ScreenUpdating = True

End Sub
 
Hi Zach

I really enjoyed the name of your first post 'Dirty Macros' I had a smirk when I read over it. Anyways here is my take on Luke's original tome. I will attach your original workbook to show workings.

Code:
Option Explicit
Sub FilterColor2()
Dim i As Long
 
ActiveSheet.Unprotect "awbi"
Sheets("MASTER-Detail").Copy Before:=Sheets(2)
ActiveSheet.Name = "MasterSummary"
 
Application.ScreenUpdating = False
    For i = Range("D" & Rows.Count).End(xlUp).Row To 10 Step -1
      Cells(i, "D").EntireRow.Hidden = (Cells(i, "D").Interior.Color = RGB(128, 128, 128))
    Next i
Application.ScreenUpdating = True
ActiveSheet.Protect "awbi"
End Sub

I really dislike looping through ranges but unfortunately the filter functions on colour are not mature enough yet to do multiple colours or exclude a particular colour or I would be waltzing down that path. Should be faster when Msoft rippen that puppy!!

Take care

Smallman
 

Attachments

  • testing file.xlsm
    78.3 KB · Views: 1
Back
Top