Macro VBA help

[aubweaver]

Hi
My knowledge of macros/VBA is limited to say the least but I'm learning and attempting to build the odd macro. However I have managed to build various 'auto open' macro's but I always end up with the same problem, they work if I manually enter a 'flag' ie text or number in a referenced cell followed by pressing 'ENTER' but if they are entered automatically by a basic 'IF' statement or by an 'F' Col. odds change nothing until I manually enter anything in any cell and press ENTER, then the macro fires. I'm sure it must something simple that I'm missing, if anybody has a pointer, it would be greatly appreciated.
Many Thanks,
Peter.

[MarkRussell]

Hi,

You need an event to fire to trigger your macro.
The usual way to do it is when Betting Assistant updates your spreadsheet.

Have a read through the following thread which should help:-
http://www.gruss-software.co.uk/forum/viewtopic.php?t=5132

Regards,
Mark

[aubweaver]

Thanks Mark for your quick response, I'll follow up on the links you left and try decipher the info' accordingly.
Many Thanks, Peter

[aubweaver]

Hi,
I've looked at the link and have read the information and kind of understand the reasoning but unfortunately I cannot get anything to work. A short while agoI was sent a worksheet with a macro from a forum member and have tried to run the macro various times but with zero happening I just end up scratching my head. This is the macro below which is in line with the details in the link that was recently sent.

Dim CurrentRace As String
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
Application.EnableEvents = False
' Check if the race has just changed and clear any old flags
If Range("BE5") = "A" Then
Range("BC5:BD35").ClearContents
End If
' Check if a flag must be placed for any BACK bet
If Range("F3:F40") >= 4 Then
Range("BC5") = "X"

End If
' Check if a flag must be placed for any LAY bet
If Range("BD3") >= 1 Then
For x = 5 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("Q" & x) = "LAY" Then Range("BD" & x) = "Y"
Next x
End If

CurrentRace = Range("A1").Value
Application.EnableEvents = True
End If

End Sub

I've attempted to test the worksheet/macro, I've also loaded the macro into other new workbook/sheets, opened it with BA loaded races, unloaded, reloaded etc.etc. but nothing happens on the sheet, no changes, no clearing of flags etc.
For your information I also copied tried out the macro that 'Osknows' had left regarding BA refresh/cell updates (in the link) and that worked without a hitch, in fact I couldn't stop it..!
Is there something I need to do to get the above macro started..? I've also tried changing some of the cell refs and keyed in random texts to see if they clear when a new race is loaded but to no avail.
Although I won't need all the stuff that is in that macro if I could see it work it would be nice, I'd also have something concrete to work with and perhaps I might also keep some hair!
Many thanks,
Peter

[aubweaver]

I just realised I sent one of my trial changed macros this is the original:
The 4th/5th lines were changed on the previous macro when I was attempting to try different things.

Dim CurrentRace As String
Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
Application.EnableEvents = False
' Check if the race has just changed and clear any old flags
If CurrentRace <> Range("A1").Value Then
Range("BC5:BD35").ClearContents
End If
' Check if a flag must be placed for any BACK bet
If Range("F3:F40") >= 4 Then
Range("AH5") = "X"

End If
' Check if a flag must be placed for any LAY bet
If Range("AG3") >= 1 Then
For x = 5 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("Q" & x) = "LAY" Then Range("BD" & x) = "Y"
Next x
End If

CurrentRace = Range("A1").Value
Application.EnableEvents = True
End If

End Sub

[Shaun]

Make sure you have betting enabled when you attach the sheet, remove triggers from the trigger column if you are worried about accidental bets being placed for testing.

[aubweaver]

Thanks Shaun, I run the system every night (Aussie time) with my basic excel functions, 'enabled betting etc. etc. without an issue, it's just I'm having problems with getting into macros and having them work/fire. I'm sure I'll get there in the end. The problem is if something is not happening and you're not too sure what should be happening maybe that's exactly what should be happening.....nothing??!!
"The mind is boggling."
Thanks,
Peter.

[steven]

Hi Peter,
First impression as I haven't really read through properly yet, but try moving the two 'Application.EnableEvents' statements outside of the 'If' loops, the first one will turn events off, but unless the second statement is executed they will remain off, so it won't fire again.

Regards
Steve

[aubweaver]

Hi Steven,
Thanks for your input. As I said earlier my VBA knowledge is limited (overstatement). However, I did move the last 'enable_events' to outside the 'If' statement, excel didn't throw a wobbly so I guess it was ok. The first one couldn't be moved i.e I wasn't too sure what other changes I needed to make. Anyway I gave it a go with the last one moved and nothing. This macro was/is being used by another forum member without any issues, so I guess I theoretically shouldn't need to make any adjustments other than my own requirements. As I mentioned initially, I have built the odd macro which work but only if I key anything into any cell afterwards ie cause an event. Which brought me back to this macro after Mark informed that I needed to use a BA refresh/update to trigger a macro automatically, he also forwarded me the link to a previous forum question and the resulting info'.
Yesterday however when I was trying anything that came into my head, I shut everything down and restarted the computer reopened and loaded BA/excel etc.etc. and the macro fired ..?! That is it cleared the 'flags', the first command,(at least it's a start) but that was the only time, unless I shut down the system each time!! I've checked my security settings, macro settings etc. but all seems good , I think it will be a lot of trial and error.
Thanks again,
Peter.

[steven]

Hi Peter,
It still sounds as though the events are off. Once that Application.EnableEvents is turned off then they stay off untill you either turn them back on (which you can do by putting 'Application.EnableEvents=True' into the Immediate Window and pressing enter) or untill you shut excel down and start again. This may not be the problem. But if you moved that Application.EnableEvents=True to the last line before End Sub it won't take effect until you either restart excel or turn events back on.
Sorry I don't want to send you in the wrong direction. If you have restarted and it still has problems then obviously something else is happening. I'll take a proper look at the code when I have some time to concentrate (which will be tonight). But I hope you can fix it before then as I know how annoying these things can be.
Regards
Steve

[osknows]

Where have you placed this macro "Private Sub Worksheet_Change(ByVal Target As Range)"?

Is it in the SHEET object and not a module?

Also there's a few things that won't work

1) Range("A1").Value

You need state which Range. The above example will use the value in A1 of the selected sheet. If you have more than 1 sheet this will cause problems. State the full path using With statements

eg

With ThisWorkbook.Sheets(Target.Worksheet.Name)
.Range("A1").Value
end with

2) If Range("F3:F40") >= 4 Then
Range("AH5") = "X"
End If

This won't work and will result in an error. You can't test whether a whole range is >=4 like this. I'm unsure whether you are attempting to test whether any cell in the range F3:F40 is greater than 4 or if the SUM of F3:F40 is greater than 4?Either use a FOR NEXT loop to test each cell individually or test if the SUM of the range is greater than 4.

[osknows]

PS - I'm around all day tomorrow so if you clarify what you're attempting in 2) above I'll help.

[steven]

EDIT-----I prepared this answer offline and just spotted that these points had been raised earlier, but I'll let it stand.-----EDIT

Hi Peter,
A couple of points on your code:

'If CurrentRace <> Range("A1").Value' Each time the macro should be running the

variable 'CurrentRace' will be empty to start with, so when you compare it to cell "A1"

it will be different (unless "A1" is empty). Your code later sets CurrentRace to equal

"A1". But when the macro finishes it forgets everything, variables are not held in

memory.

'If Range("F3:F40") >= 4' You can only compare a value to one cell at a time

this way, and not to a range of cells. So the VBA editor should tell you there is an

error when it gets to this line of code.

Which brings me back to the same subject, the first time this code runs it turns

off Events, and if there are errors they are not turned on again. But going on from here if you could give a brief description of what you are trying to achieve, then I will try and give you hand at getting it going. I don't need to know all the ideas you are trying to work out, but what for example are you trying to achieve with 'If Range("F3:F40") >= 4' are you trying to count how many flags there are or is it just a value of 4 or above in one cell. And just to give you a clue on the first part if at the end of the code you copy the value of cell "A1" into some out of the way place on your sheet EG. Range("zz1").value = Range("A1").value then you can compare the two at the start of your code to see if anything changes.

Regards
Steve

[osknows]

You're correct that 'CurrentRace' begins empty but the variable is a declared outside the procedure so the scope is effectively at module/worksheet level and will be retained after the procedure ends.

[steven]

Indeed it does, I didn't realize that, thank you, you learn something new every day.

Regards
Steve