Re: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

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

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

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