Forum Discussion

chahineatallah's avatar
chahineatallah
Copper Contributor
Nov 03, 2024

Excel forms and VBA

Hello

 

Was trying to do a button, which will open excel form (feature in excel for data entry and tables)

excel form

So basically when i run the macro , which opens shows this form, the form is not workable, i cant enter any data ,this is how the form looks (below image)

 

showform

 

MY vba code for the form is this , what is wrong with that? is this form not designed to work with vba and automation

 

vba code

 

and this is the investment table am trying to fill

 

 

Thanks for any suggestions, if anyone encountered such issue

developer
excel
Macros and VBA
office 365

2 Replies

  • chahineatallah's avatar
    chahineatallah
    Copper Contributor
    Nov 07, 2024

    Thanks Nikolino, but i wanted to use the builtin form in excel, which i have when using excel tables, 

     

    Thanks for the code provided

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 03, 2024

    chahineatallah 

    Below is a sample VBA code for the UserForm with basic data entry functionality.

    Vba Code is untested backup your file first

    ' Code for the Submit Button on the UserForm
Private Sub btnSubmit_Click()
    Dim ws As Worksheet
    Dim emptyRow As Long

    ' Define the worksheet where data will be saved
    Set ws = ThisWorkbook.Sheets("InvestmentTable")

    ' Find the next empty row
    emptyRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ' Transfer data from the form to the worksheet
    ws.Cells(emptyRow, 1).Value = Me.TextBox1.Value  ' Example: Investment Amount
    ws.Cells(emptyRow, 2).Value = Me.TextBox2.Value  ' Example: Investment Date
    ' Add additional fields as necessary

    ' Clear form fields after submission
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    MsgBox "Data has been successfully entered.", vbInformation
End Sub

' Code to close the form
Private Sub btnClose_Click()
    Unload Me
End Sub

     Display the UserForm through a Macro Button:

    Create a macro to open the UserForm, which you can assign to a button on your worksheet.

    Sub ShowInvestmentForm()
    InvestmentForm.Show
End Sub

     

    This code is intended to serve as a starting point and should be adapted by you to suit your requirements.

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources