Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter key does different things in different circumstances
This is more of an annoyace than a true issue but i would appreciate any help.
I'm running Excel 2007. I'm using a spreadsheet to keep scores for quiz matches. Across on row 6 I have the question numbers. Immediately below that is a spot for me to enter the point value. Both of these cells span two columns (i.e. column 6 and 7 for one question, 8 and 9 for the next, etc.). Then, underneath those cells there are a total of 16 rows used for the first team, a set of separator rows and then 16 more rows for the other team. Up to 8 quizzers can be on each team but, because of how I have another macro setup, each quizzer has two rows. I've set up a macro that will move the selected cell from the current cell (in a quizzer row...either column for a particular question) to the next question's cell for the point value (row 7). However, before it moves to the next question, it will check the current question to see if it was answered (correct or incorrect). If the question was not answered at all, by either team, it will gray the columns (and appropriate rows) associated with that question. Here's the wierd issue: If the macro does have to gray the column before it moves to the next question, after I've entered the point value and pressed enter, it will go to the next row down and the first of the two question columns. If the macro does NOT have to gray the cells, when I press enter on the next question, the cursor moves to the second column of the PREVIOUS question. I'll include the macro below. I hope I've delineated the situation and problem sufficiently. Here's a for instance: Question 1 uses Columns G and H. The point value for the question is entered in row 7 (the cell listed is G7). I enter the score and then enter the appropriate points(either correct or incorrect) for the appropriate quizzer (their stats are on rows 8 through 23 and 28 through 43 for the different teams). I click Ctrl-Shift-N to run the macro. It takes the cursor to question two (cell I7). I enter the score and press enter and it takes me to H8. If I had not entered any scores for the quizzer and the macro had greyed those cells, it would correctly have taken my to I7 when I pressed enter. Here's the code: Sub nextquestion() 'grays current question if there is a no response 'moves the cursor to the point value cell for the next question. 'hot key Ctrl+Shift+N cl = ActiveCell.Column 'the first column in each question group is an odd numbered column. this sets the column to odd If cl Mod 2 = 0 Then cl = cl - 1 graycells = True For x = 1 To 2 For y = 8 To 21 If (Cells(y, cl - 1 + x).Value < "") And (Cells(y, cl - 1 + x).Value < " ") _ Then graycells = False Exit For End If Next y If graycells = False Then Exit For For y = 28 To 43 If (Cells(y, cl - 1 + x).Value < "") And (Cells(y, cl - 1 + x).Value < " ") _ Then graycells = False Exit For End If Next y If graycells = False Then Exit For Next x If graycells Then Range(Cells(8, cl), Cells(21, cl + 1)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight1 .TintAndShade = 0.5 .PatternTintAndShade = 0 End With Range(Cells(28, cl), Cells(43, cl + 1)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight1 .TintAndShade = 0.5 .PatternTintAndShade = 0 End With End If Range(Cells(7, cl + 2), Cells(7, cl + 2)).Select End Sub Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A few things | Excel Worksheet Functions | |||
######THEY DO DIFFERENT THINGS TO GET IT############# | Charts and Charting in Excel | |||
Changing a Macro for new circumstances | Excel Discussion (Misc queries) | |||
Changing a Macro for new circumstances | Excel Discussion (Misc queries) | |||
How to calculate no. of hours under different circumstances. | Excel Worksheet Functions |