Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for names when cursor is there..
Dear expert,
I have a file containing 2 sheets Summary = Names and Marks Calculation = Contains the breakdown of Marks Say in Summary sheet. A1 to B15 are like this. Names Marks Chrain 81 Teri 77 Jim 75 Ricky 74 Trudy 74 Pat 68 James 67 Mark 63 Peggy 62 Michael 61 Fred 55 Kim 53 Ray 45 Fiona 42 Say I highlight the cell of Ricky. Then press (use Mouse) a button (will be done later in cell C1) next to Marks (cell B1). It can automatically go to search Ricky in sheet calculation. Say I highlight (cursor) the cell of Peggy, it can automatically go to search Peggy in sheet calculation. I can see the breakdown then. The fact is that .... The sequence in Summary can be changed from time to time. Secondly, names are more than 232 (I copy/paste few here). I don't want to set macro for 232 names and search individual names by clicking individual buttons next to their marks. Just want to click one button at the cell C1 and if I highlight the cell of Ray, then search in sheet calculation and go to look for Ray's breakdown of marks. That can save space and can time. Also, this can be customized for next year (different people each year) Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for names when cursor is there..
You really only need one button. ViewToolbarsControl Toolbox. Click on
the command button then place the crosshairs where you want the button on the sheet with the names and marks The button will scroll with the sheet unless you freeze the first two or three lines and put the button on the frozen pane. Then while in dexign mode, right click the butoon, then select view code from the drop down menu. Paste this code into the code window that opens: Private Sub CommandButton1_Click() Dim c As Range Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues) If Not c Is Nothing then c.Row.Select Else MsgBox "Student name not found. Check Spelling", ,"ADVISORY" End If End Sub Before you click the button, you must select the student's name that you want to review. You can only do one at a time. The names must be identical on both sheets. "Elton Law" wrote in message ... Dear expert, I have a file containing 2 sheets Summary = Names and Marks Calculation = Contains the breakdown of Marks Say in Summary sheet. A1 to B15 are like this. Names Marks Chrain 81 Teri 77 Jim 75 Ricky 74 Trudy 74 Pat 68 James 67 Mark 63 Peggy 62 Michael 61 Fred 55 Kim 53 Ray 45 Fiona 42 Say I highlight the cell of Ricky. Then press (use Mouse) a button (will be done later in cell C1) next to Marks (cell B1). It can automatically go to search Ricky in sheet calculation. Say I highlight (cursor) the cell of Peggy, it can automatically go to search Peggy in sheet calculation. I can see the breakdown then. The fact is that .... The sequence in Summary can be changed from time to time. Secondly, names are more than 232 (I copy/paste few here). I don't want to set macro for 232 names and search individual names by clicking individual buttons next to their marks. Just want to click one button at the cell C1 and if I highlight the cell of Ray, then search in sheet calculation and go to look for Ray's breakdown of marks. That can save space and can time. Also, this can be customized for next year (different people each year) Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for names when cursor is there..
Hi Friend,
Thanks for reply. I followed what you asked. When I ran, it gave a window saying compile error. Invalid qualifier. These are highlighted in blue. ..Row Do you know what is the problem? Thanks "JLGWhiz" wrote: You really only need one button. ViewToolbarsControl Toolbox. Click on the command button then place the crosshairs where you want the button on the sheet with the names and marks The button will scroll with the sheet unless you freeze the first two or three lines and put the button on the frozen pane. Then while in dexign mode, right click the butoon, then select view code from the drop down menu. Paste this code into the code window that opens: Private Sub CommandButton1_Click() Dim c As Range Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues) If Not c Is Nothing then c.Row.Select Else MsgBox "Student name not found. Check Spelling", ,"ADVISORY" End If End Sub Before you click the button, you must select the student's name that you want to review. You can only do one at a time. The names must be identical on both sheets. "Elton Law" wrote in message ... Dear expert, I have a file containing 2 sheets Summary = Names and Marks Calculation = Contains the breakdown of Marks Say in Summary sheet. A1 to B15 are like this. Names Marks Chrain 81 Teri 77 Jim 75 Ricky 74 Trudy 74 Pat 68 James 67 Mark 63 Peggy 62 Michael 61 Fred 55 Kim 53 Ray 45 Fiona 42 Say I highlight the cell of Ricky. Then press (use Mouse) a button (will be done later in cell C1) next to Marks (cell B1). It can automatically go to search Ricky in sheet calculation. Say I highlight (cursor) the cell of Peggy, it can automatically go to search Peggy in sheet calculation. I can see the breakdown then. The fact is that .... The sequence in Summary can be changed from time to time. Secondly, names are more than 232 (I copy/paste few here). I don't want to set macro for 232 names and search individual names by clicking individual buttons next to their marks. Just want to click one button at the cell C1 and if I highlight the cell of Ray, then search in sheet calculation and go to look for Ray's breakdown of marks. That can save space and can time. Also, this can be customized for next year (different people each year) Thanks, . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for names when cursor is there..
Try the modified 'JLGWhiz''s code.
Dim c As Range Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues) If Not c Is Nothing Then Application.Goto Sheets("calculation").Range("A" & c.Row) c.EntireRow.Select Else MsgBox "Student name not found. Check Spelling", , "ADVISORY" End If -- Jacob (MVP - Excel) "Elton Law" wrote: Hi Friend, Thanks for reply. I followed what you asked. When I ran, it gave a window saying compile error. Invalid qualifier. These are highlighted in blue. .Row Do you know what is the problem? Thanks "JLGWhiz" wrote: You really only need one button. ViewToolbarsControl Toolbox. Click on the command button then place the crosshairs where you want the button on the sheet with the names and marks The button will scroll with the sheet unless you freeze the first two or three lines and put the button on the frozen pane. Then while in dexign mode, right click the butoon, then select view code from the drop down menu. Paste this code into the code window that opens: Private Sub CommandButton1_Click() Dim c As Range Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues) If Not c Is Nothing then c.Row.Select Else MsgBox "Student name not found. Check Spelling", ,"ADVISORY" End If End Sub Before you click the button, you must select the student's name that you want to review. You can only do one at a time. The names must be identical on both sheets. "Elton Law" wrote in message ... Dear expert, I have a file containing 2 sheets Summary = Names and Marks Calculation = Contains the breakdown of Marks Say in Summary sheet. A1 to B15 are like this. Names Marks Chrain 81 Teri 77 Jim 75 Ricky 74 Trudy 74 Pat 68 James 67 Mark 63 Peggy 62 Michael 61 Fred 55 Kim 53 Ray 45 Fiona 42 Say I highlight the cell of Ricky. Then press (use Mouse) a button (will be done later in cell C1) next to Marks (cell B1). It can automatically go to search Ricky in sheet calculation. Say I highlight (cursor) the cell of Peggy, it can automatically go to search Peggy in sheet calculation. I can see the breakdown then. The fact is that .... The sequence in Summary can be changed from time to time. Secondly, names are more than 232 (I copy/paste few here). I don't want to set macro for 232 names and search individual names by clicking individual buttons next to their marks. Just want to click one button at the cell C1 and if I highlight the cell of Ray, then search in sheet calculation and go to look for Ray's breakdown of marks. That can save space and can time. Also, this can be customized for next year (different people each year) Thanks, . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for names when cursor is there..
Hi Jacob,
You often helped me .... Thanks indeed. It works now .... Tks always. "Jacob Skaria" wrote: Try the modified 'JLGWhiz''s code. Dim c As Range Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues) If Not c Is Nothing Then Application.Goto Sheets("calculation").Range("A" & c.Row) c.EntireRow.Select Else MsgBox "Student name not found. Check Spelling", , "ADVISORY" End If -- Jacob (MVP - Excel) "Elton Law" wrote: Hi Friend, Thanks for reply. I followed what you asked. When I ran, it gave a window saying compile error. Invalid qualifier. These are highlighted in blue. .Row Do you know what is the problem? Thanks "JLGWhiz" wrote: You really only need one button. ViewToolbarsControl Toolbox. Click on the command button then place the crosshairs where you want the button on the sheet with the names and marks The button will scroll with the sheet unless you freeze the first two or three lines and put the button on the frozen pane. Then while in dexign mode, right click the butoon, then select view code from the drop down menu. Paste this code into the code window that opens: Private Sub CommandButton1_Click() Dim c As Range Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues) If Not c Is Nothing then c.Row.Select Else MsgBox "Student name not found. Check Spelling", ,"ADVISORY" End If End Sub Before you click the button, you must select the student's name that you want to review. You can only do one at a time. The names must be identical on both sheets. "Elton Law" wrote in message ... Dear expert, I have a file containing 2 sheets Summary = Names and Marks Calculation = Contains the breakdown of Marks Say in Summary sheet. A1 to B15 are like this. Names Marks Chrain 81 Teri 77 Jim 75 Ricky 74 Trudy 74 Pat 68 James 67 Mark 63 Peggy 62 Michael 61 Fred 55 Kim 53 Ray 45 Fiona 42 Say I highlight the cell of Ricky. Then press (use Mouse) a button (will be done later in cell C1) next to Marks (cell B1). It can automatically go to search Ricky in sheet calculation. Say I highlight (cursor) the cell of Peggy, it can automatically go to search Peggy in sheet calculation. I can see the breakdown then. The fact is that .... The sequence in Summary can be changed from time to time. Secondly, names are more than 232 (I copy/paste few here). I don't want to set macro for 232 names and search individual names by clicking individual buttons next to their marks. Just want to click one button at the cell C1 and if I highlight the cell of Ray, then search in sheet calculation and go to look for Ray's breakdown of marks. That can save space and can time. Also, this can be customized for next year (different people each year) Thanks, . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My cursor always in selection mode,wat about other cursor option | Excel Discussion (Misc queries) | |||
move cursor on one sheet moves cursor on all sheets | Excel Discussion (Misc queries) | |||
Putting Cursor in Cell A1 of each Worksheet, then ending cursor on smalest sheet name according to VBA Editor | Excel Programming | |||
Cursor disappears / Cursor verschwindet | Excel Programming | |||
Can I change the "white cross" cursor in Excel to another cursor? | Excel Discussion (Misc queries) |