PDA

View Full Version : Solved: Passing data from userform to sheet/cell



gringo287
11-24-2012, 06:55 AM
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
11-24-2012, 10:59 AM
try replacing all textbox.value with textbox.text

gringo287
11-24-2012, 11:46 AM
Hi Patel,

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

patel
11-24-2012, 12:58 PM
attach a sample file for testing

gringo287
11-24-2012, 01:24 PM
thank you

gringo287
11-24-2012, 02:49 PM
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
11-25-2012, 03:25 PM
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
11-25-2012, 03:44 PM
Thanks snb, ill take a proper look at this when i get a min.