Posts by Excel_Phoenix

Hi,

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

Hi,

It may be the worksheet event that is causing the issue. Have you tried switching them off at the start of your routine and then on again? Like this:

Application.EnableEvents = False
'
'do stuff
'
Application.EnableEvents = True

Hi,

It's rarely necessary to actually select the worksheets and ranges in question. See how you get on with this:

Sub Marketing()


    Sheets("Marketing").Range("A1:D30").Copy _
        Destination:=Sheets("New Starter Form").Range("A45")
    
End Sub

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

Hi,

The clue is in the title of yoru post

Sub TestIfNoEmail()
    Dim x As Long
    Dim lRow As Long
    
    With Sheet1
        lRow = .Range("D4").Value
        For x = 6 To lRow
            If Not IsEmpty(.Cells(x, 3)) And IsEmpty(.Cells(x, 5)) Or .Cells(x, 5) = 0 Then
                MsgBox "Need to add code which will give a Message Box"
                '
                Exit For
                '
                'Exit the For Next loop and continue immediately after the Next statement
                '
            End If
        Next x
    End With


End Sub

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

Hi,

How about something like this:

Sub You()
    Dim xRg As Range
    
    Application.ScreenUpdating = False


    For Each xRg In Range("C3:C420")
        If xRg.Value = "" Then
            If xRg.Offset(-1, 0).Value <> "TOTAL" Then
                xRg.EntireRow.Hidden = True
            End If
        End If
    Next xRg
    
    Application.ScreenUpdating = True


End Sub

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.

Hi,

In addition to the ScreenUpdating adjustment have you tried adding Application.Calculation = xlCalculationManual at the start and then resetting at the end to Application.Calculation = xlCalculationAutomatic ?

Hi,

Are you sure that the cells in question actually contain just "Remove" and not any stray characters, like a Space giving "Remove "?

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?

Hi,

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?

Hi,

Can you still access the VBE? Press Alt & F11 to see.

If you can, re-run the code that you used but change aBar.Visible = False to aBar.Visible = True.

HTH

Thanks Tom. I'll take a look at that and report back.

Hi,

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)

Thanks.

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: https://www.mrexcel.com/forum/excel-...aring-add.html
(If we come up with a solution here I'll update the MrExcel post).