Totally new with VBA help please

[camper66]

Hi,

Totally new to your website and even newer to VBA.

I'm trying to create a very simple 4 field form in Excel 2003.

The code below is one I borrowed from a free template. The form allowed for 3 fields and I MacGyver_ed a fourth. Not surprisingly, it's not working very well.

Your help is greatly appreciated.

Private Sub cmdbtnCancel_Click()
    ' Clear data fields and reset the form
    Me.formField1.Value = ""
    Me.formField2.Value = ""
    Me.FormField3.Value = ""
    Me.formField4.Value = ""
    Me.formField1.SetFocus
    Unload Me
End Sub

Private Sub cmdbtnSave_Click()
Dim vNewRow As Long
Dim ws As Worksheet
    Set ws = DataTable
    ' Find the next empty row
    vNewRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ' Check for data in Field 1
    If Trim(Me.formField1.Value) = "" Then
        Me.formField1.SetFocus
        MsgBox "Please enter data in Field 1!"
        Exit Sub
    End If
    ' Check for data in Field 2
    If Trim(Me.formField2.Value) = "" Then
        Me.formField2.SetFocus
        MsgBox "Please enter data in Field 2!"
        Exit Sub
    End If
    ' Check for data in Field 3
    If Trim(Me.FormField3.Value) = "" Then
        Me.FormField3.SetFocus
        MsgBox "Please enter data in Field 3!"
        Exit Sub
    End If
    ' Check for data in Field 4
    If Trim(Me.formField4.Value) = "" Then
        Me.formField2.SetFocus
        MsgBox "Please enter data in Field 4!"
        Exit Sub
    End If
    ' Input the data in the Data Table
    ws.Cells(vNewRow, 1).Value = Me.formField1.Value
    ws.Cells(vNewRow, 2).Value = Me.formField2.Value
    ws.Cells(vNewRow, 3).Value = Me.FormField3.Value
    ws.Cells(vNewRow, 4).Value = Me.FormField3.Value
    ws.Cells(vNewRow, 1).Activate
    ' Clear all fields and reset the form
    Me.formField1.Value = ""
    Me.formField2.Value = ""
    Me.FormField3.Value = ""
    Me.formField4.Value = ""
    Me.formField1.SetFocus
End Sub

Private Sub Label1_Click()

End Sub

Private Sub Label3_Click()

End Sub

Private Sub UserForm_Click()

End Sub

[GTO]

Hi there,

You may wish to attach the workbook. At least to my eyes, I don't see any glaring errors. I would note that under the cancel button, clearing the values is unnecessary, as you are unloading the form (which means it no longer is in memory).

Mark

[gmayor]

There are three issues and as Mark has suggested the clearing of the values with the cancel button is superfluous.
1. the ws value 'DataTable' is not defined so we don't know what that refers to
2. The line ws.Cells(vNewRow, 4).Value = Me.FormField3.Value should refer to Me.FormField4.Value and you should have a field in the form called FormField4 to provide the value
3. The lines If Trim(Me.formField4.Value) = "" Then
Me.formField2.SetFocus should read
If Trim(Me.formField4.Value) = "" Then
Me.formField4.SetFocus

Option Explicit

Private Sub cmdbtnCancel_Click()
    Unload Me
End Sub
Private Sub cmdbtnSave_Click()
Dim vNewRow As Long
Dim ws As Worksheet
    Set ws = DataTable
    ' Find the next empty row
    vNewRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ' Check for data in Field 1
    If Trim(Me.FormField1.Value) = "" Then
        Me.FormField1.SetFocus
        MsgBox "Please enter data in Field 1!"
        Exit Sub
    End If
    ' Check for data in Field 2
    If Trim(Me.FormField2.Value) = "" Then
        Me.FormField2.SetFocus
        MsgBox "Please enter data in Field 2!"
        Exit Sub
    End If
    ' Check for data in Field 3
    If Trim(Me.FormField3.Value) = "" Then
        Me.FormField3.SetFocus
        MsgBox "Please enter data in Field 3!"
        Exit Sub
    End If
    ' Check for data in Field 4
    If Trim(Me.FormField4.Value) = "" Then
        Me.FormField4.SetFocus
        MsgBox "Please enter data in Field 4!"
        Exit Sub
    End If
    ' Input the data in the Data Table
    ws.Cells(vNewRow, 1).Value = Me.FormField1.Value
    ws.Cells(vNewRow, 2).Value = Me.FormField2.Value
    ws.Cells(vNewRow, 3).Value = Me.FormField3.Value
    ws.Cells(vNewRow, 4).Value = Me.FormField4.Value
    ws.Cells(vNewRow, 1).Activate
    ' Clear all fields and reset the form
    Me.FormField1.Value = ""
    Me.FormField2.Value = ""
    Me.FormField3.Value = ""
    Me.FormField4.Value = ""
    Me.FormField1.SetFocus
End Sub

[SamT]

First, I would add this line to the top of the code page

Option Explicit

Then I would add edited versions of this sub for each of the Formfields 2 thru 4 for data verification in "Real Time."

Private Sub formField1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Trim(Me.formField1.Value) = "" Then
        Cancel = True
        Me.formField1.SetFocus
        MsgBox "Please enter data in Field 1!"
    End If
End Sub

In the Command Button Click sub, you failed to properly set the ws variable as it is here in that sub (with out all the data verification)

Private Sub cmdbtnSave_Click()
    Dim vNewRow As Long
    Dim ws As Worksheet
    Set ws = Sheets("DataTable")  '<--------
     ' Find the next empty row
    vNewRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
     
  ' Input the data in the Data Table
    ws.Cells(vNewRow, 1).Value = Me.formField1.Value
    ws.Cells(vNewRow, 2).Value = Me.formField2.Value
    ws.Cells(vNewRow, 3).Value = Me.FormField3.Value
    ws.Cells(vNewRow, 4).Value = Me.FormField3.Value
    ws.Cells(vNewRow, 1).Activate
     
     ' Clear all fields and reset the form
    Me.formField1.Value = ""
    Me.formField2.Value = ""
    Me.FormField3.Value = ""
    Me.formField4.Value = ""
    Me.formField1.SetFocus
End Sub

[camper66]

Gentlemen, thank you all for your generous help. Again, because I have ZERO coding experience, could I simply copy MR. GM's code response and post it into the work sheet ?


PS: I'm not one of those Canadians who just voted the new idiot in.....

[gmayor]

The code goes in the Userform in place of similarly named subs already present there, and then the workbook containing the userform must be saved as macro enabled.
If you attach the workbook with the userform to the forum (go advanced) then we can check it out. I think you'll have to zip the file to attach it, as it is macro enabled.

[camper66]

Thank you, I have attached the file

[Paul_Hossler]

PS: I'm not one of those Canadians who just voted the new idiot in.....

Ha, I can 'Trump' that

[SamT]

Careful, Paul, or "YOURE FIRED!"

[camper66]

Ha, I can 'Trump' that

I'll see you 12 carbon credits and raise you 1 privilege.

[SamT]

I don't see a problem. It worked fine on my Windows XP Excel XP machine. So, here is what I did.

In your UserForm, I renamed all the TextBoxes following accepted Practice. Look at the Form and The new TextBox Names, paying attention to the Names and the associated Label Captions.

In the UserForm Code, I set the TabOrder of the Controls so that the Tab key and the Enter key move the focus to the next Control in line. I added some Constants that are used in the section that put the values in the Data Table. These Constants both make it easy to change the code if for any reason you modify the Sheet, but they also make the code self checking to the human eye., since the subjects of the Constant Names and the Control Names are identical.

At first, just read the code without making any attempt to understand what it is doing.

Then I copied your UserForm and added a button the the Worksheet to run it. In this Form, I completely rewrote your code in an advanced manner as if it had many more than a few controls on it. Each function or task is assigned to a separate procedure, which makes trouble shooting and management of the code much easier. I also used a bit of Error Handling by using Boolean Type Functions that return a False if an Error occurs in them.

Finally, I added a neat little fillip to the code. Run my form and leave some inputs blank.

I hope you enjoy.

[camper66]

Sam, thank you very very much.

You not only gave me a working form, a tutorial but a primer in VB..

You're a gentleman...and in respect, I'll continue to pronounce it, Missoura