Help! Need to copy data from UserForm to a worksheet
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Help! Need to copy data from UserForm to a worksheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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...=)

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com