|
User form Yeah!!!!
Well , thanks for your help with my user form it works great w/one exception.
It only works (the First, Prev, Next and Last) buttons when it is accessed on the database worksheet. I want it to work on a blank worksheet and have the info from the database worksheet show up in the user form. I am suppling the <getdata part of the macro, I am assuming this is were I add from which page the data should be retrieved from regardless of which worksheet it is accessed. Thank you. Jennifer Private Sub GetData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else ClearData MsgBox "Illegal row number" Exit Sub End If If r 1 And r <= LastRow Then txtFrt.Text = Cells(r, 11) txtInvoice.Text = Cells(r, 2) txtDate.Text = Cells(r, 3) cboVend.Text = Cells(r, 4) cboRan.Text = Cells(r, 5) txtPallet.Text = Cells(r, 7) txtQty.Text = Cells(r, 8) txtPrice.Text = Cells(r, 10) txtRepakHrs.Text = Cells(r, 13) txtRepakQty.Text = Cells(r, 14) DisableSave ElseIf r = 1 Then ClearData Else ClearData MsgBox "Invalid row number" End If End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
User form Yeah!!!!
how can you get data from a blank worksheet? sorry, you'll need to re-phrase
the question "Jennifer" wrote: Well , thanks for your help with my user form it works great w/one exception. It only works (the First, Prev, Next and Last) buttons when it is accessed on the database worksheet. I want it to work on a blank worksheet and have the info from the database worksheet show up in the user form. I am suppling the <getdata part of the macro, I am assuming this is were I add from which page the data should be retrieved from regardless of which worksheet it is accessed. Thank you. Jennifer Private Sub GetData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else ClearData MsgBox "Illegal row number" Exit Sub End If If r 1 And r <= LastRow Then txtFrt.Text = Cells(r, 11) txtInvoice.Text = Cells(r, 2) txtDate.Text = Cells(r, 3) cboVend.Text = Cells(r, 4) cboRan.Text = Cells(r, 5) txtPallet.Text = Cells(r, 7) txtQty.Text = Cells(r, 8) txtPrice.Text = Cells(r, 10) txtRepakHrs.Text = Cells(r, 13) txtRepakQty.Text = Cells(r, 14) DisableSave ElseIf r = 1 Then ClearData Else ClearData MsgBox "Invalid row number" End If End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
User form Yeah!!!!
I guess that you mean you want a sheet other than the sheet with the dat aon
it to be active. Ok, change a bit of your code by adding a WITH / END WITH and adding a "." before the Cells(r,nn) By using WITH followed by a sheet the . means a property or method of that object, so .Cells() refers to the 'with' object while Cells without the preceding . refers to the active sheet. If r 1 And r <= LastRow Then With Worksheets("datasheet") txtFrt.Text = .Cells(r, 11) txtInvoice.Text = .Cells(r, 2) txtDate.Text = .Cells(r, 3) cboVend.Text = .Cells(r, 4) cboRan.Text = .Cells(r, 5) txtPallet.Text = .Cells(r, 7) txtQty.Text = .Cells(r, 8) txtPrice.Text = .Cells(r, 10) txtRepakHrs.Text = .Cells(r, 13) txtRepakQty.Text = .Cells(r, 14) End With DisableSave ElseIf r = 1 Then "Patrick Molloy" wrote: how can you get data from a blank worksheet? sorry, you'll need to re-phrase the question "Jennifer" wrote: Well , thanks for your help with my user form it works great w/one exception. It only works (the First, Prev, Next and Last) buttons when it is accessed on the database worksheet. I want it to work on a blank worksheet and have the info from the database worksheet show up in the user form. I am suppling the <getdata part of the macro, I am assuming this is were I add from which page the data should be retrieved from regardless of which worksheet it is accessed. Thank you. Jennifer Private Sub GetData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else ClearData MsgBox "Illegal row number" Exit Sub End If If r 1 And r <= LastRow Then txtFrt.Text = Cells(r, 11) txtInvoice.Text = Cells(r, 2) txtDate.Text = Cells(r, 3) cboVend.Text = Cells(r, 4) cboRan.Text = Cells(r, 5) txtPallet.Text = Cells(r, 7) txtQty.Text = Cells(r, 8) txtPrice.Text = Cells(r, 10) txtRepakHrs.Text = Cells(r, 13) txtRepakQty.Text = Cells(r, 14) DisableSave ElseIf r = 1 Then ClearData Else ClearData MsgBox "Invalid row number" End If End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
User form Yeah!!!!
Ok, that is very clear and something I can use later. Now I get it! Thanks
again. Regards, Jennifer "Patrick Molloy" wrote: I guess that you mean you want a sheet other than the sheet with the dat aon it to be active. Ok, change a bit of your code by adding a WITH / END WITH and adding a "." before the Cells(r,nn) By using WITH followed by a sheet the . means a property or method of that object, so .Cells() refers to the 'with' object while Cells without the preceding . refers to the active sheet. If r 1 And r <= LastRow Then With Worksheets("datasheet") txtFrt.Text = .Cells(r, 11) txtInvoice.Text = .Cells(r, 2) txtDate.Text = .Cells(r, 3) cboVend.Text = .Cells(r, 4) cboRan.Text = .Cells(r, 5) txtPallet.Text = .Cells(r, 7) txtQty.Text = .Cells(r, 8) txtPrice.Text = .Cells(r, 10) txtRepakHrs.Text = .Cells(r, 13) txtRepakQty.Text = .Cells(r, 14) End With DisableSave ElseIf r = 1 Then "Patrick Molloy" wrote: how can you get data from a blank worksheet? sorry, you'll need to re-phrase the question "Jennifer" wrote: Well , thanks for your help with my user form it works great w/one exception. It only works (the First, Prev, Next and Last) buttons when it is accessed on the database worksheet. I want it to work on a blank worksheet and have the info from the database worksheet show up in the user form. I am suppling the <getdata part of the macro, I am assuming this is were I add from which page the data should be retrieved from regardless of which worksheet it is accessed. Thank you. Jennifer Private Sub GetData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else ClearData MsgBox "Illegal row number" Exit Sub End If If r 1 And r <= LastRow Then txtFrt.Text = Cells(r, 11) txtInvoice.Text = Cells(r, 2) txtDate.Text = Cells(r, 3) cboVend.Text = Cells(r, 4) cboRan.Text = Cells(r, 5) txtPallet.Text = Cells(r, 7) txtQty.Text = Cells(r, 8) txtPrice.Text = Cells(r, 10) txtRepakHrs.Text = Cells(r, 13) txtRepakQty.Text = Cells(r, 14) DisableSave ElseIf r = 1 Then ClearData Else ClearData MsgBox "Invalid row number" End If End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
| All times are GMT +1. The time now is 06:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com