Excel Data INSERT Point Using Excel Form


I am new to Excel Macro. I have created a store ledger using excel 2013. Three buttons were created namely, the OK, the CLEAR and the CANCEL button. When a user clicks on the OK button, data is transfered from the form to Excel sheet. I need help in the following areas.

First, given the code below, I need the data to start inserting on ROW 3 CELL 1 (i.e A3) as against  the current ROW 1 CELL 1 (A1). Secondly, i need assistance to create a BUTTON (New Record) that INSERTS new Record. Thirdly, I also need help to CLEAR the form fields everytime a user clicks on the OK button not necessarily the CLEAR button. Thank you.

Private Sub siClear_Click()Call UserForm_Initialize End Sub        Private Sub siOK_Click()                  Dim emptyRow As Long      emptyRow = WorksheetFunction.CountA(Range("A:A")) +   1                         Cells(emptyRow, Value = siDate.Value                 Cells(emptyRow, 8).Value =     siArticles.Value             Cells(emptyRow, 5).Value = siQuantity.Value           Cells(emptyRow, 6).Value = siRate.Value                  Cells(emptyRow, 11).Value = siFolioIssuing.Value   Cells(emptyRow, 12).Value = siFolioReceiving.Value         Cells(emptyRow, 3).Value = siDenominationComBox.Value              Cells(emptyRow, 13).Value = siRemarks.Value                   Cells(emptyRow, 10).Value = siList_Items_ComBox.Value            End Sub                                          Private Sub UserForm_Initialize()    siDate.Value = Date            siArticles.Value = ""                    siQuantity.Value = ""               siRate.Value = ""                       siFolioIssuing.Value = ""                    siFolioReceiving.Value = ""              siRemarks.Value = ""              siDenominationComBox.Clear               With siDenominationComBox              .AddItem "Pieces"                       .AddItem "Rims"                        .AddItem "Kilograms"                        End With                                             With siList_Items_ComBox               .AddItem "Printing Paper"                .AddItem "Carbon Paper"                     .AddItem "Pen"                         .AddItem "Catridge"                 .AddItem "Correction Pen"     .AddItem "Envelope"                         End With                                             End Sub                                          Private Sub siList_Items_ComBox_Change()                    Dim actWsh As String         actWsh = siList_Items_ComBox.Text     Worksheets(actWsh).Select         End Sub



Just food for thought. You may want to try Google Sheets. They have really easy form automation.
gebobs (rep: 30) Feb 7, '18 at 9:56 am
Please don't copy/paste questions (this is what happens); kindly edit your question and remove the bullets.
don (rep: 1317) Feb 7, '18 at 12:27 pm
Add to Discussion


Selected Answer

You should really look at our tutorials on UserForms in Excel as all of this is covered there.

To clear the form, simply unload and then reload it like this (clear userform):

Unload Me


To insert data into a new row you need to get the value from the UserForm control, and how you do that depends on the control - look to our tutorial on that here: Getting Data from a UserForm

To get the next empty row, use something like this (Find the Next Blank Row with VBA Macros in Excel):

Range("A" & Rows.Count).End(xlUp).Offset(1)

This should give you all/most of the information that you need!



Thank you so much. 
Antong (rep: 2) Feb 23, '18 at 8:33 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login