Posts by JimmyB

    Good day

    I'm having a problem that I can't figure out. I have a workbook that, when activated, emails out a report of some of the sheets. It works great, but to clean the sheets up, I want to remove the form controls as they are no longer needed. I found the code from Ron de Bruin site to remove the controls. It works great on one sheet, and does exactly as I need provided the sheet is unprotected. I have a sub to unprotect sheets which I use in another area of the program...(called UnprotectAllSheets (attached below)) ...It works great elsewhere.

    I added the code..."For Each sh In Application.ThisWorkbook.Worksheet & "Next", to the appropriate spot in the sub that I got from Ron de Bruin; to remove the form controls. It's called (Shapes4) also attached below. I'v also included the section of the program, which houses the array that I'm saving in the 'Report' file. My problem is that both subs do what they're supposed to, EXCEPT go to the "Next" sheet to perform the necessary function. It does everything well with unprotecting & deleting the form controls...but just on the first sheet of the array.

    Any help will be greatly appreciated.

    I hope that the code went in as readable. Seems to me, that when I used to hit the </> symbol it gave me 2 words to frame the code. Anyway you can respond to me on that if you'd like, so I'll know for the future

    Thanks JimmyB :)

    Hi Fluff

    My program has about 44 sheets, all integrated within each other, using around 100 interactive subroutines that I created from scratch. Also I've emailed the program to myself and my son, over the last year, as I developed all of the stages. No-one could, by any stretch of the imagination, have haphazardly developed the same data code. :)


    JimmyB ;)

    Yeah, I get it. That's kind-of what I thought.

    As an added protection I'll incorporate various data strings, into some of the (hundreds of) Rem statements throughout.

    I understand that the use of VBA cannot be harnessed, but cannot an algorithm be considered as proprietary...thus belonging to myself?

    Thanks JImmyB

    Yup. Right, and I do have a monster password in the VB Protection box upper, lower case numbers & 2 different characters all jumbled up

    Have a great day Roy

    Thanks Roy. I guess if they're savvy enough to break the password, it will be hard to protect it further. I just don't want to sell a hundred thousand HAHA, copies and have someone come up and say they created it and sue me. LOL


    I have an Excel workbook that I want to copyright. I've googled how to do this and have been advised, that I just need to place the following statement in

    cell A2: "Copyright © 2009 {Jim Bondy]. All Rights Reserved."

    I have about 40 sheets in my workbook. All of the Sheets and Macros are password protected. I've worked hard on this for the last year - don't want it stolen.

    1) Do I have to include the statement on all 40 sheets?

    2) Does it have to be in Cell "A2"? I don't have that cell available in every sheet.

    3) Cosmetically it would look messy. Can I put it at the bottom of the page instead?

    4) What's to stop a bad guy to crack my password and insert his own statement?

    I have emailed the program to myself, periodically throughout the process of development, over the last year (without that statement).

    I tried using the 'UI Editor' for 'Microsoft Office' and get the error: "Not a valid XML document Root element is missing", so I guess that this is not of xlsm files

    I can't afford a legal person for help with copyrighting this. Any help will be greatly appreciated.

    Thanks so much JimmyB ?(

    Hi. I have my workbook setup so that my macros can still work in Protected sheets. This is the code that works great, in my "This/workbook" that I'm using to accomplish this:

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

    ws.protect "Pamela491", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True

    Next ws

    What I need is to protect 4 rows in the worksheet "Meals Data" so that the macro cannot alter them. I tried to reverse the UserInterfaceOnly...code from True to False but not sure how to do this. Here is the code that I'm wrestling with. I'm sure there must be a better method all around it.

    Dim ac

    Dim ro10

    Dim ro10R

    Dim ro11

    Dim ro16

    Dim ro19

    Dim ws

    Call Column4_FirstBlank 'Sets active cell at first blank cell in col D (which is good start point)

    Set ws = ThisWorkbook.Worksheets("Meals Data")

    Set ac = ActiveCell

    Set ro10 = ac.Offset(10, 0)

    '''Set ro11 = ac.Offset(11, 0)

    '''Set ro16 = ac.Offset(16, 0)

    '''Set ro19 = ac.Offset(19, 0)

           '''Just testing with the msgboxes below

    MsgBox ro10.Address 'Show me D57...correct

    MsgBox ro10.Row 'Shows Row # 57...correct

    MsgBox ro10 'shows the data in the cell

    ro10R = ro10.Row

    MsgBox ro10R 'Shows row # 57

    For Each ro10R In ws

    '''Below I changed the trues to falses...but no good

    .protect "Pamela491", UserInterfaceOnly:=False, DrawingObjects:=False, Contents:=False, Scenarios:=False


    '''I step through and it brings me along but errors out at the .Protect... line

    I figured out the offsets for the row locations, of the 4 rows on the sheet that I want to protect...they're just waiting in Rem statements until this is figured out. Then will need to incorporate them in.

    Any help will be appreciated.

    Hitting the sack...zzzzzzzzzz...will check back tomorrow...Thanks a heap


    First time for everything...LOL

    No problem I'd backed up the file just before trying it. I'll keep trying with insignificant files to try to see what's going on when I get time

    Thanks for your help Roy


    Weird. The only thing checked off in options is "Clean the VBA project" & create backup. I can't delete any of the macros. I had a backup before I ran the cleaner, and reverted to that last night. I wonder what's going on.

    (Step by step)

    The cleaner exports the modules which copies but leaves originals in the program.

    Then imports the files back in.

    To me this would create the duplicates...Which is happening.

    I'm sure this logic shouldn't apply, but that is just what it's doing.

    Thanks JImmyB


    Recently I updated some of my macros with corrected versions, which I had in another workbook. I exported them out; then imported into the new workbook. I then removed the old macros and renamed the new ones by removing the suffix "1". Easy...everything worked out problem...piece of cake. Today I performed a "Clean Project" function to help improve my code. For some reason, it duplicated all of the files that I had imported yesterday and added the suffix "1" to the new duplicates :huh:. Also, now in right-click, I don't have the option to "Remove" ANY of my macros...even the ones that were not affected with this duplication. Like a dummy, (what did I have to lose LOL), I tried the "cleaner" again. Now I have another copy with the suffix "11"...So... 3 duplicate copies which I can't remove.

    I have a backup that I can revert to (if necessary), but I'd like to find out what I'm doing wrong :/. I understand that the "Cleaner" is a great tool to clean up my code and really want to use it.

    Very much appreciate some HELP Please


    Thanks Roy. I have had a few problems with dropbox. I think that they do have some issues. Once I copied a folder into the main, thinking that it would make the duplicate renamed (1) or (Copy). It named the folder the same as one right next to it and both froze up. I managed to get my files out of the folders but never managed to get rid of the empty folders. Their guy couldn't figure it out either. I've also had an issue with a file that became corrupted and thought that it was due to dropbox.

    I'll check out OneDrive if that is what you recommend

    I just retrieved an older version of my program and am updating it with the recent improvements (daunting task but I can do it). I'm a little nervous to run the cleaner. What does it actually do? Is there something in the background that I can't see that it's cleaning? Otherwise I feel that all of the code lines that I have are necessary.

    Thanks a ton