Posts by JimmyB

    ok Carim. I'm quite sure you've given me exactly what I need...I just need to incorporate it in. I'm sure that I'll find out what is wrong on my end. The syntax is right and that is where I often struggle. It works on your end and that's what I need to hear. So Thanks a ton. It is really very much appreciated.

    You know, you've helped me a lot on this program over the past months. I'm almost finished with the program. If you'd like to see what you, (and others at OzGrid), have been helping me develop, just click on the link of my You Tube channel in my profile. There is a series of videos, showing myself actually running the program as an instructional feature.

    There's also my website etc...

    Thanks again


    Silly ME :S

    If you place this code in the workbook event it will allow macros to run in password protected sheets. If that doesn't work the password is right there in the code

    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

    Still not running. I got error: Object required on Line 14...but nothing else happened.

    You mentioned that if you had the workbook, you could probably help better, so I pasted the extra 8 sheets into the file that you previously sent me. Hope that this helps solve the issue.

    To help eliminate confusion, I adjusted all of the sheet #'s to match my workbook.

    Thanks Jimmy :)

    Test DeleteRow JimmyB .xlsm

    Hi Carim

    This is pretty well what you had sent me yesterday, except you adjusted the sheet designations on line 7, but that isn't my problem. I've adjusted for that.

    My issue is with line 11. Shouldn't we be referring to the main sheet 'Sheet12' which is the actual target? Also shouldn't it be outside of the loop, so it only deletes once? instead of Sheets(i) which mimics the deletions orchestrated in line #10

    The code that you sent me in thread #11 had structure that worked, but we're not applying the same here. If you go back to that thread, and compare the event code in that file to what we have now, you'll see what I mean.

    in any event I'm getting error "object required" on line 11 either way

    Thanks for your patience... Jimmy

    Sorry Carim

    I didn't see your response before I sent the last thread.

    I had to change line 11 from the variable "i" to the actual sheet name. Shouldn't it be Sheet1 as this is the sheet that is what we are comparing from

    When I run the code I get an error: Object required on line 9

    BTW I don't think that I did send you all of the subs by accident. I must have had my program up & it picked them up from that.

    Also, if you are helping me on the request from the last thread, can you also insert something. so that if an item match is not found on any of the sheets, it doesn't give an error? It just continues to the next sheet

    Hi Carim

    I mentioned, (in error), on my last thread, that I couldn't use the double click method that you suggested, but I was wrong. I have my original event setup, so it would not activate unless I activated it, using the (meal item selector) form control. Therefore your easy method works perfectly. I just added it below the original event and tweaked the sheet #'s to match mine. WORKS GREAT! and I love easy for the user.

    The only issue I now have is that it must preform the same calculations on the next 8 sheets. So I need it to search and delete accordingly on sheets 13 through 21 (Inclusive). (VALUES for MEALS being Sheet 12). I was going to copy the calculation and paste another 8 times and adjust. I'm sure there's a better way to incorporate it into your code. Can you help me with that?

    Thanks Jimmy

    Yeah Carim. All of the sheets are identical and driven by the Values for Meals sheet. I must have deleted a few while trying different things. I'll double check them before i finalize.

    The easy solution cannot work. The main sheet is what populates the Meals Data sheet. When the user picks a food item, it automatically goes to a daily consumption list, on a different sheet. It then calculates the total amount of calories, transfat, sodium etc., transfers the data to a table which in turn creates charts. Worked hard to get that going and don't want to change it. That is why I want to use the row numbers.

    I didn't mean to send you all of the code, for the complete rudimentary, (self taught), as it is, It's a secret...LOL. Planning to go public in the spring. I struggled all year to develop it...Hope you're as honest as you seem. LOL. I'd hate to put it out there and get sued because someone else claimed it.

    Sooooo back to the drawing board? Appreciate it

    HEY Dave...Thanks...I didn't know about the 'Copy Icon' very very helpful info.haha

    Actually I re-copied your sub again using the 'Copy Icon' and must have missed something when I originally did it manually. The copied file in in my folder . Only problem now is that I'm getting a transport error when it tries to send, because the email password is deleted prematurely in the original file. But we're much closer...almost got it

    I moved the deleted line to after the saveas and it sent but not with deleted content. Tonight I'll just ad a resave line of code after the cell deletion so it saves before it sends.

    Hi Carim

    Honestly, I tried what you suggested and due to my experience, (or lack there of), I've screwed it up, to beyond recognition...LOL. I feel that this should be soooo easy, but I can't seem to accomplish this task. This sub is one of the last of my problems, that I've muddled through over the last year, to build this program. I can finally see daylight! Another being a problem with my CDO email that 'Uncle Stringer" & 'DaveExcel' is helping me with. (I believe that you were my mentor & provided that code.)

    I've attached a copy, which has just the 2 sheets that are the focus of my problem.JimmyB Trial.xlsm

    What I'm trying to do is this:

    I have a master sheet -"VALUES for MEALS" where row of meal item & categories go.

    The user is allowed to add meal rows into the master sheet

    It is then automatically sorted into the category sheets, ie: Calories, Total fat, etc..

    That's cool..I got that down very well.

    Need to make it possible to remove unwanted items. which I've uploaded into it,

    by placing the line # in the box provided.

    I will create a 'form control' button to activate the sub.

    The sub needs to identify the line number IE: 220

    then go to the line #... then check column "A" and store the text IE: "Test"

    Then delete the row IE: 220 where the text "Test" is located

    Then go to the "Calories" sheet

    Search column "A" for the text "Test"

    and delete that row

    That's it. Simple right?

    If you can help me with the syntax I can copy and adjust it to all of the other categories

    IE: Cholesterol, Sodium, etc...

    Thanks Again Carim...very much appreciate it

    Oh yeah,

    The sub that I worked on is to pull up the most recent that I sent...Sorry

    Yeah, I get it. I don't know how you guys do any of the help, with the limited information you're given. (especially from me, ?( I guess).

    As you can tell my nemesis it assigning types to my variables. That's why I let the programs assign as variants. I've read that it's not a good way to go, but should work anyway. I've tried to learn the types, but it seems like most of the time the assignment faults out, somewhere down the line. I just can't seem to grasp the logic in it.

    I don't know if this will help but here is more information on my variables...

    I very much appreciate any help that you're giving me.

    Rng finds the value in sheet VALUES for MEALS in a specific cell that houses the line # to delete

    SheetNum is the variable where Rng is stored

    RC finds the cell location A & SheetNum where text is stored

    RCa is the variable where RC is stored

    Sitem = RCa

    rg sets the range to search text and match for similarity to RCa

    Sres is assigned to the .Find(Sitem) command

    Hi Carim. I found an error in the code above the line I was having problem with, so I corrected the code accordingly, but I'm still getting the error message: "Type Mismatch" on the same line of code. Here s the amended code:

    Thanks a ton