I must be missing something stupid, as ive done this with the previous project. basically ive set up a useform with, two DTPickers and a few text boxes, plus one spin button. the aim is to work out the termination charges for a contract. Its essentially working, but, other than the DTPickers, the other info doesnt pass to the cell until i close the form. Although this doesnt prevent the command button from performing the calculation, i want the data to pass to the cell "live".
What am i doing wrong ?
[VBA]Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Sheet1")
TextBox2.Value = Format(.Range("B6").Value, "£00.00")
TextBox3.Value = Format(.Range("B7").Value, "£00.00")
End With
End Sub
Private Sub DTPicker1_Change()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Range("B2") = DTPicker1.Value
End Sub
Private Sub DTPicker2_Change()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Range("B3") = DTPicker2.Value
End Sub
Private Sub SpinButton1_SpinUp()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Sheet1")
.Range("B5").Value = .Range("B5").Value + 0.5
TextBox1.Value = Format(.Range("B5").Value, "£00.00")
End With
DoEvents
update
End Sub
Private Sub SpinButton1_SpinDown()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Sheet1")
.Range("B5").Value = .Range("B5").Value - 0.5
TextBox1.Value = Format(.Range("B5").Value, "£00.00")
End With
DoEvents
update
End Sub
Private Sub update()
Application.ScreenUpdating = False
calculate
With ThisWorkbook.Sheets("Sheet1")
.Range("B5").Value = .Range("B5").Value + 0.5
.Range("B5").Value = .Range("B5").Value - 0.5
TextBox1.Value = Format(.Range("B5").Value, "£00.00")
End With
End Sub
[/VBA]
[vba]
Private Sub UserForm_Initialize()
TextBox1.Text = FormatCurrency(Sheet1.Range("B5").Value)
End Sub
Private Sub DTPicker1_Change()
Sheet1.Range("B2") = DTPicker1.Value
End Sub
Private Sub DTPicker2_Change()
Sheet1.Range("B3") = DTPicker2.Value
End Sub
Private Sub SpinButton1_SpinUp()
spin 1
End Sub
Private Sub SpinButton1_SpinDown()
spin 0
End Sub
Sub spin(y)
With Sheet1
.Range("B5").Value = .Range("B5").Value + IIf(y = 1, 0.5, -0.5)
For j = 1 To 3
Me("TextBox" & j).Text = FormatCurrency(.Cells(4 + j, 2).Value)
Next
End With
End Sub[/vba]