Posts by lswanson

    I have a workbook for which I would like to protect the worksheets, while still allowing my code to alter the worksheets, which can be done with the line:

    Sheet1.Protect Password="abc" UserInterfaceOnly:=True

    However, I want allow some users (who know the password) to be able to unprotect the sheet if they need to edit it, then turn the protection back on after they are done. How can I make sure that they use my macro to protect the sheet with UserInterfaceOnly set to true, rather than the standard way to turn on protection?

    That is, is there a way I can prevent the user from being able to protect the sheet with the Tools->Protection->Protect Sheet menu item?


    Re: Undo Macro

    Does this work for you?

    Note: you MUST ensure that your workbook includes a worksheet called "Undo".

    Re: Enabling Macros Without Re-opening Worksheet

    Hey, that is a good idea! Not sure if I will use it, but still... very innovative. If I start needing to change the enabled status of my workbooks more often I will certainly use this as a starting place for whatever system I end up implementing. Thank you very much.

    Re: Failure to Clear Control References

    Well, the "kWorkbookData" object is a project level variable & the variables referencing the combo boxes are members of kWorkbookData, or members of members of kWorkbookData, or... well, you get the picture. So destroying the kWorkbookData object SHOULD start a cascade of Class_Terminate events destroying all of the member objects. Point being there are too many different places that objects are supposed to get destroyed to easily trace what is happening & find out where the reference is not being cleared.

    When I hide or delete cells that are referred to by the RowSource property of a combobox in my code is causes all sorts of weird errors. Sometimes it just causes a "catastrophic error" & crashes Excel, but sometimes it lets me debug & I can trace the problem back to the Change event of the combo box being fired.

    In any case, I think I found (just by luck) where I was missing a "Set _____ = Nothing" & so have solved the problem (hopefully. I am still crossing my fingers). So I suppose you could count this thread as solved?

    Re: Failure to Clear Control References

    Quote from Andy Pope

    Maybe you could use the watch window in VBE to watch the userform and combobox, espcially when the userform closes and unloads.

    How are you closing and unloading the userform?

    Yeah, I have thought of using the watch window, but it would be rather complicated (I am using classes & dynamically allocated arrays so it would be a bit tricky to figure out exactly how to reference what I want to reference in the watch window. & I have quite a few controls that I would have to watch...)

    I am closing the userform with a command button:

    Private Sub cmdClose_Click()
        '   Close the wizard
            Unload Me
    End Sub

    And freeing the memory in the Userform_Terminate event:

    Private Sub UserForm_Terminate()
    '   Release memory
        Set kWorkbookData = Nothing
    End Sub

    kWorkbookData is an instance of the complicated class that I use for... well, pretty much everything in this project.

    In C++ I developed a method to save the address of the target memory every time that memory was dynamically allocated or deallocated & then I could compare those two files to see if there was any memory being allocated but not deallocated. I am not really sure if there would be any way to do this with VBA...

    I am working on a very complicated project using a userform where I set references to several combo box controls. When I close the userform it should clear all of these references. However, for some reason it is not, & so when I change any data in the sheets to which combo boxes have their rowsource property set, it fires the change event for those combo boxes & causes all sorts of errors since the userform is not open. Is there any good way to track my references so that I can try to figure out why the memory is not being released?

    Re: Scanning Range instead of single cell

    So let me see if I have this straight. Is this what you want:

    1) When the user enters a value into column 'X' from row 5 to the last data row on the "Approved" sheet, start the procedure for the row into wich the user entered data.
    2) Ask the user if he is sure that he wants to process the data
    3) If the user says yes, copy the cells in columns B to F, & J to V in that row to the next empty row in
    - the "Del 2006" sheet if the value was a 1
    - the "Add 2006" sheet if the value was a 2.
    4) Add the date that this process took place in column H of the current output row.
    5) Delete the value (1 or 2) that the user entered in the "Approved" sheet

    Can I assume that the "Del 2006" and "Add 2006" will already be formatted correctly with the proper headers and everything?

    Is it really necessary to format the cells? Because when you copy & paste the data from the "Approved" sheet, the format will be copied over too.

    Anyways, here is what I have now, check it out & let me know if it works for you.

    In the "Approved" worksheet's code module:

    In a standard code module:

    Re: Scanning Range instead of single cell

    Quote from Baller3356

    I caught that error. This only works for Row 5. The others rows do nothing when I enter in a value. After I enter a value in row 5 it then checks the other rows. How do I make it so it checks the other rows independantly?

    Once again I ask, can you please post an example worksheet? Something with the code you are currently using, as well as a good example of what your data is like. An example of what you want the output to be would also be very nice. If you do this, I will take a look at the code & see if I can make it work for you. Without this, it will be a lot harder for me to give you any answers if I even can at all.

    As it now stands, the "CheckX" function works for all of the rows in one run of the procedure. If you want to do this for only one row per run of the procedure, then "CheckX" will have to be changed. This is something I can do, but I would still appreciate it if you sent me your example workbook. Thanks.

    Re: Sum last 9 cell entries in a row greater than 0

    Haha, I threw that together really quickly, so it is pretty messy... hopefully it works! (It should at least give you a starting point.)

    Re: Merge Row data, with critera

    Quote from choa32

    Thanks again lswanson. That perfect

    Glad to hear that!

    Quote from choa32

    Now i need to understand the codes : D

    If you have any specific questions, feel free to ask for explanations.

    Re: Merge Row data, with critera

    So does this do what you want? I just changed the bit where it decides whether to sum the data (for numeric data, which changes) or just use the data from the first instance (for strings, which do not change).

    Re: loop a script a fixed number of times

    When you get that error in a line that refers to a workbook it is because no workbook exists with the name that you are giving. Check out the value of the SZ_WS_TEMPLATE constant (it can be found at the top of the module). Make sure that this string is the same as the name of the sheet holding your template.

    Re: loop a script a fixed number of times

    That is significantly better, thank you. (Though it would help if your example output was for one of the actual sets of data in your example data.) In any case, I think I have been able to figure out what you are doing here.

    A few questions:

    Are you sure that setting formulas to refer to the cells in the data sheet is what you want to be doing? (Rather than just copying the cell value...)

    Actually, one more question, do you want to run this procedure for the "Total" row as well, or just for each of the employee rows?

    Can I assume that the "CPI 2006 q3 simon.xls" & "monthly review.xls" workbooks are in the same folder? It would make things a lot easier if they were.

    There does not seem to be any example data in columns U to X for the Call Observations, so I cannot be sure that I did the right things for those.

    About the naming of the monthly review files, do you want to use the employee's full name or just their surname?

    In any case, this should probably work for you:

    Try it out & let me know how it works!

    Re: loop a script a fixed number of times

    Um, I do not mean to be rude, but that is a pretty useless example file. Just one sheet with an empty table with headings that mean nothing to me? If you upload another example sheet, this time with data (it does not have to be actual data, just make up some good examples that is at least representative of what kind of data you will be expecting in your form), as well as including the macros as you currently have them (at least the copy macros for at least two rows so that I can figure out what changes & what stays the same between the different row copying macros), & (most importantly) an example, constructed by hand, of what output you want to achieve with the example data that you provide me, then I will be more than happy to help you. I apologize once again if I am coming off as rude, but this example workbook of yours tells me absolutely nothing about what you are trying to do & so is no help in me helping you.