Posts by Excel_Phoenix


    I'm looking for ideas/suggestions as to how to best organise the game time for each of the kids in a team taking place in a football tournament. The structure is as follows:
    * Ten players per team
    * Eight players on the fields at one time
    * Each game lasts for 10 minutes
    * Each squad will play 5 games

    The objective is for all kids to get the same amount of playing time and to play in each of the games.

    Any easy way to schedule this?

    Thanks :)

    How about looping through the UsedRange? (I can't see how you can test the whole of the UsedRange at once - it could contain many different colours)

    Sub NoYellow()
        Dim rngCell As Range
        For Each rngCell In ActiveSheet.UsedRange
            If rngCell.Interior.Color = Excel.XlRgbColor.rgbYellow Then rngCell.Interior.Color = xlNone
        Next rngCell
    End Sub


    The clue is in the title of yoru post :)

    You could also just end the count by adding x=lRow instead of Exit For.


    How about something like this:

    Note that deleting rows can be tricky - you might want to look at looping backwards (ie from the bottom to the top).

    In addition, for some minor improvements have a look at the variable declarations. Using Dim moxi, Tty, dnum, cnum, Esum, Dly, Dlyn, h, jd, m, j, k, pk, i As Integer does not declare all of the those variables as Integers - only i is an Integer, the rest all default Variants. You need to do each one individually.

    Solved (quickly touches wood!)

    My custom menu was added with Workbook_AddinInstall and removed with Workbook_AddinUninstall. I've now extended this to also include Workbook_Open and Workbook_BeforeClose. I can't see why this is necessary but, for now at least, it seems to be working :)

    No luck I'm afraid :(

    So, just to re-cap, adding the add-in's folder location to the Trusted Location list has not resolved matters nor has changing the add-in properties to remove the block on downloaded files (the option wasn't actually available so doesn't look to be applicable on a network). So, I have a loaded add-in but a missing custom menu - if I go to the VBE and run the routine from there it works as intended.

    Anyone have any further thoughts?


    You can't store a value within the Userform itself. You will need to save it somewhere else such as a cell on a worksheet within the workbook or possibly a text file - then use the stored value when you re-load the Userform.

    What code have you got so far?


    Would it not be easier to use workbook variables rather than worrying about activating/selecting?

    Dim wbkEV as Workbook
    Set wbkEV = Workbooks.Open Filename:=MyFolder & "" & MyFile, UpdateLinks:=False, WriteResPassword:="ben", ReadOnly:=False, IgnoreReadOnlyRecommended:=True
    'Do stuff with wbkEV

    Hi Tom,

    Thanks for your reply.

    None of the users has the add-in stored locally (or at least they shouldn't have as I specifically asked them not to during installation!). This is by design so that any amendments to the add-in just have to be made to the network copy. Not having a local copy shouldn't make any difference should it? (I'm not dismissing your suggestion - I'm grateful that somebody has replied! - just trying to think why that would have the indicated effect.)

    Hi all,

    I've created an add-in that is deployed on a network (I keep a local copy for making amendments before uploading to the network). The add-in works fine for most users but for some it disappears (remains in the add-in menu but is unticked and the custom menu disappears) once Excel is closed. It seems to just be those using Excel 2016.

    Any suggestions for making it 'stick'? (internet research suggests it may be a security issue)


    BTW, in the interests of full disclosure and to avoid wasting anybody's time, I did also post this query at MrExcel but have so far not received any replies. Link to cross-post:
    (If we come up with a solution here I'll update the MrExcel post).