formratings
05-04-2015, 05:28 AM
Hi Guys
I am new to this and have no knowledge of VBA whatsoever
any help appreciated with this please, would someone look at the code and tell me if there are any errors, from what I understand this book is set run between 11.00 and 12.30 and run command button 21 which loads data
then to run 4 mins before the time in f13 subject to correct date being in d13
the problem is sometimes it runs and sometimes it doesn't and I am at a loss to know why, is the code faulty or something on my PC, I have windows 8.1 and office 2013
as far as I understand if I have the sub open and click run it should run but it doesn't does this tell you anything
BUT when I click on a sheet in workbook message comes up this cell is locked it will run sometimes but I get message after it has run grey cross in a red circle and 40013295
thanks in advance dave
Private Sub Workbook_Open()
Dim data
Dim entervalues_sh As Worksheet, lrow As Long
If ThisWorkbook.Name = "Btw.xlsm" Then
If Sheets("nofrillsbetfair").Range("d13") <> Date Then
If Time > TimeSerial(11, 0, 0) And Time <= TimeSerial(12, 30, 0) Then Application.Run "sheet2.CommandButton21_Click" Else Application.OnTime TimeSerial(11, 0, 0), "Btw.xlsm!Sheet2.CommandButton21_Click"
With Sheets("nofrillsbetfair")
.Unprotect "daveGood"
.Range("d13") = Date
.Protect "daveGood"
End With
ThisWorkbook.Save
End If
End If
Set entervalues_sh = Sheets("enter values")
lrow = entervalues_sh.Cells(Rows.Count, "w").End(xlUp).Row
If lrow = 1 Then Exit Sub
data = entervalues_sh.Range("w1:w" & lrow)
For i = 2 To lrow
If data(i, 1) <> "" Then
temp = Trim(data(i, 1))
If Len(temp) = 2 Then
itime = TimeSerial(temp, 0, 0)
ElseIf Len(temp) <> 5 Then
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1) * 10, 0)
Else
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1), 0)
End If
Application.OnTime DateAdd("n", -IIf(Sheets("nofrillsbetfair").Range("f13") = 0, 4, Sheets("nofrillsbetfair").Range("f13")), itime), "Btw.xlsm!Sheet2.CommandButton22_Click"
End If
Next
End Sub
I am new to this and have no knowledge of VBA whatsoever
any help appreciated with this please, would someone look at the code and tell me if there are any errors, from what I understand this book is set run between 11.00 and 12.30 and run command button 21 which loads data
then to run 4 mins before the time in f13 subject to correct date being in d13
the problem is sometimes it runs and sometimes it doesn't and I am at a loss to know why, is the code faulty or something on my PC, I have windows 8.1 and office 2013
as far as I understand if I have the sub open and click run it should run but it doesn't does this tell you anything
BUT when I click on a sheet in workbook message comes up this cell is locked it will run sometimes but I get message after it has run grey cross in a red circle and 40013295
thanks in advance dave
Private Sub Workbook_Open()
Dim data
Dim entervalues_sh As Worksheet, lrow As Long
If ThisWorkbook.Name = "Btw.xlsm" Then
If Sheets("nofrillsbetfair").Range("d13") <> Date Then
If Time > TimeSerial(11, 0, 0) And Time <= TimeSerial(12, 30, 0) Then Application.Run "sheet2.CommandButton21_Click" Else Application.OnTime TimeSerial(11, 0, 0), "Btw.xlsm!Sheet2.CommandButton21_Click"
With Sheets("nofrillsbetfair")
.Unprotect "daveGood"
.Range("d13") = Date
.Protect "daveGood"
End With
ThisWorkbook.Save
End If
End If
Set entervalues_sh = Sheets("enter values")
lrow = entervalues_sh.Cells(Rows.Count, "w").End(xlUp).Row
If lrow = 1 Then Exit Sub
data = entervalues_sh.Range("w1:w" & lrow)
For i = 2 To lrow
If data(i, 1) <> "" Then
temp = Trim(data(i, 1))
If Len(temp) = 2 Then
itime = TimeSerial(temp, 0, 0)
ElseIf Len(temp) <> 5 Then
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1) * 10, 0)
Else
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1), 0)
End If
Application.OnTime DateAdd("n", -IIf(Sheets("nofrillsbetfair").Range("f13") = 0, 4, Sheets("nofrillsbetfair").Range("f13")), itime), "Btw.xlsm!Sheet2.CommandButton22_Click"
End If
Next
End Sub