Posts by Sammie37


    I have VBA code that locks my workbook when closed if someone unlocks a sheet:

    I found code that will stop someone from clicking save button but the only way I know to get it to work is to place it in ThisWorkBook module by itself. I'm trying to get both codes to work. Can I put them in together somehow? I tested doing that to no avail. Here's the other bit of code

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        If Not SaveAsUI Then
            Cancel = True
            MsgBox "You cannot save this workbook. Use Save As if this is not a blank", vbInformation, "MJM"
        End If
    End Sub

    FYI, I've created workbooks for employee"s. They all have "blank" copies. When they fill them out I have buttons for them to save their work that re-names the blank rather than overwriting it, however, its second nature to just click the "save" button. If they do, they've lost their "blank" so I love this idea which will make them pause and not do it. Occasionally, they also unlock their workbook blanks and the other command ensures its re-locked when they close it so they dont accidentally screw up any formulas next time they open it.

    Thanks for any help!


    Thanks, that did the trick, I also simplified it a bit after adding the quotes and playing around. Looks like this now and works perfectly.

    Sub ClearDatatoShowTotal()
    Sheets("Proposal 2").Range("A46").ClearContents
    Sheets("Proposal 2").Range("J46").Formula = "=IF(ISNUMBER(R46),ROUNDUP((Q46/R46),2),"" "")"
    Sheets("Proposal 1").Activate
    Sheets("Proposal 1").Range("J47:K48") = "Total:"
    Sheets("Proposal 1").Range("C25:H25").Select
    End Sub

    Mark this as solved if there is a way to do that. Love this forum, I tried all sorts of things, never would have thought of quotes causing the problem.


    I have a button assigned to a macro where I need to "trick" other formulas by entering some data into a couple cells on another sheet. Anyway, here's the code, it works as intended:

    Sub EnterDataforHideTotal()
    Sheets("Proposal 2").Range("A46") = "1"
    Sheets("Proposal 2").Range("J46") = "-1"
    Sheets("Proposal 1").Range("J47:K48") = " "
    End Sub

    My problem lies with undoing that macro. I have another button assigned to the following macro to "Undo" what the above one did and to also to enter a formula in a specific cell

    When I try to run this macro, I get the following error (I've tried several variations of the above, always get an error)

    When I click debug, it always points to the line trying to insert the formula:

    Any ideas to get this to work?

    Thanks in advance for any help

    BTW, I did 12 macros just in case clicking "done" on one sheet, executed the macro on another sheet. It doesn't but I was preparing for contingencies should I need to change the reference from active sheet to the name of a sheet for example. Anyway, after inserting 400 buttons and 300 macros's before I quit and came here, 12 was a breeze :)

    Hi All,

    I went with Carim suggestions. I did have to edit it though as I got an error first time I tried, just had to make the last "date" formatting line into 2 separate lines. Here's the final code I am using:

    I did 12 macros, one for each month, just felt safer doing it this way, and assigned a button on each page to execute it. I liked adding a warning to the message box, I also added another "alert" in the cell that used to house the :done" buttons, thats 2 warnings for the user, so if they "clear" the wrong row by accident, no one to blame but themselves. Here's a screenshot:

    royUK I tried your code, couldn't figure out how to execute it. Double clicking didn't do anything. Let me download your attachement, I didn't do that yet

    Carim I like your approach with the message box, changing the text better than entering the row #, fewer steps for user the better. I'm going to test it


    Actually the first response's code did the trick except it could be easy to "clear" the wrong row if the user accidentally has their cursor in the wrong row. It does allow me to insert one button and one macro to "clear" any row and I may just go with this.

    I've attached a sample workbook. Row 9 is set up to run the "clear" macro. As you will see, its a color coded checklist type system for tracking the progress of an order. All conditional formatting is based on the dates entered in specific cells and today's date, with one extra format based on entering specific text in one cell, there is a lot of it but it works the way I want. Once an order is complete (invoiced), we just want to "gray" out the row. I used clear formatting because of all the conditional formatting and changed the dates cells back to dates for historical reference purposes since "clear formatting" changes the dates.

    Keep in mind, their will be 12 worksheets, one for each month, named for that month. So my thought in my first post was, where you see the "done" buttons, is there a way to delete that button and just use that cell to trigger a "clear" macro by typing something in that cell like "DONE".


    I have a relatively simple macro that does exactly what I want it to, its for a checklist at work we use to track orders until completion, when complete, we "gray" out the cells and clear the formatting in that row (A3-W3), keeping only the dates for reference. There's a ton of conditional formatting to clear, so I use the clear function across the entire range of cells then reformat the dates. Here's the macro then the problem I need a solution to.

    Sub finished_1()
    If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
    Range("B3").NumberFormat = "mm/dd/yy"
    Range("L3").NumberFormat = "mm/dd/yy"
    Range("S3:T3").NumberFormat = "mm/dd/yy"
    Range("A3:W3").Interior.ColorIndex = 16
    End Sub

    So, I inserted a button to click to execute the macro. Here's the problem, there are 200 rows per month and twelve sheets, one for each month, which leads to 2400 buttons and macros, yikes. I need a better way!

    My thought, instead of a button, I have a free cell in each row where the button is, is there a way I can type some text like "run" in that cell and have that text execute a macro for that row? The macro would need to identify the row I'm in based on the row I'm typing in, if this is possible I could have 1 macro instead of 2400. I like the button but don't see any way to use it and have the macro know which row the button is referencing.

    Anyway, I'm open to suggestions, I want it to run all the same commands just have one macro for any row or at most 12 macros since each month may need it own.

    Thanks for any help. BTW, I've already inserted 300 macro's and said, Ugh, there has got to be a better way


    royUK Hey, just an FYI, something strange happens at work, this is the second time. I used your code on my home laptop, it worked as expected, I copied the workbook to my work computer, and again from my home laptop (connected remotely), tried again, it worked again. I get to work and open the workbook and the code no longer works. I'm just gonna do it the harder way without a macro and have to type a password twice everytime I need to unprotect and re-protect a sheet in the workbook. It must have to do with some network admin settings when I'm at work. Its really frustrating as I am being asked to do these things but they limit my ability to do so. Thanks for trying, code is good, work is bad!


    Agreed. Not possible from my research. Oh well, my colleague will get one thing while losing another. Just means she'll need to type out an easy formula instead of selecting a cell then typing the rest of the formula. Thanks again.

    royUK Dangit! I just did some testing to be sure using this Macro would not lead to unexpected problems. It did, but only one. I see you made it so only unlocked cells could be selected which is what I wanted, however, I have one range of locked cells that I also need to be selectable, can that range be added to the macro. Thinking I'm smarter than I am I tried adding this the line below but that didn't work.

    wSheet.EnableSelection = xlUnlockedCells
    wSheet.EnableSelection = xlRange("Q26:Q43")

    Hi royUK

    Ok, one more thing. can I also add the option to NOT let the user select locked cells. This way even though formatting is allowed on all cells, the user can only format unlocked cells. BTW, this is all to prevent a certain user from screwing up the workbook as she has a history... but I also want to accommodate her needs, ie allow formatting of unlocked cells. The protecting the workbook automatically when closing is purely for my convenience. I appreciate all you do to help people!!!