Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Amending User Form to set number of times data to be copied

    I have a Template was a user form which is activated on sheet "Macro"

    The user form copies the data into the vrespective fields once "add Fixed assets" is selected.

    I would like to amend the user form to add a Block to select how many times the data must be copied for eg if "Paper Feeder FS-1135 MFP" is to be repeated, I would like to be able to have a block on the user form and select how hany time this is to be repeated. For eg if I type in all the iformation in the repective fiilds on the user form for "Paper Feeder FS-1135 MFP" and I select 1, it must only be copied one, if 2 is selected if must copy the information twice etc

    I have attached my sample data

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,197
    Thanks
    45
    Thanked 227 Times in 210 Posts
    Howard,

    You can add a spinbutton with a text box to control the number of times the entry is copied to the sheet. Below is the changes to your Userform code that you will need to make:

    spinner.png

    Code:
    Private Sub UserForm_Initialize()
    TxtAssetype.Value = ""
    TxtDes.Value = ""
    TxtDept.Value = ""
    TxtDate1.Value = ""
    Txtcost.Value = ""
    SpinButton1.Value = 1 'ADD
    TextBox1.Value = SpinButton1.Value 'ADD
    End Sub
    Code:
    Private Sub SpinButton1_SpinDown()
    TextBox1.Value = SpinButton1.Value
    End Sub
    
    Private Sub SpinButton1_SpinUp()
    TextBox1.Value = SpinButton1.Value
    End Sub
    In the Private Sub CMDAdd_Click() routine, add the loop:

    Code:
        For I = 1 To TextBox1.Value  'ADD
            iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
             '  .Unprotect Password:="password"
            .Cells(iRow, 1).Value = Me.TxtAssetype.Value
            .Cells(iRow, 4).Value = Me.TxtDes.Value
            .Cells(iRow, 5).Value = Me.TxtDept.Value
            .Cells(iRow, 9).Value = Format(TxtDate1.Value, "mm/dd/yyyy")
            .Cells(iRow, 12).Value = Me.Txtcost.Value
        Next I  'ADD
    Have you considered how you will populate you comboboxes? If you need help then let me know.

    Maud
    Last edited by Maudibe; 2014-07-15 at 23:07.

  4. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for all the help Maude

    I will get back to you tomorrow as I need to test the code this evening (it is 14:48pm over here)

    Howard

  5. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Maud

    Thanks for your help. I am getting closer to the desired result.

    After entering the data and selecting "Add Fixed Assets" , I get a message "compile error variable not defined and the code below is selected

    Code:
     For I = 1 To TextBox1.Value
    I have attached sample data. Please test and amend code. I tried to amend this myself, but could not succeed



    With regard to populating the textboxes, this is only a sample. If I get stuck I will make contact with you. Always grateful for your help
    Attached Files Attached Files

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,197
    Thanks
    45
    Thanked 227 Times in 210 Posts
    You are using Option Explicit. Please add "Dim I as Integer" near the top in the CMDAdd_Click() routine.

    I as integer.png

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2014-07-16)

  8. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Maud

    I overlooked this (Dim I as Integer). Have tested and code works perfectly

    Once again thanks for all the help

    Howard

  9. #7
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Maud

    In the text box containing the cost (Txtcost), I would like the code amended, so that if there is a xzero after the decimal pace for eg 7040.00 and the user enters 7040, the code will insert 7040.00

    Howard

  10. #8
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,197
    Thanks
    45
    Thanked 227 Times in 210 Posts
    Howard,

    Add this code to your userform's module

    Code:
    Private Sub Txtcost_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Txtcost.Value = Format(Txtcost.Value, "##.00")
    End Sub

  11. #9
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Maud

    Thanks for the help, much appreciated

    Howard

Posting Permissions

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