Hello,
I am developing a userform that is both:
1. to collect new 'risk' information from colleagues (and store the information on a particular tab in the same spreadsheet), and
2. to enable these same colleagues to be able to retrieve and/or edit the records using the same userform.
My challenge is that the VBA coding inconsistently gets the information from a varying cell reference (depending on record to retrieve). Sometimes it works, while sometimes it captures a null value (""). In effect, I need a way for the 'getdata' subroutine to consistently retrieve the information.
I have tried different methods, as shown below, with spotted success. When I retest what is retrieved, I receive inconsistent results.
I appreciate feedback on possible alternatives.
I did look at .ControlSource, but, based on the documentation that I saw, it is associated with a fixed cell reference.
I am developing a userform that is both:
1. to collect new 'risk' information from colleagues (and store the information on a particular tab in the same spreadsheet), and
2. to enable these same colleagues to be able to retrieve and/or edit the records using the same userform.
My challenge is that the VBA coding inconsistently gets the information from a varying cell reference (depending on record to retrieve). Sometimes it works, while sometimes it captures a null value (""). In effect, I need a way for the 'getdata' subroutine to consistently retrieve the information.
I have tried different methods, as shown below, with spotted success. When I retest what is retrieved, I receive inconsistent results.
Code:
UserForm3.RiskName.Text = Sheet4.Cells(nSelectedRecord + 1, 2)
With UserForm3.PrimaryRisk
.Text = Sheet4.Cells(nSelectedRecord + 1, 3).Value
End With
UserForm3.PrimaryRisk.Text = Sheet4.Cells(nSelectedRecord + 1, 3)
If UserForm3.PrimaryRisk.Text = "" Then
tTempText = Sheet4.Cells.Cells(nSelectedRecord + 1, 3).Value
UserForm3.PrimaryRisk.Text = tTempText
End If
If UserForm3.PrimaryRisk.Text = "" Then
UserForm3.PrimaryRisk.Value = Sheet4.Cells(nSelectedRecord + 1, 3).Value
UserForm3.PrimaryRisk.Value = tTempText
End If
UserForm3.PrimaryRiskText.Value = WorksheetFunction.VLookup(UserForm3.PrimaryRisk.Value, Range("RISKTABLE"), 2, False)
If Sheet4.Cells(nSelectedRecord + 1, 4) = "" Or Sheet4.Cells(nSelectedRecord + 1, 4) = 0 Then
UserForm3.SecondaryRisk.Text = ""
UserForm3.SecondaryRiskText.Value = ""
Else
With UserForm3.SecondaryRisk
.Text = Sheet4.Cells(nSelectedRecord + 1, 4).Value
End With
UserForm3.SecondaryRisk.Value = Sheet4.Cells(nSelectedRecord + 1, 4)
If UserForm3.SecondaryRisk.Text = "" Then
tTempText = Sheet4.Cells.Cells(nSelectedRecord + 1, 3).Value
UserForm3.SecondaryRisk.Text = tTempText
End If
If UserForm3.SecondaryRisk.Text = "" Then
UserForm3.SecondaryRisk.Value = Sheet4.Cells(nSelectedRecord + 1, 3).Value
End If
UserForm3.SecondaryRiskText.Value = WorksheetFunction.VLookup(UserForm3.SecondaryRisk.Value, Range("RISKTABLE"), 2, False)
End IfI appreciate feedback on possible alternatives.
I did look at .ControlSource, but, based on the documentation that I saw, it is associated with a fixed cell reference.