Error when calculating blank userform textboxes

[bademployee]

Hi all,

I'm using the following, but get a run-time error if any of the kg# user form textboxes are blank:

Private Sub convert1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Blends")
If fert_rate.Value = "" Then Exit Sub
ws.Range("h22").Value = fert_rate
ws.Range("r9").Value = kg1.Value / fert_rate.Value * 100
ws.Range("r10").Value = kg2.Value / fert_rate.Value * 100
ws.Range("r11").Value = kg3.Value / fert_rate.Value * 100
ws.Range("r12").Value = kg4.Value / fert_rate.Value * 100
ws.Range("r13").Value = kg5.Value / fert_rate.Value * 100
ws.Range("r14").Value = kg6.Value / fert_rate.Value * 100
ws.Range("r15").Value = kg7.Value / fert_rate.Value * 100
Unload Me
End Sub

How do I get around this?

Thanks in advance

Mark

 

[Sam_ExcelVault]

You could apply default values of 0 to each of your kg# text boxes. Select a text box on your userform and find the 'Text' field in the properties window (keyboard shortcut F4 if it's not visible). Set its value to 0. Doing this will mean those text boxes display a 0 when the form is launched - they can of course be overwritten by the user as well.

Another alternative would be to write some validation code to check all elements on your form have been filled out correctly before running calculations and setting worksheets values but this is significantly more work than the above (although probably more robust).

 

[bademployee]

Added some If & Then statements, seems to have done the trick:

Private Sub convert1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Blends")


If IsNumeric(fert_rate) = False Then
MsgBox "Please add fertiliser rate"
Exit Sub
Else
ws.Range("h22").Value = fert_rate
End If


If Disc.kg1.Value = "" Then
Else
ws.Range("r9").Value = Disc.kg1.Value / fert_rate.Value * 100
End If


If Disc.kg2.Value = "" Then
Else
ws.Range("r10").Value = Disc.kg2.Value / fert_rate.Value * 100
End If


If Disc.kg3.Value = "" Then
Else
ws.Range("r11").Value = Disc.kg3.Value / fert_rate.Value * 100
End If


If Disc.kg4.Value = "" Then
Else
ws.Range("r12").Value = Disc.kg4.Value / fert_rate.Value * 100
End If


If Disc.kg5.Value = "" Then
Else
ws.Range("r13").Value = Disc.kg5.Value / fert_rate.Value * 100
End If


If Disc.kg6.Value = "" Then
Else
ws.Range("r14").Value = Disc.kg6.Value / fert_rate.Value * 100
End If


If Disc.kg7.Value = "" Then
Else
ws.Range("r15").Value = Disc.kg7.Value / fert_rate.Value * 100
End If


Unload Me
End Sub

Thanks