Posts by JimmyB

Hey Justin...you're a genius. As soon as I saw your recommendation I figured, that had to be it.

Thanks a ton. You're a lifesaver.

Have a great day and

Keep a good distance

Jimmy B

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

Sub Shapes4()
'Deleting only form controls

    Dim shp As Shape
    Dim testStr As String
    Dim sh As Worksheet
    
        For Each sh In Application.ThisWorkbook.Worksheets
            For Each shp In ActiveSheet.Shapes
                If shp.Type = 8 Then
                    If shp.FormControlType = 2 Then
                        testStr = ""
                        On Error Resume Next
                        testStr = shp.TopLeftCell.Address
                        On Error GoTo 0
                        If testStr <> "" Then shp.Delete
                    Else
                        shp.Delete
                    End If
                End If
            Next shp
        Next sh
End Sub



Sub UnprotectAllSheets()

Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
        sh.Unprotect "Pamela491"
    Next sh

End Sub




'----------------Save selected sheets as a new workbook--------------------
    Sheets(Array("Disclaimer", "Sign In", "Glucose Data", "INR Data", _
        "Exercise Data", "Glucose Chart", "INR Chart", "Exercise Chart", "LOG ENTRIES", _
        "Meals Data", "Weight Data", "BP Data", "Meals Charts", "Weight Chart", "BP Chart", _
        "Default Sheet", "Medication History", "Fahrenheit Chart", "Fahrenheit Data", _
        "Celsius Data", "Celsius Chart")).Copy
       
    With ActiveWorkbook
        .SaveAs AtchFile, 51
    
        Call UnprotectAllSheets
        Call Shapes4
    
        .Sheets("Default Sheet").Range("B38,B44").ClearContents
        .Sheets("LOG ENTRIES").Select
        .Save
        .Close
        
    End With
 '----------end of save selected sheets as new workbook---------------

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.

LOL...thanks

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

If I add more characters etc, would you say it make it harder to crack, or is it just a process that would take them longer?

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

Alright. I get the picture Roy. I'll quit wasting my time on this. Thanks so much for your help.

JimmyB

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

Hi

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

Sorry Roy I misunderstood about the tags. I thought that I did use the tags. I used the inline-code tags from the end icon on the taskbar. I thought that I was supposed to use that, if I wanted to add more conversation (after and/or) between where I displayed my code.

Thanks Roy. Good advice

Being self taught has its' demons

I'm often taking the hard path...LOL

Have a great day

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

Next

'''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

JimmyB

...LOL

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

JimmyB

Must leave. Checking back in tonight

Thanks for any help

JimmyB

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

Hi

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 fine...no 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 . 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

JimmyB

Thanks...gotta go for the day

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

JimmyB

Found backed up file from FEb 6. Updating with improvements to bring back to current level (without the GLITCH!...LOL).

zzz bedtime...will finish tomorrow

JimmyB