Solved: Passing data from userform to sheet/cell

[gringo287]

Hi,

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 ?

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

[patel]

try replacing all textbox.value with textbox.text

[gringo287]

Hi Patel,

I got all excited then, as that method has helped before when dealing with percentages.. however this didnt help.

[patel]

attach a sample file for testing

[gringo287]

thank you

[gringo287]

good job im not getting paid for this... id have to do workback to make up for lost time. grrrrrr

Sub open_userform()
UserForm1.Show vbModeless
End Sub

[snb]

This might suffice:


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

[gringo287]

Thanks snb, ill take a proper look at this when i get a min.