Hello - I'm trying to add a formula to a spread (not done this before) but obviously I'm missing something major. When the form containing the spread loads I call the following routine to initialize the spread which has a formula in col 11. I would also like the user to be able to modify the formula. Could you please tell me what I've missed? I'm sure it's something stupid but oh well I've been looking all morning for an answer.
I'm using Spread7 with VB6.
Vic
Private Sub SetupCalcTable()
'fpSpread1.BackColorStyle = 1
'ret = fpSpread1.SetOddEvenRowColor(&HFFFFFF, &H80000008, &H80FFFF, &H80000008)
'fpSpread1.ProcessTab = True
'fpSpread1.CellType = CellTypeEdit
'fpSpread1.TypeEditMultiLine = True
fpSpread1.BackColorStyle = 1
ret = fpSpread1.SetOddEvenRowColor(&HFFFFFF, &H80000008, &H80FFFF, &H80000008)
fpSpread1.ProcessTab = True
With fpSpread1
For i = 1 To 12
Select Case i
Case 1
.Row = -1
.Col = i
.Lock = False
.TypeHAlign = TypeHAlignLeft
.Row = 0
.Text = "ID"
.FontBold = True
.ColWidth(i) = 10
.ColHidden = True
Case 2
.Row = -1
.Col = i
.Lock = False
.TypeHAlign = TypeHAlignLeft
.Row = 0
.Text = "Facility ID"
.FontBold = True
.ColWidth(i) = 10
.ColHidden = True
Case 3
.Row = -1
.Col = i
.Lock = False
.TypeHAlign = TypeHAlignLeft
.Row = 0
.Text = "Substation"
.FontBold = True
.ColWidth(i) = 15
.ColHidden = False
Case 4
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeNumber
.TypeNumberDecPlaces = 0
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Largest Equipment Volume (gal)"
.FontBold = True
.ColWidth(i) = 8
Case 5
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeNumber
.TypeNumberDecPlaces = 0
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Secondary Containment (Gal)"
.FontBold = True
.ColWidth(i) = 12
.ColHidden = False
Case 6
.Row = -1
.Col = i
.CellType = CellTypeCheckBox
.TypeCheckType = TypeCheckTypeNormal
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Sufficient Containment"
.FontBold = True
.ColWidth(i) = 9
Case 7
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeCheckBox
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Practicable Secondary Containment"
.FontBold = True
.ColWidth(i) = 9
.ColHidden = False
Case 8
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeNumber
.TypeNumberDecPlaces = 0
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Site Width (ft)"
.FontBold = True
.ColWidth(i) = 5
Case 9
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeNumber
.TypeNumberDecPlaces = 0
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Site Length (ft)"
.FontBold = True
.ColWidth(i) = 7
Case 10
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeNumber
.TypeNumberDecPlaces = 0
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Site Depth (in)"
.FontBold = True
.ColWidth(i) = 5
.ColHidden = False
Case 11
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeNumber
.TypeNumberDecPlaces = 0
.TypeHAlign = TypeHAlignCenter
.Formula = "F# * G# * (H# / 12) * .2 * 7.481"
.AllowUserFormulas = True
.Row = 0
.Text = "Allowable Containment Volume (gal)"
.FontBold = True
.ColWidth(i) = 10
Case 12
.Row = -1
.Col = i
.Lock = False
.CellType = CellTypeCheckBox
.TypeHAlign = TypeHAlignCenter
.Row = 0
.Text = "Site Footprint Sufficient"
.FontBold = True
.ColWidth(i) = 10
End Select
Next i
End With
End Sub