Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data display userform problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data display userform problem
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 "Lucas Steiner" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data display userform problem
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 "Webtechie" wrote: 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 "Lucas Steiner" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data display userform problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help !, problem updating data through userform ! | Excel Programming | |||
display status bar value in excel userform? | Excel Discussion (Misc queries) | |||
Data Entry and Display problem | Excel Programming | |||
Listbox data display problem | Excel Programming | |||
Hide excel, display userform only | Excel Programming |