Posts by JimmyB

    Hey Jack. That sounds like a great practice. When I get this back on track, I'll add a control button to backup to a couple of files with date & time, like you suggested. I have emailed to myself periodically, as I've progressed through this adventure...(mostly to prove that the program had been my creation, for legal purposes if necessary)...but I will try to go back to where it worked and rebuild from there I guess.

    I gave some thought to export the modules for safekeeping, but wouldn't that be a bit redundant? When I save the file, the modules just follow right along.

    Besides I have over 80 modules in this program.

    Anyway thanks for the suggestions. I do really appreciate it

    BTW as far as switching from 2007, 10. 13? LOL I'm lucky to have a free programmers version of 2007 HAHA...that a programmer friend acquired from a convention ...Can't afford all of those toys. ;)


    Hi Roy

    I'm using dropbox and can search through the history, to hopefully find where it was still good. I usually keep good log entries of changes but somehow missed when exactly this happened. I've been making improvements all along and will lose those but that's ok. My largest worry is that when I finally do get this back to normal, how will I protect, so that it doesn't reoccur. My intent is to market this program for sale and have been working hard to make sure that it is working without a hitch. This happened right out of the blue and don't know what caused it. Should I suspect that 'Dropbox' may have caused the glitch? Have you heard of this causing problems? I believe they are out of the UK if I'm not mistaken. I plan on using it for sales (by selling links to the program with unique passwords to each in the 'Public Folders') This is already set I'm scared to do this. Your thoughts? I'm a little apprehensive about sharing the complete program code as I feel you had asked me for. It has taken me a lot of time and hunting to develop it and (even though you've helped me extensively through the last year...I don't really know you or anyone else for that matter). I feel a bit stuck between a rock and...

    Not time to work on it now. Will return to the computer later tonight if you return with your thoughts...Very much appreciated from you

    Thanks JimmyB

    Hey Jack

    I created it with 2007 and opened with same (2007) and get the issue. Now according to my previous post that said it's opening ok...suddenly it's not. Today as I went to save the file, it gave me my signin as though I was opening the workbook. Problems are erratic with no reasonable structure. What a tragedy. It's probably something stupid that I'm just not seeing

    Thanks for your help



    Originally I had an error:

    Excel found unreadable content in 'filename'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes

    When I clicked 'Yes' the file opened fine but I needed this error to not pop up.

    I searched the net and found how to repair this error by: <start blank workbook><Open><highlight problem file><Open & Repair (drop down at bottom of window)>Repair>. This fixed the error message from popping up, but created a new problem with my worksheet.

    I get new error (that I never got before) when my sub tries to shift rows up after deleting data. (It worked perfectly for the last 600 entries...before I repaired)

    Error: "The operation is not allowed. The operation is attempting to shift cells in a Table on your worksheet."

    There is no table in the 600 rows where these rows exist. It's as though I'm trying to affect a range of cells, that contain a segment of a table mixed in. I've scoured the whole sheet and couldn't find any evidence of a table. I fly-specked the code and it is identical to what it has been since startup....nothing changed

    Also for some reason my forward and back arrows in the toolbar are not workable. (Sheet is not protected).

    I have a feeling that this worksheet may be pooched. Is there any way I can diagnose what is wrong?

    Thanks a ton

    JimmyB ?(

    Ok I want to make sure that I respond to all of you people that helped me with my issue.

    Hi change. I also tried .MealItem.paste with your 'with' statement. Thanks.

    Hi Jolivanes. Re: post #7. What I need to do is just copy the range A55:L55 (MealItem) from 'Default Sheeet' to the bottom of a list in 'VALUE for MEALS' sheet. The sub then sorts it alphabetically to Column 'A'. Following this the range 'MealItem' is transferred to 9 more sheets which are then sorted to various columns, depending on certain criteria. This worked for me to insert over 150 rows of items. It's faulting out on the line 'ActiveSheet.Paste'. Not sure what else you want here.

    Re Post #8. OOPS my fault...typo...I an't believe that I missed that. No, there is only one sheet 'VALUES for MEALS'. I changed the errors, and there is no difference...but it did work up until recently.

    Re Post #4. The only difference I could see with what I was using, is you are finding the from the bottom and I using from the top. Would it be better practice to use your method when possible...going forward? (still learning)

    Re Post #9. BINGO! got it back. Works great

    Hi Dave. I was going to try your suggestion next. It looks like that would have worked fine. Thanks for that.

    Ok, I just want to thank everyone for all of the input. This section of the program is extremely important an can't go without it. If you want to see the program run just click my YouTube channel. I have videos of me running the program. Videos need updating, as I've made a ton of improvements (like screen updating for one) since. Thanks Again

    Hi Dave

    Just so you know, this code works well at about 50 other locations in my program.

    Sub Column1_FirstBlank()
        Dim ws As Worksheet
        Set ws = ActiveSheet
        For Each cell In ws.Columns(1).Cells
            If IsEmpty(cell) = True Then cell.Select: Exit For
        Next cell
    End Sub

    Good day

    I'm having problem with pasting a range of cells, from one worksheet to another. It has worked 100 times before, but since stopped working. I get the error: "Paste method of worksheet class failed". I've been upgrading many parts of the program, which I didn't think would be of concern. It fails on all 9 sheets that it's supposed to update, so I don't think that it's caused by the destination. I stepped through it and watched the 'Clipboard' and it is copying the range to it, so the copy seems fine. I have no idea at which point it failed, as I hadn't used this sub for a while. If you can just give me some advice on what to check next, I'd appreciate it greatly.

    Here's part of the sub that the code is failing to paste (ActiveSheet.Paste). Ignore the rem statements (comments)...trying different things:

    Thanks JimmyB

    No doubt Carim. I did look through his site and it looked like a world of information there, that I can benefit from. Thanks so much for that! I saved the link for future help. I did what was advised at the onset of his post, set a reference to the VBA Extensibility library and of course my trust center is good, but nothing in his post helped with what I wanted. If you look at the post that I just put up (#10) you'll see my issue more in depth. I was going to kind of let my issue go by the wayside. I even set the thread as resolved, but RoyUK advised that he wants to help out. So here I am again.:S LOL

    Speaking about time and energy...I've been working through this program every day for the last year||, using my 'Basic' Programming training from the 1980's (HaHa...yeah I'm really old :whistling:), but i love the challenge), and learning from scratch LOL. With most of the help from Google. When that doesn't pull through, you (especially) and a few others from Ozgrid, have saved me:thumbup:. Now It's almost complete.

    If you check my YouTube channel, you'll see me actually running the program. I did this early on in the quest and have since made a ton of improvements (including 'screen updating') but you'll get the picture. check out my links and feel free to subscribe etc. Ooops sorry didn't mean to plug it but couldn't help myself.

    Thanks so much. You've been a GREAT help

    Have a great day;)


    Hi Roy. Thanks for the offer. Here's my issue, explained as good as I can. (too much info is better than not enough...I say...LOL).

    <Call InputBox2> is activated by ThisWorkbook event on startup, which has the sole purpose of prompting me to assign a unique password to the program and an “Alpha code word” to correlate with the password, so I can respond in case the user forgets it.

    Sub Inputbox2()

    Dim inbx As String

    Dim inbx2 As String

    Dim vbCom As Object

    Dim wb As Workbook

    Dim ans As String

    Dim SaveChanges As Boolean

    Dim FileName As String

    inbx = InputBox("Program the New Password" & vbNewLine & vbNewLine & "DEVELOPER SECTION", _

    "Assign Password BOX", "Type Here", 2000, 6000)

    'This is for the programmer to insert the password for the program

    inbx2 = InputBox("PROGRAM the ALPHA" & vbNewLine & "DEVELOPER SECTION", "ALPHA Box", _

    "Type Here", 5000, 6000)

    'This is for the programmer to insert the password for the program

    Sheets("Default Sheet").Activate

    Range("B38").Value = inbx

    'This sets the paasword from the developer into the program

    Sheets("Sign In").Activate

    With Range("A2")

    .Value = inbx2

    .HorizontalAlignment = xlCenter

    .Font.Bold = True

    End With

    With Range("A1")

    .Value = "ALPHA"

    .Font.Bold = True

    End With

    MsgBox "Hi, I will delete myself "

    ans = MsgBox("DONE" & vbNewLine & vbNewLine & "Password & Alpha set to" _

    & vbNewLine & inbx & inbx2 & vbNewLine & vbNewLine & " YES = end and shutdown" _

    & vbNewLine & " NO = Continue to app", vbYesNo)

    If ans = vbYes Then

    FileName = "BondyMT_Alpha_" & Range("A2").Value


    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName

    Call DeleteCodeLines




    Exit Sub

    End If

    End Sub

    It assigns the password – Saves as new name – Deletes code line <Call InputBox2> from ThisWorkbook – re-saves the program (Therefore <Call InputBox 2> is gone from the WorkBook event) – Closes the program.

    Option Explicit

    Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    With Sheets("Home Sheet").ComboBox1

    .AddItem "Appoint. (change)"

    .AddItem "Appoint. (needed)"

    .AddItem "Bleeding"

    .AddItem "Dizzy Spells"

    .AddItem "Flu / Cold"

    .AddItem "Getting Better"

    .AddItem "Getting Worse"

    .AddItem "Headache"

    .AddItem "Pain"

    .AddItem "Heart Rate"

    .AddItem "Pain(Chest)"

    .AddItem "Prescription"

    .AddItem "Sprain"

    .AddItem "Throat Sore"

    .AddItem "Vomitting"

    .AddItem "Other"

    End With

    With Sheets("Home Sheet").ComboBox2

    .AddItem "Before B-fast"

    .AddItem "After B-fast"

    .AddItem "Before Lunch"

    .AddItem "After Lunch"

    .AddItem "Before Supper"

    .AddItem "After Supper"

    .AddItem "Bedtime"

    End With

    With Sheets("Default Sheet").ComboBox3

    .AddItem "Choose Here"

    .AddItem "gmail"

    '.AddItem "yahoo"

    '.AddItem "outlook"

    End With

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

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

    Next ws

    'This allows the macros to run on protected sheets

    'This next block is for developer use when adjusting sheets (to remove password)

    'For Each ws In ThisWorkbook.Worksheets

    'ws.Unprotect "Pamela491"

    'Next ws

    Application.ScreenUpdating = True

    Sheets("Sign In").Select

    'This ensures we start on the header sheet

    'Insert Call Inputbox2 below this line

    Call Inputbox2

    Call Inputbox1

    End Sub

    The renamed altered program is now sitting in my location for the user. (Thus InputBox2 is gone) so <Call InputBox1> is activated for the user to open exactly as planned, (disabling the sheets until the user confirms that the disclaimer has been read,etc...) and must use the newly assigned password.

    Option Explicit

    Sub Inputbox1()

    Dim rng1 As String

    Dim A1 As String

    'Dim rng2 As String

    Dim A2 As String

    Dim blnSignInAsGuest As Boolean

    Dim Answ

    Dim xWs As Worksheet

    Do Until Len(A1) > 0 And Len(rng1) > 0 And A1 = rng1

    'Sets inputBox (A1) to test if it matches (rng1) from Default Sheet

    'Also checks input & Password from 'Default sheet' it present

    A1 = 0

    rng1 = Sheets("Default Sheet").Range("B38").Value

    A1 = InputBox("Type in Password" & vbNewLine & "OWNER SECTION" & vbNewLine & vbNewLine & vbNewLine _

    & "For Guest with limited access" & vbNewLine & "Just hit ENTER", "PASSWORD SIGN IN BOX", "Type Here", 5300, 3500)

    'Sets values for A1 & rng1

    If Len(A1) = 0 Then

    GoTo Jump1

    End If

    'If the password A1 is left blank or hit cancel or 'X out' (go to retry or guest limited access)

    If A1 = rng1 Then

    MsgBox "Welcome you're in With Full Access" _

    & vbNewLine & vbNewLine & "AFTER YOU'VE READ THE DISCLAIMER" & vbNewLine & "Feel free to change your passcode" & _

    vbNewLine & ">>>>> Just Click OK <<<<<" & vbNewLine & vbNewLine & _

    "____THEN_____" & vbNewLine & vbNewLine & "Tap the Default Sheet tab" & vbNewLine & _

    "at the bottom of this page"

    'Tests if password from InputBox = rng1 and ('Cancel' and 'X' out) has not been hit

    Call HideWorksheets



    MsgBox "PLEASE ENSURE THAT YOU READ THE TERMS" & vbNewLine & vbNewLine & _

    "Click (OK)" & vbNewLine & "Read the Terms" & vbNewLine & _

    "Click the button at the bottom" & vbNewLine & "YAHOO! START YOUR DAY"

    Exit Sub

    End If

    'Exits Sub releasing to full access of program


    If A1 <> rng1 Then

    Answ = MsgBox("Sorry Password does not match" & vbNewLine & _

    "Please hit YES to Re-try" & vbNewLine & "Or hit NO to enter as Guest", vbYesNo)

    End If

    'If password is false then user has option to retry or go on to limited guest access

    blnSignInAsGuest = False

    'Sets boolean YesNo to 'NO' value for MsgBox choice at label Jump1: to retry or go on to limited access

    If Answ = vbYes Then

    GoTo Jump3

    End If

    'This assigns the 'YES" (decision to retry password) by going to label - Jump 3:

    'thus looping back to start to begin the process or trying to sign in

    If Answ = vbNo Then

    blnSignInAsGuest = True

    GoTo Jump2

    End If

    'This assigns the 'NO' (decision to retry password) to actually sign in as guest at label - Junp2:

    'reaching past the Loop command



    'Looping has completed. Either the password has been accepted or

    'the user has decided to just sign in as Guest with limited access


    If blnSignInAsGuest = True Then

    A2 = InputBox("Guest/Physician Entry with limited access", _

    "GUEST BOX", "JUST HIT ENTER", 500, 5000)

    End If

    'Now the user has conceded to sign in as Guest as inputbox (A2) has stated

    'If Len(A2) = "" Or StrPtr(A2) > 0 Or A2 = 0 Then

    'This line tests that the user can input anything or nothing & hit 'Enter'

    Call DeleteSheets

    'Call DeleteButtons

    'This calls the sub routine that deletes the restricted view sheets

    MsgBox "Welcome! You're in" & vbNewLine & "Limited Access" _

    & vbNewLine & vbNewLine & ">>>>> Just Click OK <<<<<" & vbNewLine & _

    "_______THEN" & vbNewLine & "Tap any of the tabs at the bottom of the sheet", 300, 3000

    'This advises that the user in in the limited access area for viewing only

    'End If



    MsgBox "Please click OK" & vbNewLine & "Then take a few minutes" & vbNewLine & _

    "and read the Disclaimer" & vbNewLine & vbNewLine & "Enjoy your" & vbNewLine & _

    "Bondy Medical Tracker"

    End Sub

    Works Great, but I want the macros to be protected. If I protect the project by: <Tools><Project Properties><Protection><Lock Properties for viewing><Password>...It won’t allow me to remove “Call InputBox2”

    As you suggested there must be a work-around that I can do. I’m having trouble with this one. Any help will be appreciated, as this is one of the very last items to be completed, on this grueling quest...LOL... that I’ve set out on.

    Sorry for the length. I figure if you want you can just ignore what you don't need.

    Thanks JimmyB

    Hi Roy. Yeah I overlooked the possibility of just working around the line of code, especially since deleting it is not feasible, due to the project protection. I just thought it would be cleaner if I got rid of it, as it's only used once before sending the program to the recipient (who has no need for it). Sometimes I can't see the forest...all of the darn trees are in the way...LOL

    Thanks for your help

    Have a great day


    Well I thought that it would be a no brainer, but the more I google it and search all of the forums, that is not the case. I made sure the items that you recommended for me to check out was no avail. I know that the password protection is easily hacked by a coding-savvy programmer. I just wanted to protect the file from people accidentally going in and messing things up. I'll just remove the developer tab from the toolbar. If someone goes that far to screw with it, there's no stopping them anyway...LOL

    Thanks for your help Carim


    I have password protected my macros from viewing, by: <Developer><Visual Basics><Tools><VBAProject Properties><Protection><Lock project for viewing><password>

    After the program runs, I need to delete a line of code in the Workbook Event Sheet. Everything works great on their own, but when together, I get the 'protected error.

    I need the protection removed

    Then execute the code

    Then protection reinstated.

    Thanks for your time and help


    Hi Carim

    I played around a bit and got it to work. Your code helped loads. Really appreciate it

    I can finally put this one to rest

    Don't forget to check out my You Tube channel. I have made improvements since the videos (like ScreenUpdating) etc but you'll get the idea. Just send me a comment on any of my sites. (You Tube, Blog, Website, GoFundMe... I check them daily.

    Thanks again Carim

    Jimmy ;):!: