Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,292
    Thanks
    47
    Thanked 257 Times in 237 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-16 at 00:07.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  4. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  5. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,292
    Thanks
    47
    Thanked 257 Times in 237 Posts
    You are using Option Explicit. Please add "Dim I as Integer" near the top in the CMDAdd_Click() routine.

    I as integer.png

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

    HowardC (2014-07-16)

  7. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  8. #7
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  9. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,292
    Thanks
    47
    Thanked 257 Times in 237 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

  10. #9
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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
  •