Excel data display userform problem

[Lucas Steiner]

Hello,

I have a simple spreadsheet that gathers data from a data input userform
I've created. After all the data is input I want to be able to call another
userform that will allow me to scroll through the data line by line and
display the data in labeled text boxes. I have the userform all set up and
all the boxes named appropriately but for some reason my GetData sub is not
working. Basically if the user selects row 3 I need to have all the data
pulled from row 3 and placed in the appropriate boxes on the userform for
review. Code is below, any help greatly appreciated!

Code block

Private Sub GetData()

Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = (RowNumber.Text)

Else
ClearData
MsgBox "Illegal row number"
Exit Sub

End If

If r > 1 And r <= LastRow Then
EquipmentID.Text = Cells(r, 1)
Nomenclature.Text = Cells(r, 2)
InspType.Text = Cells(r, 3)
InspInt.Text = Cells(r, 4)
DateLastInsp.Text = FormatDateTime(Cells(r, 5), vbShortDate)
NextInspDue.Text = FormatDateTime(Cells(r, 6), vbShortDate)
DaysUntilDue.Text = Cells(r, 7)
DisableSave

ElseIf r = 1 Then
ClearData

Else
ClearData
MsgBox "Invalid row number"

End If

End Sub

Code block

Again, thank you for your input!

~Lucas

 

[Webtechie]

Hey Lucas,

Here is what I normally do. I'm not one of the Excel Gurus, but here it is
anyway.


Dim wb as workbook
Dim wks as worksheet
Dim myRNG as range
Dim mActiveRow as long

Set wb = thisworkbook
set wks = wb.sheets("data")
mActiveRow = rownumber.text

set myRng = wks.range("a"&mActiverow)

With myRNG

EquipmentID.Text = .offset(0,0).value
Nomenclature.Text = .offset(0,1).value
InspType.Text = .offset(0,2).value
InspInt.Text = .offset(0,3).value
DateLastInsp.Text = FormatDateTime(.offset(0,4).value, vbShortDate)
NextInspDue.Text = FormatDateTime(.offset(0,5).value, vbShortDate)
DaysUntilDue.Text = .offset(0,6).value

end with


Hope that helps!

Tony

 

[Patrick Molloy]

make sure that you have Tools/Options 'require variable declarations' checked
and that each code module has at the top:
OPTION EXPLICIT

Your code worked fine for me once I set a value for lastrow

 

[Lucas Steiner]

Thanks for the help, I finally got it to work last night, I deleted this
portion:

Code Block

If IsNumeric(RowNumber.Text) Then

Else
ClearData
MsgBox "Illegal row number"
Exit Sub

End If

If r > 1 And r <= LastRow Then

Code Block

So basically I had the right code, just had a little too much in there!
Here's what I ended up with that works great!

Code Block

Private Sub GetData()
Dim r As Long

r = (RowNumber.Text)

EquipmentID.Text = Cells(r, 1)
Nomenclature.Text = Cells(r, 2)
InspType.Text = Cells(r, 3)
InspInt.Text = Cells(r, 4)
DateLastInsp.Text = FormatDateTime(Cells(r, 5), vbShortDate)
NextInspDue.Text = FormatDateTime(Cells(r, 6), vbShortDate)
DaysUntilDue.Text = Cells(r, 7)
End Sub

Code Block

Hope this can help somebody else!

~Lucas