Help! Need to copy data from UserForm to a worksheet

Solo

New Member
Joined
Mar 20, 2002
Messages
2
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...=)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top