Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Code to add data using entry form

    Hi Excel experts-

    I continue to learn Excel. It is a blast.

    I've gathered together snippets of code for doing various things, but I haven't arrived at the point where I can do as much as I would like.

    The attached excel file has a Form to add data. I hope an excel pro will provide code. Some lines on the entry form may be blank, to be filled in later.

    Please look at the download, it has sample data and explanation

    Thanks in advance for your expertise and help.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2015-05-12 at 08:53.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi newbie.

    OK. For macros in a file, you need to save the workbook in either .xlsm or .xlsb (my favourite) format.
    The code you need is elegantly simple:
    Code:
    Sub showDataEntryForm()
    
    [a1].Select
    ActiveSheet.ShowDataForm
    
    End Sub
    This code is assigned to a button on the relevant sheet.

    see attached example file.

    If you add new columns, the data entry form will be automatically adjusted.
    No code adjustments necessary!
    How nifty is that!

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-05-12 at 05:09.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy-

    Thanks for pointing out this approach. I appreciate your help. You are right, it is nifty.

    All Excel Experts:

    I've been working on a form part of the day to use VBA to create a form. One of my objectives is to learn enough VBA to work on basic projects. I'm in the A,B,C of it right now. Having fun though.

    The attached file has a form with code I've pieced together. It works, but I'm interested to find out if this is a good approach or is there a better way?

    Any tips, ideas, thoughts you might offer would be helpful.

    Lastly, Zeddy why do you like .xlsb more than .xlsm when you save a worksheet?
    Attached Files Attached Files
    Last edited by Excelnewbie; 2015-05-12 at 23:27.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi newbie

    see attached example file.

    I liked the way you assigned your own names to the command buttons on your Form.
    That's what I would do.
    Also, you assigned your own names to the textboxes.
    I would do that too, but I wouldn't use txb1, txb2,.. as these 'names' can be 'confused' with cell addresses (i.e. three letters and a number - cell columns go up to xfd)
    I would assign names to the Labels as well.
    If you look at your Form, you'll see the order of the label names is not in sequence.
    No big deal, but, when you assign your own names, you are in control.

    This means we can easily refer to the Form labels, and make them match the Data column headers, using the initialise event for the Form.

    The best approach for doing what you are doing in this specific case is to use the method I showed in post#2. Load that test file, insert some new columns, rename the headers, and check the Form that is displayed.
    Minimal vba code required.

    For workbooks, the reason I prefer saving as .xlsb files is that they are typically half the size of the .xlsm file (for large files say, over 200Kb). Smaller file size means less network traffic and quicker loading. Unfortunately, you cannot post files of .xlsb format here!

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Excelnewbie (2015-05-13)

  6. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy-

    Thanks for everything.

    I'm going to continue expanding the features and may be back with more questions.

    Thanks for your thoughts.

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excelnewbie,

    Ditto everything Zeddy states plus a few more points.

    Like variables you can loop through objects on your form. For that reason I like the naming convention for grouped form objects to be name1, name2, name3,... For instance, in your code:
    Code:
    'Populate the new data values into the 'Data' worksheet.
        ActiveCell.Value = i 'Next ID number
        ActiveCell.Offset(0, 1).Value = Me.txb1.Text 'set col B
        ActiveCell.Offset(0, 2).Value = Me.txb2.Text 'set col C
        ActiveCell.Offset(0, 3).Value = Me.txb3.Text 'set col D
        ActiveCell.Offset(0, 4).Value = Me.txb4.Text
        ActiveCell.Offset(0, 5).Value = Me.txb5.Text
        ActiveCell.Offset(0, 6).Value = Me.txb6.Text
    
    'Clear down the values ready for the next record entry...
        Me.txb1.Text = Empty
        Me.txb2.Text = Empty
        Me.txb3.Text = Empty
        Me.txb4.Text = Empty
        Me.txb5.Text = Empty
        Me.txb6.Text = Empty
    could be coded as:
    Code:
    'Populate the new data values into the 'Data' worksheet.
    For a = 1 To 6
        ActiveCell.Offset(0, a).Value = Me.Controls("txb" & a).Text
    Next a
    'Clear down the values ready for the next record entry...
    For a = 1 To 6
        Me.Controls("txb" & a).Value = Empty
    Next a
    When looping in the above fashion, it is important to keep your objects in sequence as Zeddy pointed out.

    In the above code, I would make the code to clear the text boxes a separate routine then branch to it from within your code (highlighted in blue). This new subroutine would also be available for a clear button to clear the textboxes.
    Code:
    Private Sub cmdAdd_Click()
        Dim i As Integer
        'position cursor in the correct cell A2.
        Range("A2").Select
        i = 1 'set as the first ID
        'check to see the next available blank row start at cell A2...
        Do Until ActiveCell.Value = Empty
            ActiveCell.Offset(1, 0).Select 'move down 1 row
            i = i + 1 'keep a count of the ID for later use
        Loop
        'Populate the new data values into the 'Data' worksheet.
        ActiveCell.Value = i 'Next ID number
        For a = 1 To 6
            ActiveCell.Offset(0, a).Value = Me.Controls("txb" & a).Text
        Next a
         'Clear down the values ready for the next record entry...CALL ROUTINE
        ClearTextboxes
        Me.txb1.SetFocus 'positions the cursor for next record entry
    
    End Sub
    
    Private Sub CleardButton_Click()
    'NEW BUTTON: CALL ROUTINE
    ClearTextboxes
    End Sub
    
    Public Sub ClearTextboxes()
    'CLEAR ALL TEXTBOXES
    For a = 1 To 6
        Me.Controls("txb" & a).Value = Empty
    Next a
    End Sub
    You also want to make sure you understand the differences between Unload vs .hide and UserForm_Initialize() vs UserForm_Activate()

    Also keep in mind that textbox values are strings. So, if you want to perform mathematical operations in the future with your textboxes, the results might not be what you expect.
    For example, if txb1.value=1 and txb2.value=2 then
    txb1.Value + txb2.Value will equal 12 not 3

    You will need to convert to numeric:
    Val(txb1.Value) + Val(txb2.Value) will equal 3

    HTH,
    Maud

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

    Excelnewbie (2015-05-14)

  9. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud-

    Thank you for your help and insight!

    I used the code you provide and everything is A1. I'm manipulating the code to understand it better.

    I have a question about the numbering part of the code. If a user deletes rows of data the numbering code doesn't adjust. Is there a way to reorder the number when a row or rows of data are deleted?

    Once again, thanks for sharing your VBA talent.

    PS I have an off the wall question. Maybe I shouldn't take your time, so if you decide not to deal with it, I understand.

    When I manipulate the code and comment out, Range("A2").Select, and press the add button the data appears in AC thur AI on row 18, and continues from that point as I add data. Why is that range being selected?

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excelnewbie,

    If you look at your code, just about every line references the Active cell. Your code initially sets A2 as the active cell then references everything from that point. When you comment out the line Range("A2").Select then the active cell is whatever cell happens to be selected. In your query, it sounds like cell AC18 happened to be the active cell at the time the code was run and your Do...Loop incremented the active cell from there. So, the way your code is designed, it must start with A2 selected.

    To answer your other question of a deleted row, I will point you in the direction and let's see if you can modify the code. Because you have a header row, the ID number will always be one less than the row it is on. For example, an ID of 7 should be on row 8. If it is not then either a row has been added or deleted. The code should check that relationship and write new sequential IDs in column A if it is not correct.

    Hint: Write this code in the sheet module's worksheet_Change event and use the following line of code to find the last row:

    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    If you get stuck, we'll help you out. Trial and error is the best way to learn.

    Maud

  11. #9
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maudible

    I tried many things but didn't hit pay dirt, so to speak.

    Following are my Excel newbie’s thoughts for the problem:

    A1 = Count
    A2 = 1 (=A2 -1)
    A3 = 2 (=A3 -1)
    A4 = 3 (=A4 -1)

    Event—user deletes data in row A2 resulting in:

    A2 = 1 (=A2 -1)
    A4 = 3 (=A4 -1)

    Macro needs to modify A4 by moving to A2 and redo the procedure =A2 -1 to the last row.
    This can be done with a worksheet_Change event and code that determines the last row:

    Activecell = LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row.


    From this point I not sure how to proceed, but it might look like this:

    Range("A2").Select ‘move to the first row to be number 1

    i = 1 ‘ variable

    ActiveCell.Offset(1, 0).Select ‘moves the row down

    i = i + 1 ‘adds one to the number in the last cell

    Do Until ActiveCell.Value = Empty ‘stop when active cells are empty

    Loop until

    Activecell = LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row



    This was a good exercise. Reminds me of being in first grade trying to use the ABC's to write words in sentences.

    I’m interested more than ever now to see code that does the job.
    Last edited by Excelnewbie; 2015-05-15 at 15:01.

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi newbie

    There are lots of ways to do what you want.
    Essentially, you are just renumbering the records sequentially from 1 to the last record.
    This routine uses the Autofill method:

    Code:
    Sub zzzz()
    
    zLastRow = [a1].CurrentRegion.Rows.Count    'e.g 53
    
    [a2] = 1
    [a3] = 2
    
    temp = "a2:a" & zLastRow                    'e.g. "a2:a53"
    [a2:a3].AutoFill Destination:=Range(temp), Type:=xlFillDefault
    
    End Sub
    zeddy

  13. #11
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy-

    Thanks for providing the code.

    A few questions:

    1. Why the z? I remove the z in zLastRow in two places. The code still worked?

    2. I need to run a macro to activate the code. Is it possible to have it run automatically when a row is deleted?

    3. 'e.g. 53 and 'e.g. "a2:a53". Are they needed, if so, what do they mean?

    Thanks for your help.

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excelnewbie,

    By placing code in the Worksheet_Change event routine, you can detect changes in the ID order or number of rows. The following code will re-order the ID numbers if a row has been added of deleted or if the ID order has been changed. The code has been written to be flexible to accommodate additional columns while having data elsewhere on the sheet. Please note that there is an additional snippet in the ThisWorkbook Module and some declarations in a standard module.

    HTH,
    Maud

    Worksheet Module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '--------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim I As Long, LastRow As Long
        Dim s(Cols) As Variant 'SET COLS NUMBER IN THE STANDARD MODULE
    '--------------------------------------------------
    'CHECK IF RANGE APPLICABLE OR CHECK IF ROW HAS BEEN ADDED OR DELETED
        If Not Intersect(Target, Range("A2:A1000")) Is Nothing Or _
            LastRow <> OldRows Then
    '--------------------------------------------------
    'FIND LAST ROW IN A DESIGNATED USED AREA
            For I = 1 To Cols
                s(I) = Cells(Rows.Count, I).End(xlUp).Row
            Next I
            LastRow = WorksheetFunction.Max(s)
    '--------------------------------------------------
    'RE-ORDER ID NUMBERS
            For I = 2 To LastRow
                Application.EnableEvents = False
                Cells(I, 1) = I - 1
                Application.EnableEvents = True
            Next I
            OldRows = LastRow
        End If
    End Sub

    ThisWorkbook Module:
    Code:
    Private Sub Workbook_Open()
    Dim I
    Dim s(Cols) As Variant
    '--------------------------------------------------
    'FIND LAST ROW IN A DESIGNATED USED AREA AND REMEMBER VALUE
        For I = 1 To Cols 'SET COLS NUMBER IN THE STANDARD MODULE
            s(I) = Worksheets("Sheet1").Cells(Rows.Count, I).End(xlUp).Row
        Next I
        OldRows = WorksheetFunction.Max(s)
    End Sub
    Standard Module:
    Code:
    Option Explicit
    Public Const Cols = 7
    Public OldRows As Long
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-16 at 04:03.

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

    Excelnewbie (2015-05-16)

  16. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi newbie

    Usually the Excel VBA editor auto-capitalizes keywords and property names for you. Keywords are reserved words that VBA uses as part of its programming language. So if you always type in lowercase
    e.g. cells(1,1).value =
    ..then, when you see it become..
    Cells(1,1).Value
    ..you know you typed it correctly.
    I avoid starting my own variables with a Capital letter, to distinguish it from the standard keywords. For example, LastRow is NOT a builtin keyword, but a new learner may think it is.

    There are some common ways of naming your variables. If you work in large teams, your organisation may require you to use a 'standard' naming convention, so that others may 'understand' your code more easily.

    1. Why the z? I remove the z in zLastRow in two places. The code still worked?
    For simplicity, I prefix the majority of my variables with a lowercase z and then use a meaningful description for the rest. As you have discovered, removing the z from the variable name doesn't make a difference in the code.

    Generally, you can use whatever you like for your variable names. For example, you could have:
    Dim baseballCards As New Collection, Dim rope As String, Dim Sum
    (actually, best not to use that last one, I was just a bit hungry)

    For Each dog in ThisWorkbook.WorkSheets
    dog.Select
    If dog.Name Like "Biscuits*" Then
    xxxx
    etc etc

    Within a Procedure, while writing code you can add comments to explain the purpose and what the code is doing. To differentiate your comments with the code, you precede the comments with a single apostrophe character ' . Comment text will be ignored by VBA.

    2. I need to run a macro to activate the code. Is it possible to have it run automatically when a row is deleted?
    Yes. Maud shows how to do this in post#12

    3. 'e.g. 53 and 'e.g. "a2:a53". Are they needed, if so, what do they mean?
    They are respectively just used to illustrate an example of what the last row may be, and, if so, what the value of the temp variable would then be.

    zLastRow = [a1].CurrentRegion.Rows.Count
    With your headers in row 1, the current region will extend to the last row of 'joined' data e.g. even if column [A] has some empty cells at the bottom, if other columns have data, then these records will be included in the region. So, as an example, this may give the last row as 53. Using this example again further down, we have a "a2:a" & zLastRow, so this would give a result of "a2:a53" for the variable named temp. So later on in the code, Destination:=Range(temp) is like Destination:=Range("a2:a53").

    zeddy
    NOTE: If your keyword doesn't capitalize, for example you see Cells(1,1).value , then you have used value as a variable name, or used value as a module name, or used value as a function name or as a sub procedure name e.g. sub value(). That can be very confusing. So don't do it.
    Last edited by zeddy; 2015-05-16 at 09:46.

  17. The Following User Says Thank You to zeddy For This Useful Post:

    Excelnewbie (2015-05-16)

  18. #14
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud-

    Thank you for taking your time to prepare the code and provide a file for me to study and use. Very kind of you!

  19. #15
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy-

    I appreciate your help. Thanks for providing the code and answering my questions.

Posting Permissions

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