PDA

Click to See Complete Forum and Search --> : [vb] - Make this shorter?


immoral giant
Jan 26th, 2004, 10:01 AM
Can i make this shorter?

Private Sub OK_Click()
Sheets("Details").Activate
If IsEmpty(Range("B2")) Then
Row = 2
Else
Row = Range("B1").End(xlDown).Row + 1
End If
Cells(Row, 1) = txtFor.Text

If IsEmpty(Range("C2")) Then
Row = 2
Else
Row = Range("C1").End(xlDown).Row + 1
End If
Cells(Row, 2) = txtSur.Text

If IsEmpty(Range("D2")) Then
Row = 2
Else
Row = Range("D1").End(xlDown).Row + 1
End If
Cells(Row, 3) = txtAge.Value

If IsEmpty(Range("E2")) Then
Row = 2
Else
Row = Range("E1").End(xlDown).Row + 1
End If
Cells(Row, 4) = txtMob.Value

If IsEmpty(Range("F2")) Then
Row = 2
Else
Row = Range("F1").End(xlDown).Row + 1
End If
Cells(Row, 5) = txtHom.Value


Unload Me

End Sub

'new sub

Private Sub txtAge_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii <> 8 And KeyAscii < 48 Or KeyAscii > 57 Then
KeyAscii = 0
End If

End Sub

'new sub

Private Sub txtMob_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii <> 8 And KeyAscii < 48 Or KeyAscii > 57 Then
KeyAscii = 0
End If

End Sub

'new sub

Private Sub txtHom_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii <> 8 And KeyAscii < 48 Or KeyAscii > 57 Then
KeyAscii = 0
End If

End Sub

'new sub

Private Sub txtSur_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii <> 8 And KeyAscii < 65 Or KeyAscii > 90 Then
KeyAscii = 0
End If

End Sub

'new sub

Private Sub txtFor_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii <> 8 And KeyAscii < 65 Or KeyAscii > 90 Then
KeyAscii = 0
End If

End Sub

CornedBee
Jan 26th, 2004, 10:06 AM
You can place the KeyAscii test in a separate function and just call this function.
Saves you two lines per on_key function.

You can also make the main part shorter, but at a rather heavy speed impact, and I don't think it's worth it unless it's likey that more columns get added.