View Full Version : User form will not allow blank fields

07-24-2010, 11:05 PM
I am make a userform to edit existing data in a spreadsheet. I need it to be able to allow the expense tabs to be blank when necessary. This throws an error every time. I would also like it to clear the data when it is updated.

This is my first time using VBA so i am not to familiar with all the commands. If you spot any other obvious errors or have suggestions let me know.

I am using excel 2007 and it is new to me, any assistance will be greatly appreciated.

I have also attached the form giving me issues.

Simon Lloyd
07-25-2010, 12:51 AM
I don't have 2007 here but when you say the expenses tab do you mean an actual tab or a combobox......etc

As for clearing your data if that too is all the comboboxes then you can adapt this:
Using this code snippet under a command button on your userform will allow you to clear all textboxes and comboboxes on the form, you can see the structure for the controls, and how to expand them so clearing others shouldn't be a problem.
I have also included a YesNo message box to check with the user if they want to go ahead or not.

Private Sub CommandButton1_Click()
Dim cCont As Control
If MsgBox("Do you really want to cancel?", vbYesNo, "Cancel Action") = vbYes Then
For Each cCont In Me.Controls
If TypeName(cCont) = "ComboBox" Then
cCont.Value = False
End If
If TypeName(cCont) = "TextBox" Then
cCont.Value = ""
End If
Next cCont
End If
End Sub

07-25-2010, 01:59 AM
Always use Option Explicit and Dim all your variables

Try this

Private Sub cmdOkay_Click()
Dim one As Single
On Error GoTo exits
Application.Calculation = xlCalculationManual
With Sheets("Sheet1")
n = Application.Match(Me.ListBox1.Value, .Range("C:C"), 0)
one = Me.AmtOwed
two = DateValue(Me.TrPyDate.Text)
three = Me.HBId
four = DateValue(Me.exp1dt.Text)
five = DateValue(Me.exp2dt.Text)
six = Me.E1PO.Text
seven = Me.E2PO.Text

.Cells(n, 1).Value = one
.Cells(n, 2).Value = two
.Cells(n, 3).Value = three
.Cells(n, 4).Value = four
.Cells(n, 5).Value = five
.Cells(n, 6).Value = six
.Cells(n, 7).Value = seven

End With
Application.Calculation = xlCalculationAutomatic

End Sub

07-26-2010, 12:36 AM
Simon Lloyd: I do not want to blank out all the fields. I just want to be able to ALLOW some of the fields to be blank when it is updated. I do not always have expenses to add. But when i click on update it faults out to the debugger. I tried to add an if statement that allowed it to be blank but i have no idea how to end it.

mdmackillop: I added the code you suggested. It still works the same. Can you offer a suggestion as to what that does?

Simon Lloyd
07-26-2010, 12:43 AM
Please supply what you have tried, also if you save the workbook down to xl97 - 2003 many more people will be able to view it :)