Results 1 to 2 of 2

Thread: [vb] - Make this shorter?

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2003
    Location
    England
    Posts
    3

    [vb] - Make this shorter?

    Can i make this shorter?

    VB Code:
    1. Private Sub OK_Click()
    2. Sheets("Details").Activate
    3. If IsEmpty(Range("B2")) Then
    4.     Row = 2
    5. Else
    6.     Row = Range("B1").End(xlDown).Row + 1
    7. End If
    8. Cells(Row, 1) = txtFor.Text
    9.  
    10. If IsEmpty(Range("C2")) Then
    11.     Row = 2
    12. Else
    13.     Row = Range("C1").End(xlDown).Row + 1
    14. End If
    15. Cells(Row, 2) = txtSur.Text
    16.  
    17. If IsEmpty(Range("D2")) Then
    18.     Row = 2
    19. Else
    20.     Row = Range("D1").End(xlDown).Row + 1
    21. End If
    22. Cells(Row, 3) = txtAge.Value
    23.  
    24. If IsEmpty(Range("E2")) Then
    25.     Row = 2
    26. Else
    27.     Row = Range("E1").End(xlDown).Row + 1
    28. End If
    29. Cells(Row, 4) = txtMob.Value
    30.  
    31. If IsEmpty(Range("F2")) Then
    32.     Row = 2
    33. Else
    34.     Row = Range("F1").End(xlDown).Row + 1
    35. End If
    36. Cells(Row, 5) = txtHom.Value
    37.  
    38.  
    39. Unload Me
    40.  
    41. End Sub
    42.  
    43. 'new sub
    44.  
    45. Private Sub txtAge_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    46.  
    47. If KeyAscii <> 8 And KeyAscii < 48 Or KeyAscii > 57 Then
    48. KeyAscii = 0
    49. End If
    50.  
    51. End Sub
    52.  
    53. 'new sub
    54.  
    55. Private Sub txtMob_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    56.  
    57. If KeyAscii <> 8 And KeyAscii < 48 Or KeyAscii > 57 Then
    58. KeyAscii = 0
    59. End If
    60.  
    61. End Sub
    62.  
    63. 'new sub
    64.  
    65. Private Sub txtHom_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    66.  
    67. If KeyAscii <> 8 And KeyAscii < 48 Or KeyAscii > 57 Then
    68. KeyAscii = 0
    69. End If
    70.  
    71. End Sub
    72.  
    73. 'new sub
    74.  
    75. Private Sub txtSur_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    76.  
    77. If KeyAscii <> 8 And KeyAscii < 65 Or KeyAscii > 90 Then
    78. KeyAscii = 0
    79. End If
    80.  
    81. End Sub
    82.  
    83. 'new sub
    84.  
    85. Private Sub txtFor_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    86.  
    87. If KeyAscii <> 8 And KeyAscii < 65 Or KeyAscii > 90 Then
    88. KeyAscii = 0
    89. End If
    90.  
    91. End Sub

  2. #2
    Kitten CornedBee's Avatar
    Join Date
    Aug 2001
    Location
    In a microchip!
    Posts
    11,594
    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.
    All the buzzt
    CornedBee

    "Writing specifications is like writing a novel. Writing code is like writing poetry."
    - Anonymous, published by Raymond Chen

    Don't PM me with your problems, I scan most of the forums daily. If you do PM me, I will not answer your question.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width