PDA

View Full Version : Totally new with VBA help please



camper66
10-21-2015, 06:58 PM
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
10-21-2015, 09:50 PM
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
10-21-2015, 10:36 PM
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
10-22-2015, 12:50 AM
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
10-22-2015, 03:38 AM
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
10-22-2015, 04:01 AM
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
10-22-2015, 04:08 AM
Thank you, I have attached the file

Paul_Hossler
10-22-2015, 04:15 AM
PS: I'm not one of those Canadians who just voted the new idiot in.....


Ha, I can 'Trump' that

SamT
10-22-2015, 07:06 AM
Careful, Paul, or "YOURE FIRED!"

camper66
10-22-2015, 07:48 AM
Ha, I can 'Trump' that

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

SamT
10-22-2015, 09:45 AM
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
10-22-2015, 10:01 AM
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