I'm working on a VBA form to populate an Excel workbook, and I have the feeling that my code could be written/operate more efficiently. In fact, it's probably messy enough to make some of you cringe, but I'm a total newb when it comes to VBA.
Too, and perhaps this needs to be its own thread, I would prefer that - if a field were left blank - that it insert nothing into the corresponding workbook cell instead of "0", but the code didn't work when I tried writing the conditional to read:
If frmJanData.txtDeath2 = "" Then
frmJanData.txtDeath2 = ""
End If
And don't let the morose verbiage concern you: I work in healthcare, so it's to populate a workbook with a list of patient IDs for those who've expired. The pt IDs are typically a 9 digit number.
Thanks in advance for any help!
Private Sub cmdSubmit_Click()
Sheets("deaths").Select
Dim txtDeath1 As Long
Dim txtDeath2 As Long
Dim txtDeath3 As Long
Dim txtDeath4 As Long
Dim txtDeath5 As Long
Dim txtDeath6 As Long
Dim txtDeath7 As Long
Dim txtDeath8 As Long
Dim txtDeath9 As Long
Dim txtDeath10 As Long
If frmJanData.txtDeath1 = "" Then
frmJanData.txtDeath1 = 0
End If
If frmJanData.txtDeath2 = "" Then
frmJanData.txtDeath2 = 0
End If
If frmJanData.txtDeath3 = "" Then
frmJanData.txtDeath3 = 0
End If
If frmJanData.txtDeath4 = "" Then
frmJanData.txtDeath4 = 0
End If
If frmJanData.txtDeath5 = "" Then
frmJanData.txtDeath5 = 0
End If
If frmJanData.txtDeath6 = "" Then
frmJanData.txtDeath6 = 0
End If
If frmJanData.txtDeath7 = "" Then
frmJanData.txtDeath7 = 0
End If
If frmJanData.txtDeath8 = "" Then
frmJanData.txtDeath8 = 0
End If
If frmJanData.txtDeath9 = "" Then
frmJanData.txtDeath9 = 0
End If
If frmJanData.txtDeath10 = "" Then
frmJanData.txtDeath10 = 0
End If
txtDeath1 = frmJanData.txtDeath1
txtDeath2 = frmJanData.txtDeath2
txtDeath3 = frmJanData.txtDeath3
txtDeath4 = frmJanData.txtDeath4
txtDeath5 = frmJanData.txtDeath5
txtDeath6 = frmJanData.txtDeath6
txtDeath7 = frmJanData.txtDeath7
txtDeath8 = frmJanData.txtDeath8
txtDeath9 = frmJanData.txtDeath9
txtDeath10 = frmJanData.txtDeath10
Range("b6").Value = txtDeath1
Range("b7").Value = txtDeath2
Range("b8").Value = txtDeath3
Range("b9").Value = txtDeath4
Range("b10").Value = txtDeath5
Range("b11").Value = txtDeath6
Range("b12").Value = txtDeath7
Range("b13").Value = txtDeath8
Range("b14").Value = txtDeath9
Range("b15").Value = txtDeath10
End Sub
Too, and perhaps this needs to be its own thread, I would prefer that - if a field were left blank - that it insert nothing into the corresponding workbook cell instead of "0", but the code didn't work when I tried writing the conditional to read:
If frmJanData.txtDeath2 = "" Then
frmJanData.txtDeath2 = ""
End If
And don't let the morose verbiage concern you: I work in healthcare, so it's to populate a workbook with a list of patient IDs for those who've expired. The pt IDs are typically a 9 digit number.
Thanks in advance for any help!
Private Sub cmdSubmit_Click()
Sheets("deaths").Select
Dim txtDeath1 As Long
Dim txtDeath2 As Long
Dim txtDeath3 As Long
Dim txtDeath4 As Long
Dim txtDeath5 As Long
Dim txtDeath6 As Long
Dim txtDeath7 As Long
Dim txtDeath8 As Long
Dim txtDeath9 As Long
Dim txtDeath10 As Long
If frmJanData.txtDeath1 = "" Then
frmJanData.txtDeath1 = 0
End If
If frmJanData.txtDeath2 = "" Then
frmJanData.txtDeath2 = 0
End If
If frmJanData.txtDeath3 = "" Then
frmJanData.txtDeath3 = 0
End If
If frmJanData.txtDeath4 = "" Then
frmJanData.txtDeath4 = 0
End If
If frmJanData.txtDeath5 = "" Then
frmJanData.txtDeath5 = 0
End If
If frmJanData.txtDeath6 = "" Then
frmJanData.txtDeath6 = 0
End If
If frmJanData.txtDeath7 = "" Then
frmJanData.txtDeath7 = 0
End If
If frmJanData.txtDeath8 = "" Then
frmJanData.txtDeath8 = 0
End If
If frmJanData.txtDeath9 = "" Then
frmJanData.txtDeath9 = 0
End If
If frmJanData.txtDeath10 = "" Then
frmJanData.txtDeath10 = 0
End If
txtDeath1 = frmJanData.txtDeath1
txtDeath2 = frmJanData.txtDeath2
txtDeath3 = frmJanData.txtDeath3
txtDeath4 = frmJanData.txtDeath4
txtDeath5 = frmJanData.txtDeath5
txtDeath6 = frmJanData.txtDeath6
txtDeath7 = frmJanData.txtDeath7
txtDeath8 = frmJanData.txtDeath8
txtDeath9 = frmJanData.txtDeath9
txtDeath10 = frmJanData.txtDeath10
Range("b6").Value = txtDeath1
Range("b7").Value = txtDeath2
Range("b8").Value = txtDeath3
Range("b9").Value = txtDeath4
Range("b10").Value = txtDeath5
Range("b11").Value = txtDeath6
Range("b12").Value = txtDeath7
Range("b13").Value = txtDeath8
Range("b14").Value = txtDeath9
Range("b15").Value = txtDeath10
End Sub