Hi all

Borrowed a friends code to move some cells when you click a live button and
then on a crytalise button. The 'live' button changes colour when you are
ready and clicking the crystalise button should reset the colour and move a
row of numbers into another spreadsheet.
keep tripping up on the following;
If liveButton.Fill.ForeColor.SchemeColor = 13 Then

Anyone have any ideas? I've included the whole code below;

im thisShape As Shape
Set thisShape = ActiveSheet.Shapes(Application.Caller)
Dim r As Range
Set r = thisShape.TopLeftCell
'check that this is a row that we have made live
Dim liveButton As Shape
For Each liveButton In ActiveSheet.Shapes
If liveButton.TopLeftCell.Row = r.Row And
liveButton.TopLeftCell.Column = 14 Then
GoTo checkColour
End If
Next liveButton
checkColour:
If liveButton.Fill.ForeColor.SchemeColor = 13 Then
Exit Sub
End If

'check that we have all the required entries
If Not (checkCellValue(Cells(r.Row, 6), "Stop/Exit $")) Then
Exit Sub
End If

'find the pnl row
Dim firstCellInPnLRow As Range
Set firstCellInPnLRow = Cells(25, 5)
While firstCellInPnLRow.Value < Cells(r.Row, 5).Value
Set firstCellInPnLRow = firstCellInPnLRow.Offset(1, 0)
If (firstCellInPnLRow.Value = "") Then
MsgBox ("Can't find entry in PnL table for this instrument")
Exit Sub
End If
Wend

'find the trade log entry for this row
Dim logSheet As Worksheet
Set logSheet = ActiveWorkbook.Sheets("Trade Log")
Dim firstCellInRow As Range
Set firstCellInRow = logSheet.Cells(2, 1)
Dim found As Boolean
found = False
While firstCellInRow.Value < ""
If firstCellInRow.Value = Cells(r.Row, 17).Value Then
found = True
GoTo after
End If
Set firstCellInRow = firstCellInRow.Offset(1, 0)
Wend
after:
If found = False Then
MsgBox "Can't find log entry for this row"
Exit Sub
End If

'update the trade log entry
firstCellInRow.Offset(0, 2).Value = Date
firstCellInRow.Offset(0, 7).Value = Cells(r.Row, 10)

'update the pnl
firstCellInPnLRow.Offset(0, 1).Value = firstCellInPnLRow.Offset(0,
1).Value + Cells(r.Row, 6).Value

'look up the fx rate
Dim cur As String
cur = firstCellInRow.Offset(0, 12).Value
Set logSheet = ActiveWorkbook.Sheets("Reference Data")
Dim referenceTableRow As Range
Set referenceTableRow = logSheet.Cells(3, 12)
While (Not referenceTableRow.Value = "" And Not referenceTableRow.Value
= cur)
Set referenceTableRow = referenceTableRow.Offset(1, 0)
Wend
If (referenceTableRow.Value = "" Or referenceTableRow.Offset(0, 1).Value
= "") Then
MsgBox "Can't find fxrate for " + cur
End If
firstCellInRow.Offset(0, 13) = referenceTableRow.Offset(0, 1).Value

'clear the row ready for a new trade
Call clear

End Sub


I am trying to run this on excel 03 and I'm not sure what this was written
on -would that make a difference?

If anyone has any thoughts I would be most grateful!!!

I think it is dropping into that code after it has processed all buttons,
therefore trying to get the properties of a null object. The Goto code you
have in this proc is very badly written, jumping out of a loop, and then you
do it again later on.

I think the whole routine needs a re-write.

--
__________________________________
HTH

Bob

"out of my depth" wrote in message
...
Hi all

Borrowed a friends code to move some cells when you click a live button
and
then on a crytalise button. The 'live' button changes colour when you are
ready and clicking the crystalise button should reset the colour and move
a
row of numbers into another spreadsheet.
keep tripping up on the following;
If liveButton.Fill.ForeColor.SchemeColor = 13 Then

Anyone have any ideas? I've included the whole code below;

im thisShape As Shape
Set thisShape = ActiveSheet.Shapes(Application.Caller)
Dim r As Range
Set r = thisShape.TopLeftCell
'check that this is a row that we have made live
Dim liveButton As Shape
For Each liveButton In ActiveSheet.Shapes
If liveButton.TopLeftCell.Row = r.Row And
liveButton.TopLeftCell.Column = 14 Then
GoTo checkColour
End If
Next liveButton
checkColour:
If liveButton.Fill.ForeColor.SchemeColor = 13 Then
Exit Sub
End If

'check that we have all the required entries
If Not (checkCellValue(Cells(r.Row, 6), "Stop/Exit $")) Then
Exit Sub
End If

'find the pnl row
Dim firstCellInPnLRow As Range
Set firstCellInPnLRow = Cells(25, 5)
While firstCellInPnLRow.Value < Cells(r.Row, 5).Value
Set firstCellInPnLRow = firstCellInPnLRow.Offset(1, 0)
If (firstCellInPnLRow.Value = "") Then
MsgBox ("Can't find entry in PnL table for this instrument")
Exit Sub
End If
Wend

'find the trade log entry for this row
Dim logSheet As Worksheet
Set logSheet = ActiveWorkbook.Sheets("Trade Log")
Dim firstCellInRow As Range
Set firstCellInRow = logSheet.Cells(2, 1)
Dim found As Boolean
found = False
While firstCellInRow.Value < ""
If firstCellInRow.Value = Cells(r.Row, 17).Value Then
found = True
GoTo after
End If
Set firstCellInRow = firstCellInRow.Offset(1, 0)
Wend
after:
If found = False Then
MsgBox "Can't find log entry for this row"
Exit Sub
End If

'update the trade log entry
firstCellInRow.Offset(0, 2).Value = Date
firstCellInRow.Offset(0, 7).Value = Cells(r.Row, 10)

'update the pnl
firstCellInPnLRow.Offset(0, 1).Value = firstCellInPnLRow.Offset(0,
1).Value + Cells(r.Row, 6).Value

'look up the fx rate
Dim cur As String
cur = firstCellInRow.Offset(0, 12).Value
Set logSheet = ActiveWorkbook.Sheets("Reference Data")
Dim referenceTableRow As Range
Set referenceTableRow = logSheet.Cells(3, 12)
While (Not referenceTableRow.Value = "" And Not referenceTableRow.Value
= cur)
Set referenceTableRow = referenceTableRow.Offset(1, 0)
Wend
If (referenceTableRow.Value = "" Or referenceTableRow.Offset(0,
1).Value
= "") Then
MsgBox "Can't find fxrate for " + cur
End If
firstCellInRow.Offset(0, 13) = referenceTableRow.Offset(0, 1).Value

'clear the row ready for a new trade
Call clear

End Sub


I am trying to run this on excel 03 and I'm not sure what this was written
on -would that make a difference?

If anyone has any thoughts I would be most grateful!!!