| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
| How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
| help with vba user form | Excel Programming | |||
| I am looking to see if anybody has an equivalant user form to Outlooks CONTACT form | Excel Programming | |||
Linear Mode
