Help! Need to copy data from UserForm to a worksheet

[Solo]

Hi all,

I am currently writing a Macro which has an userform, which include 3 textboxes, asking for info from users....I would like to paste data, for example, paste textbox#1 data into Sheet1!A1, paste textbox#2 data into Sheet1!B1 and so on.... Also, I would like the macro to look for an empty row before pasting the data into the respective cells...I have tried quite a few method but couldn't manage to make it work....anybody out there can help me? Really appreciate the help...=)

[Joe Was]

See if this helps you?
The Builder code will build a test application for you to test my code with. You only run the builder code once. Copy both macros to a sheet module in a workbook with three default worksheets. Then run the builder.

Press the application button and a user form with three check boxes will open select a check box. A input box will open to get your data.

Your data will be copied to a database table into the next empty cell in the right place in the table.

I think this may get you started on your own code?

JSW


Sub Builder()
'This is the application builder.
'Note: Only run this on a blank workbook.
'The blank workbook should have three blank sheets,
'named "Sheet1, Sheet2 and Sheet3."
'Only run "Builder" once, to create the application.
'By: Joe Was, 12/2001.

Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Utility Log"
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 22
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "To use, press the button below!"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Note: Row 3 on the (Data)Sheet must have valid start values!"
Range("D1").Select

ActiveSheet.Buttons.Add(81, 60.75, 175.5, 33.75).Select
Selection.OnAction = "Sheet1.Utilities"
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 9
End With
Selection.Characters.Text = "Record Utility Readings"
Worksheets("Sheet1").Select
Range("D1").Select

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Worksheets("Sheet1").Select
Sheets("Sheet1").Name = "Menu"
Range("A1:G1").Select
Worksheets("Sheet2").Select
Sheets("Sheet2").Name = "Data"
Application.ScreenUpdating = True
Application.DisplayAlerts = True

With Worksheets("Data")
.Range("B1").Value = "1st Utility"
.Range("B2").Value = "Reading"
.Range("B1:B2").Font.Bold = True
.Range("B1:B2").HorizontalAlignment = xlCenter
.Range("B3").Value = "999999"
.Range("C1").Value = "Usage"
.Range("C2").Value = "This"
.Range("C3").Value = "Period"
.Range("C1:C3").Font.Bold = True
.Range("C1:C3").HorizontalAlignment = xlCenter
.Range("E1").Value = "2nd Utility"
.Range("E2").Value = "Reading"
.Range("E1:E2").Font.Bold = True
.Range("E1:E2").HorizontalAlignment = xlCenter
.Range("E3").Value = "999999"
.Range("F1").Value = "Usage"
.Range("F2").Value = "This"
.Range("F3").Value = "Period"
.Range("F1:F3").Font.Bold = True
.Range("F1:F3").HorizontalAlignment = xlCenter
.Range("H1").Value = "3rd Utility"
.Range("H2").Value = "Reading"
.Range("H1:H2").Font.Bold = True
.Range("H1:H2").HorizontalAlignment = xlCenter
.Range("H3").Value = "999999"
.Range("I1").Value = "Usage"
.Range("I2").Value = "This"
.Range("I3").Value = "Period"
.Range("I1:I3").Font.Bold = True
.Range("I1:I3").HorizontalAlignment = xlCenter
.Range("B3").Font.ColorIndex = 11
.Range("E3").Font.ColorIndex = 11
.Range("H3").Font.ColorIndex = 11
.Range("A1").Select
End With
Sheets("Menu").Select
Range("D1").Select
End Sub

Sub Utilities()
'By: Joe Was, 12/2001.
'This is the application.

Dim clickTest As Variant
Dim my1st As Variant
Dim my2nd As Variant
Dim my3rd As Variant
Dim my1stD As Variant
Dim my2ndD As Variant
Dim my3rdD As Variant
Dim my1stO As Variant
Dim my2ndO As Variant
Dim my3rdO As Variant

'Open checkbox menu.
With Assistant.NewBalloon
.Heading = "Start utility reading;"
.Text = "Click the box which is your choice:"

'Build three check boxes.
For i = 1 To 3
.CheckBoxes(1).Text = ("1st Utility.")
.CheckBoxes(2).Text = ("2nd Utility.")
.CheckBoxes(3).Text = ("3rd Utility.")
Next
.Button = msoButtonSetOkCancel
.Show
clickTest = 0
On Error GoTo myEnd

'Test for which box is checked.
If .CheckBoxes(1).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate

'Get new reading.
my1st = Application.InputBox(prompt:="What is the new reading for the 1st Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)

'Test for errors.
If my1st = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop

'Do data updating and math.
Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("B65536").End(xlUp).Offset(1, 0).Value = my1st
my1stO = Worksheets("Data").Range("B65536").End(xlUp).Offset(-1, 0).Value
my1stD = my1st - my1stO
Worksheets("Data").Range("C65536").End(xlUp).Offset(1, 0).Value = my1stD
Application.ScreenUpdating = True
End If

'Display results.
If my1st <> 0 And my1stO <> 0 Then
MsgBox prompt:="The current reading is: " & my1st & Chr(13) & _
"The last reading was: " & my1stO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my1stD, _
Title:="Current Usage!"
End If

'The next two check boxes are designed as above!
If .CheckBoxes(2).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate
my2nd = Application.InputBox(prompt:="What is the new reading for the 2nd Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)
If my2nd = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop
Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("E65536").End(xlUp).Offset(1, 0).Value = my2nd
my2ndO = Worksheets("Data").Range("E65536").End(xlUp).Offset(-1, 0).Value
my2ndD = my2nd - my2ndO
Worksheets("Data").Range("F65536").End(xlUp).Offset(1, 0).Value = my2ndD
Application.ScreenUpdating = True
End If
If my2nd <> 0 And my2ndO <> 0 Then
MsgBox prompt:="The current reading is: " & my2nd & Chr(13) & _
"The last reading was: " & my2ndO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my2ndD, _
Title:="Current Usage!"
End If

If .CheckBoxes(3).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate
my3rd = Application.InputBox(prompt:="What is the new reading for the 3rd Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)
If my3rd = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop
Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("H65536").End(xlUp).Offset(1, 0).Value = my3rd
my3rdO = Worksheets("Data").Range("H65536").End(xlUp).Offset(-1, 0).Value
my3rdD = my3rd - my3rdO
Worksheets("Data").Range("I65536").End(xlUp).Offset(1, 0).Value = my3rdD
Application.ScreenUpdating = True
End If
If my3rd <> 0 And my3rdO <> 0 Then
MsgBox prompt:="The current reading is: " & my3rd & Chr(13) & _
"The last reading was: " & my3rdO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my3rdD, _
Title:="Current Usage!"
End If

'Test for errors and other events.
If .CheckBoxes(i).Checked = False And clickTest <> 1 Then GoTo Emp
End With
End
myStop:
MsgBox prompt:="Operator ended update, no action taken!", Title:="UpDate Stopped!"
End
Emp:
MsgBox prompt:="You did not check an Utility box.", Title:="Input Data Error!"
End
myEnd:
End Sub

[Solo]

Hi Joe Was,


Thanks a million....it works! I have been trying to write something like that for my work and apparently unable to do so.....really really appreciate it...Thanks.