Posts by Maxi

  • Remove a shadow from a Form Control Button in Excel 2010

    I have opened an Excel 2003 workbook in Excel 2010. My workbook has many form control buttons with macros assigned to them. My problem is that Excel 2010 has applied some shadow effect to the left and bottom of many of these buttons (not all), and I can't get rid of it. The "shape effects" button on the Drawing Toolbar has no effect?? Ideas? Thanks in advance.

  • Stop Workbook Functioning After X Days

    Re: Stop Workbook Functioning After X Days


    AAE -- Just noticed something in the code you created for me -- take a look -- hasn't caused a problem so far but seems it could --


    see --
    "TrailDays" and "TrialDays" --throughout code-- small misspelling I assume. Shouldn't these all be the same?

  • Stop Workbook Functioning After X Days

    Re: Stop Workbook Functioning After X Days


    AAE & Dave --


    Been gone, but now I'm back.


    Thanks a whole bunch.


    I had to figure a few things out, but I got there, and greatly appreciate your assistance. (I ended up using AAE's code since it kept the workbook running through the last 30 days prior to expiration, which was critical.)


    My workbook, each worksheet and the VB code are all password protected, which created some problems. (As in, after the Datesheet was created upon first run of the file, it didn't even appear in the VBE Project window ----But, it was there somewhere, because the file still offered up the "< 30 day warning" and "< 1 day close". Once I removed the VBE password, resaved the file, reopened the file, bingo: there was the "DATESHEET" -- and after unprotecting the workbook, I was able to make the "Datesheet" visible, so I could delete it and re-set the "xdays" in the ThisWorkbook code.) So I'm now cookin' with gas.


    One added ingredient would complete my needs (I think):


    If the file were always "read only", and could only become "not read only" each time the user "enabled macros" upon opening, -- wouldn't this solve the problem of the user still having some functionality if he "disables macros" when opening the file after his expiration date? And if this would work, can you get me there? ...hmmm, just thought of something which may be a problem: As soon as he "enables macros" upon opening, (which, if possible, would include code to make "not read only"), once he then saves the file, it would remain "not read only", defeating this purpose. Right? Or is there a way to get it back to "read only" upon saving?


    If I ever sell more than five of these things (so far, it's just a labor of love), some comp will be headed your way. ...Your patience with my naivety and your time and assistance are appreciated.


    Let me know, and again, my sincere thanks --
    Maxi, as in...
    Burke T. Maxfield

  • Stop Workbook Functioning After X Days

    Re: Stop Workbook Functioning After X Days


    Hi AAE –


    Thank you for your generous input.


    But now I’m kind of in a pickle and very frustrated. The code does not repeatedly accomplish the objective.


    Once I insert your code, remove the quotation mark before the “.Visible = x1Very Hidden” instruction, save, close, and reopene the file again, I’m not able to go back into the code to re-set the expiration time frame for the program, ...because every time the program re-opens thereafter, the very hidden “DateSheet” already exists. (And I don’t know how to unhide the “x1VeryHidden” “DateSheet” which was created in your code, so the hidden worksheet exists somewhere, ...but, if I'm not mistaken, it must be eliminated to successfully reset a new expiration time frame and rerun the code on a new copy of the program.) I'm sure if you test it a bit, you'll see my problem.


    If a resolution to the problem could be better facilitated by an in-person call, please send me a private message. I’m happy to call (or give you my number for you to call me) and in talking on the phone, I’m sure the issue should be quickly resolved. Alternatively, I’ll look forward to any resolution you may offer back in this forum.


    My sincere thanks again,
    Maxi

  • Stop Workbook Functioning After X Days

    Re: Stop Workbook Functioning After X Days


    Dave/AAE --


    Hmmmm...


    Thanks for your efforts, but I'm still not cookin'.


    Even with the change (per AAE), the program continues to close as per my previous post, unless I "disable macros".


    Also, I still don't know what to put into the code (when I send it to a user) to make the program remain functional for varying periods of time before the 30 day notice begins to appear -- (say a 45 day trial, when after 15 days, the "expiration within 30 days" notice should begin to appear, after which, 30 days later, the program closes per the warning.) (--or a one year subscription, when after 11 months, the "expiration within 30 days" notice should begin to appear, after which, 30 days later, the program closes per the warning.)


    Maybe some remarks in the code would help me understand.


    Thanks in advance once more,
    Maxi

  • Stop Workbook Functioning After X Days

    Re: Stop Workbook Functioning After X Days


    Thanks Dave –


    Let me know if my request is unreasonable or “over the top”, so to speak, for this forum. If I’m out of bounds, or if I should just go to your “make an offer” forum, I’ll understand. Just let me know.


    Nonetheless, here are a couple of questions/problems regarding the code you’ve created for me:
    1) After pasting your “code” into the ThisWorkbook module, every time I reopen the file, and “enable macros”, the “expiration” message appears, I click “OK” and then the program closes. This should only happen at the end of the 30 day warning period – not during.
    2) So, what must be changed in the code to enable the program to run for 11 months, --or any period for that matter—(e.g. first 15 days of a 45 day trial, 23 months for a two year subscription, etc.), before the “30 day warning message” appears?
    3) May I increase the language in the MSGBOX – i.e. - make it as long as I want? Any restrictions or limits? I’d like to include “subscription or re-subscription instructions” etc.


    Again, thanks in advance; and let me know re my opening statement.


    Ciao-


    Maxi

  • Stop Workbook Functioning After X Days

    Re: Stop Workbook Functioning After X Days


    Hi Dave –


    Wow; I got the main guy! Hope you guys are doing well.


    Regarding my issue: My users pretty much have to "enable macros" to gain all functionality that makes my workbook of value to them, so they'll normally "enable macros." The workbook is currently a 20 worksheet/12 meg file which has numerous macros (which I simply created using the macro recorder – I’m no VBA guy) and figures out a myriad of golf bets which are played when multiple foursomes compete. But you are right. Without macros, they could still use many parts of the program.


    But maybe if part of the solution I’m seeking could also bring up a flash screen when they opened the program (and enabled macros) – lets say, within the last 30 days of their annual subscription – informing them that “it is time to renew their subscription/support/upgrade fee and that upon their expiration date, their existing program (and all copies) will cease to fully function,” ---they would be more inclined to re-subscribe (and pay the peanuts I charge) rather than risk not having the full-functioning program. I think for the level of sophistication of those few users I have, this would suffice for now.


    …Excuse the wordiness; but hopefully this will help you understand what I’m looking to accomplish, so any code --and where to put it -- would be greatly appreciated. And again, my thanks in advance for any assistance you are able to provide.


    With immense respect – (since I’m a hack -- at both golf and Excel),


    Maxi

  • Stop Workbook Functioning After X Days

    Hello Excel Smartees --


    I need to know how to make a workbook (and any/all copies made there from) cease to function (become irreversibly read-only) after a specific period of time following the first time it is loaded by a user (this would be preferable), or after a specified date (this could also work). My thanks in advance.


    Maxi

  • Remove Brackets From Names & Reverse Names With No Comma

    Re: Remove Brackets From Names &amp; Reverse Names With No Comma


    It's a second "Bingo"!


    Great shooting Kris and thanks so much. (Although now I'll have to spend the next four hours figuring out how the formula works, but sure enough, it works.)


    In the mean time, hold on Amirampeled, your solution works great too. And yours I could figure out -- reasonably well. So degrade yourself no longer, good friend, I was wrestling in the dark until you appeared. -- You're light years ahead of me.


    My thanks to both of you.


    Maxi

  • Remove Brackets From Names & Reverse Names With No Comma

    I've unsuccessfully searched, so now I must ask...


    Using a formula, how do I pull the exact name of a person from the middle of a text cell, since the length of each person's name varies in the original text cells, and the name within each text cell is not always preceded by (Ghost) but is always succeeded by “ (number)”. e.g., resolve to "Jarman, John" from text cell containing "(Ghost) Jarman, John (21)", or resolve to "Joe Smith" from "Joe Smith (2)"?


    Additional Example: Column A needs to be converted to Column B:


    Column A
    (Ghost) Jarman, John (21)
    Smith, Joe (2)
    Johnson, Harvey (30)
    (Ghost) Bob Jolly (-1)


    Column B
    Jarman, John
    Smith, Joe
    Johnson, Harvey
    Jolly, Bob


    My thanks in advance...


    Maxi

  • Handicap Balanced Score Card For Golf Group.

    Re: Create handicap &quot;balanced&quot; foursomes for golf group.


    Fen --
    Wait...wait...
    There's a glimmer shining through.
    I didn't realize the "data sheet" had to be rank ordered from low to high handicapper. (The guys I plugged in were in alphabetical order.) Now I'm understanding a few things. BUT, still not as many as ultimately necessary. I'll continue to wade, but if you want to turn on the halogen, that would be great.
    Maxi

  • Handicap Balanced Score Card For Golf Group.

    Re: Create handicap &quot;balanced&quot; foursomes for golf group.


    Wow. Frustrated. This is heavy Greek to me. Can't tell how you get from a to z, so I'm hard pressed to know what to do next, and I could ask questions until the cows come home. Don't want to screw with what you've done, cause I can see it's close. But I continue to get teams which do not have a player from each "tier" as desired, and as explained in my original post. A brief explanation of what's cooking in each column would be vonderbar. Maybe adding some comments in cells could help lead me out of the dark so I can better understand the "steps."


    I've plugged in some players and true example handicaps, which range from -1.5 to 29. (Yes, when a player is better than a "0", his handicap continues downward! Tiger would be about -7. Does this effect any of your formulas?)


    Also, we can even have as many as 75 players. But because your formulas in the columns do not stay the same, I dare not/don't know how to expand in any direction. Sorry I'm such a dumb dumb, and I'm envious of your Excel Phd. My continued thanks in advance.

  • Handicap Balanced Score Card For Golf Group.

    Re: Create handicap &quot;balanced&quot; foursomes for golf group.


    WOW! Lostsville! But wow again.


    Major effort! Thanks a gazillion Fengore.


    I'm trying to figure it out (lots o' functions/references I've never used... or seen). Any step by step explanation would be GREAT. So far I'm slowly slogging.


    But also, I don't see "handicap balanced" teams, as I described.


    Tell me what you can tell me.


    Thanks once again --
    Maxi

  • Handicap Balanced Score Card For Golf Group.

    Golfers have a numeric handicap based upon their proven ability (reported scores); the lower the handicap, the better the golfer.


    My golf group is randomly drawn into handicap-balanced foursomes. If 24 guys are playing, six teams are created as follows:
    1) The six players with the lowest golf handicaps are "Captains," and are randomly drawn.
    2) After the Captains are drawn, the six players with the next lowest handicaps are then randomly drawn as the #2 players for each team.
    3) The same process is followed to randomly determine the #3 player and #4 player for each team.


    If the number of players is not divisible by four, some threesomes are then created, and one, two or three players are then on two teams. e.g., If we have 29 players, we draw 5 foursomes (20 players) and 3 threesomes (9 players). In this event, each threesome will use the scores of a randomly drawn player (Ghost) from one of the five foursomes. (Each Threesome's "Ghost" player should be drawn from the group [captains, #2's, #3's, #4's] which will best round-out and handicap-balance the team.)


    Can teams be quickly and randomly drawn by Excel from a list of players and respective handicaps as attached?

  • Can Vlookup return multiple values?

    Re: Can Vlookup return multiple values?


    Hi Dom --
    First of all, I'm slow. I didn't know you'd responded to my private message; I assumed I'd received an e-mail notice once you responded, but I didn't. And OK, I'll try to keep it on the board.


    Attached is our back and forth winnings2.xls file with a few more clarifications you'll understand I hope. But first read what I've written below.



    All who tied for 1st split 1st, 2nd and 3rd prize: $12,000 + $6000 + $3000 = $21000 / 6 = $3500 each. And those who rank 2nd and 3rd in Net win nothing (because they really place 7th and 8th).


    OK Next: Pursuant to your question to explain precedence for Highest Finish (the terminology should say Highest $ Finish) here goes --


    1) Highest available prize is first precedent. (if 1st Gross pays $10,000 and 1st Net pays $12,000, and one person places 1st in both categories, he would win 1st Place Net and receive 1st Place Net prize money. If the next highest prize is 1st Gross, then next lowest Gross score wins 1st Gross prize.)


    2) If places (1st, 2nd, 3rd etc) pay the same in both gross and net, then Gross finish takes precedence over Net finish.


    (e.g. in our Winnings2.xls worksheet, if Anderson HAD NOT been tied by Christopher in Gross, Anderson would have placed 1st in BOTH Gross and Net divisions, yet would have won 1st Place Gross and been paid 1st Place money in Gross and nothing in Net.)


    3) Next, if 1st 2nd 3rd etc in Gross and Net pay the same amount, once 1st Place Gross is determined, 1st Place Net is then the next highest available prize, so the lowest net score is next determined and awarded this prize. If no ties exist, 1st Net is awarded, and then the next precedent would be back to determine 2nd Gross. If ties exist, however: a) if none of those who tie would place in Gross, how ever many tie split total amount due that many places (4 tie = equally split 1st, 2nd, 3rd and 4th place dough) b) if one of those who tie for 1st Net would win Gross 2nd alone which would pay more than 1/4 of sum of 1st thru 4th money in net, that person receives Gross 2nd (his highest finish available) and the other 3 then split the sum of 1st, 2nd and 3rd Net.


    So, we have now awarded Gross: 1st, 2nd; and in Net: 1st, 2nd, 3rd (who all tied). Next highest available prize is 3rd Gross, so the next lowest Gross score wins 3rd place. But again if there are ties for 3rd Place Gross, and if one or more of those who tie would place in the Net division and win more than the winnings they'd receive by splitting 3rd, 4th, 5th whatever places etc in the gross division, they then go back to the Net payout.


    Another factor which can serve to further confuse the Highest $ Finish determination is the fact that most tournaments pay more Net places than Gross places, since the actual Gross “field,” in reality, is much smaller than the Net “field.” Although all players are considered “entered” in both the Gross and Net divisions, only the best players in the field truly have a chance in the Gross division. (e.g. A 24 handicapper is never going to shoot a score which will earn him a “place” in the gross (handicap doesn’t count) division. See what I mean?)


    Clear as mud? Whistle if I've not made myself clear. Hopefully the attached file will again help.


    And once again, thank you thank you thank you in advance.


    Maxi


    PS Still studying my array formulas. They make me feel like a real dullard.