Hi Rajan,
You are a champ and it's working perfectly, thnaks so much....
One more thing here:
When the previous code in the worksheet change was used, I could select the
Booking Ref from Cell "O4" and populate as per the Booking Ref on the Form,
but with the new codes you provided now I cannot, I tried other means but
couldn't get through. Could you also help me out on that based on your
codings, so that when I select the booking ref from cell "O4" the whole row
gets populated on the form as per the booking ref.
Sorry for too mucn of this.
Once again thank you for your great help
Regards
Chris
On Thu, Jan 19, 2012 at 12:20 AM, Rajan_Verma <rajanverma1...@gmail.com>wrote:
> Hi Chris.****
>
> Try this one:****
>
> See the attached .****
>
> ** **
>
> Sub GetRecord()****
>
> Dim strID As String****
>
> Dim lngRow As Long****
>
> Dim rngMyRange As Range****
>
> Dim wksSheet As Worksheet****
>
> Dim strPos As String****
>
> strPos = Application.Caller****
>
> Set wksSheet = ThisWorkbook.Worksheets("RoomReservation")****
>
> strID = Range("rngDest").Value****
>
> lngRow = 0****
>
> With wksSheet****
>
> Select Case strPos****
>
> Case "First"****
>
> Range("rngDest").Resize(18, 1).Value =
> Application.Transpose(Range("rngMyRange").Offset(1, 2).Resize(1, 18))****
>
> Case "last"****
>
> Range("rngDest").Resize(18, 1).Value =
> Application.Transpose(Range("rngMyRange").Offset(Range("rngMyRange").End(xlDown).Row
> - 6, 2).Resize(1, 18))****
>
> Case "next", "back"****
>
> Set rngMyRange = .Range(Range("rngMyRange").Offset(1, 2),
> .Range("rngMyRange").Offset(.Range("rngMyRange").End(xlDown).Row - 6, 2))*
> ***
>
> If strID <> "" Then****
>
> lngRow = WorksheetFunction.Match(strID, rngMyRange, 0)****
>
> End If****
>
> If strPos = "back" Then****
>
> lngRow = lngRow - 1****
>
> If (lngRow + 7) = Range("rngMyRange").Offset(1).Row Then**
> **
>
> MsgBox "First Record"****
>
> Exit Sub****
>
> End If****
>
> Else****
>
> lngRow = lngRow + 1****
>
> If (lngRow + 5) = .Range("A" & .Range("A" &
> .Rows.Count).End(xlUp).Row).Row Then****
>
> MsgBox "Last Record"****
>
> Exit Sub****
>
> End If****
>
> End If****
>
> Range("rngDest").Resize(18, 1).Value =
> Application.Transpose(Range("rngMyRange").Offset(lngRow, 2).Resize(1, 18))
> ****
>
> End Select****
>
> End With****
>
> End Sub****
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Christopher Kevin
> *Sent:* Jan/Wed/2012 04:14
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Previous and Next Buttons on Excel Sheet!
> Help Please****
>
> ** **
>
> Rajan,****
>
> ****
>
> Thank you very much for your help, you are a champ mate...****
>
> ****
>
> Everything is working fine except that when I click on the getprevious
> record button it shows/populates the headers from RoomReservation Sheet and
> shows the following error:****
>
> ****
>
> "Unable to get the match of the worksheetfunctiopn class"****
>
> ****
>
> The line giving the error is highlighted in yellow. Thank you for any
> furhter help.****
>
> ****
>
> ****
>
> Sub GetPreviousRecord()****
>
> Dim strID As String
> Dim lngRow As Long
> Dim rngMyRange As Range
> Dim wksSheet As Worksheet
> 'On Error Resume Next
> Set wksSheet = ThisWorkbook.Worksheets("RoomReservation")
> strID = Range("rngDest").Value
> With wksSheet
> Set rngMyRange = .Range(Range("rngMyRange").Offset(, 2),
> .Range("rngMyRange").Offset(.Range("rngMyRange").End(xlDown).Row - 7, 2))
> If Err.Number <> 0 Then
> MsgBox "Last Record"
> End If
> End With
> lngRow = WorksheetFunction.Match(strID, rngMyRange, 0)
> lngRow = lngRow - 2
> Range("rngDest").Resize(18, 1).Value =
> Application.Transpose(Range("rngMyRange").Offset(lngRow, 2).Resize(1, 18))
> End Sub****
>
> Thanks****
>
> ****
>
> Chris****
>
> On Wed, Jan 18, 2012 at 4:33 AM, Rajan_Verma <rajanverma1...@gmail.com>
> wrote:****
>
> Hi****
>
> See the attached sheet for navigation sheets****
>
> ****
>
> Rajan.****
>
> ****
>
> ****
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Christopher Kevin
> *Sent:* Jan/Mon/2012 03:47 ****
>
>
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Previous and Next Buttons on Excel Sheet!
> Help Please****
>
> ****
>
> Can you experts help me with my problem?****
>
> ****
>
> What I want is:****
>
> ****
>
> When the previous < button is clicked, I want only records up untill row 7
> to show on the Form Sheet as per the attached Room Reservation Sheet.****
>
> ****
>
> And the same should be applied to the go to last record << button. That is
> I want row 7 to be the last row and not going back beyond row 7 to row 1.*
> ***
>
> ****
>
> How can that be done as per the attached file?****
>
> ****
>
> The that I currently have is included in file.****
>
> ****
>
> ****
>
> Additional Explaination:****
>
> ****
>
> When I clcik the << and < buttons, column headers and blank rows above row
> 7 are also populated on to the Form. How can this be avoided?****
>
> ****
>
> I hope I have explained myself clearly.****
>
> ****
>
> Thank you all you experts in advance for any help that may be provided.***
> *
>
> ****
>
> Chris****
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com****
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com****
>
> ** **
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com****
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
