Enter key functions differently after different macro behavior

I posted this last week in the general forum and didn't hear anything.  
Since, I believe, it has something to do with the macro, i thought I might 
have better luck here in the programming forum.

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 

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 

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 

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


12/9/2009 3:19:01 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 33


Similar Artilces:

Why is Actual Work updated when entering time into Work Field?
We're using MS Project 2007 and seeing that sometimes when time is entered into the Work field for a task, that task's Actual Work is updated to the same value entered in the Work field. This happens to multiple resources, but only on some of their tasks and not on others. For example, for the weeks of 1/24 and 1/31, whatever hours I schedule (enter in the "work" cell in the Resource Usage View) for a given task also appears in the "actual" cell. The tasks are setup up differently. Some are Fixed Work, some are Fixed Duration. I believe all have the e...

Help! Install Key Doesn't Work!
I have been happily using Microsoft Money Deluxe (version 17.00.1414) for over a year. I purchased a new computer several months ago and installed Money from the same install file (downloaded from Internet). I ignored the activation notice for weeks because I was too busy to fire up the old computer, but now am trying to activate it and it keeps failing. I used Belarc Advisor to retreive the install key from my old installation, but for some reason this install requires a key with a different format. The old format is xxxxx-xxxxx-xxxxx-xxxxx-xxxxx, but the new installation requir...

Emails Receiving time in Inbox Differs
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: pop Hi, <br><br>I wonder if anyone can help me with this. I realised that my incoming mails received in the in the Inbox, the time received is different from actual time received. Meaning, the timing in the inbox shows the time that I download the emails. IS there a way to resolve this? ...

Excel 2003 spreadsheet. I enter the any number and it is entered.
If I enter a number into a cell it is put in like a decimal. For example, if I enter 10 it is put in as .1 500 is entered as a 5 Thank you Mary Mary, Goto Tools>Options and on the Edit tab you should see The Fixed Decimal Places box checked. Uncheck it. -- HTH RP "maryu" <maryu@discussions.microsoft.com> wrote in message news:65C0E0FB-43F3-4F34-8A86-FBA3F02EC9B1@microsoft.com... > If I enter a number into a cell it is put in like a decimal. For example, if > I enter 10 it is put in as .1 500 is entered as a 5 > Thank you > Mary ...

Onkey vs Macro Short cut key
Is there a benefit to using the macro short cut key in macro options vs coding a key combo using Onkey? Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11350 View this thread: http://www.excelforum.com/showthread.php?threadid=385952 I don't think there is a huge difference but with the macro options approach the shortcut is almost always there. For instance it takes a macro to run to set the Onkeys but not for macro options. And to extend ...

Calendar entries with macros
My wife makes 6 types of appointments repeatedly. I figger 6 macros: add the type of appointment at the selected time change the color set the lenngth set the reminder time add description Any one have some examples of such? J ...

Filter records by date in subform by date entered in mainform.
Hello, I've spent 3 days trying to understand what I'm doing wrong.....Here is the summary of my problem. I have a form named 'frm_EfficiencyStats' and a subform within the form named 'qry_Total hours subform' (the subform is based on a query). The form contains a text box (for date) which is bound to a table 'tbl_EfficiencyStats'. I would like to be able to enter a date in the text box on my form, and display the records in the subform based on the date I entered in the text box. The subform will only pull records from my table based on the date. Here are th...

produt key
i got the cd microsoft office 2003 but when i try it ask me for a 25 digit product key could you pls help me The product key should be with the CD. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh" <hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh@discussions.microsoft.com> wrote in message news:0E585FDC-301E-4759-9074-116902486905@microsoft.com... > i got the cd microsoft office 2003 but when i try it ask me for a 25 digit > product key could you pls help me > ...

How to get two different grouped pivot tables from the same data table?
Hi, I have one data sheet that looks like this: Date Value (=80) 2008-02-01 450 .. .. .. 2009-06-03 From the data sheet (Data) I have created a pivot table grouped by months and years (Pivot 1) with the sum of value. That is no problem. But later when I create one more sheet where I want the data to be grouped by weeks (Pivot 2) the grouping changes in Pivot 1 to weeks too. Does anyone know how to get around this? How can you get for example get one pivot graph showing the sum per week and one per month in the same Excel file using grouping on...

how to enter simple list and sum
using excel 2002 with an xp sp2 system how would I enter a simple list of positive and negative numbers and then create a cell with the sum of these values? if the list would look like this 0.58 -1.22 1.79 -0.56 -0.92 the output would look something like 0.58 0.58 -1.22 -0.64 1.79 1.15 -0.56 0.59 -0.92 -0.33 John In B1 enter =A1 In B2 enter =B1+A2 Copy down to B5 Gord Dibben MS Excel MVP On Wed, 16 May 2007 06:34:13 +0300, "John" <johnj@multipl.es> wrote: >using excel 2002 with an xp sp2 system >how would I enter a simple list of positive and negative...

License/Install key- how to change....
Does anyone know how to change or delete the software key (ID)? I've installed Office with a key that was accidentally installed on another machine, so I have a duplicate, and I can't run both at the same time. I've purchased another license, but can't see how to get rid of the old key.<br> <br> I've tried to uninstall/reinstall, but that does not delete the software ID- does anyone know how to do this? Office 2008, see here: http://www.entourage.mvps.org/version/license_version.html#reset Office 2004, see here: http://www.mcgimpsey.com/macoffice/office/pid.ht...

How do I declare a function whose output is an array?
My current workaround to this problem is to declare the function as Public Function xyz(abc as Range) as Variant 'Code 'klm is an array xyz = klm End Function This works efficiently enough, but I would like to tidy up my code by eliminating all non-explicit declarations. Furthermore, can someone tell me what exactly the difference between an array and a range is? For example, if I declare the function as Public Function(abc() as Double) as Variant, how will that make a difference? Is there any advantage of one over the other? Will functions like Rows.Count or Application.Count ...

Resource Units for Tasks entered in PWA differs than tasks entered in Project 2007
Hello, I have an ePM user asking why if he enters a project task in PWA does it not check the resource max units allocation the same way as when he enters a task in Project Professional. For example: You have a project where resource A is 50% allocated. When you create a task in Project Professional for this resource, the resource allocation is set to 50%. Create a task under the same project in PWA, submit and approve the task. Open project professional and view the task information for the task entered in PWA, why is the resource allocation set to 100% instead of 50% set for th...

IF Function with Text values
Hi, I can't seem to get the IF formula to work with my spreadsheet. I have two colums one with user login the other with surname and i need to pick the surname out of the user login and i can't get the IF true or false to pick out the surnames, any help very much appriciated. Hi use something like =VLOOKUP("user_id",A1:B100,2,0) -- Regards Frank Kabel Frankfurt, Germany .. wrote: > Hi, I can't seem to get the IF formula to work with my spreadsheet. I > have two colums one with user login the other with surname and i need > to pick the surname out of the u...

Enter formula by code.
Hi All can anyone help. I cant seem to get this to work. I keep getting run time error 1004 application or object defined error. Public Sub TLIdentify() finalrow = Worksheets("INV Bookings").Range("B65536").End(xlUp).Row Worksheets("INV Bookings").Range("AV26:AV" & finalrow).FormulaR1C1 = "=IF(OR(RC[-38]>0,RC[-32]>0,RC[-26]>0,RC[-20]>0,RC[-14]>0,RC[-8]>0,RC[-2]>0),VLOOKUP(RC4,'Team Summary'!R4C3:R16C4,2,0),"")" End Sub -- Regards and Thanks for any assistance. Francis Brown...

Entering a dollar amount w/out having to enter the decimal
What is the formula to have Excel automatically enter the decimal so all I have to enter is the numbers? It's one of the options you can specify. In Excel 2007, it's: Excel Options...>Advanced>Automatically insert a decimal point Regards, Fred "Julieanne" <Julieanne@discussions.microsoft.com> wrote in message news:3AABE398-3B77-4ACD-8938-05ED7F5CC58E@microsoft.com... > What is the formula to have Excel automatically enter the decimal so all I > have to enter is the numbers? ...

I have a table of data as below. There are more Customers, and more dates' worth of data, but they won't fit in this window. For a number of our customers, we change delivery routes during the week in order to optimize our shipping capacity. Basically, I am trying to see for each delivery route, on a given day, how many of the same customers are on the same route. In other words, when one customer changes from one route to another over a date range, do other customers switch to the same routes on the same days? Cust # Rt# 8/27/2005 8/29/2005 8/30/2005 8/31/2005 a 1069 ...

How can I show different bar colors in the same chart?
I have 2 different data groups in the same month for a 3 month period. I want to show the same data groups for all three months in the same color. Example: I have an "in" total and an "out" total for Nov, Dec and January. I want to show all "ins" as blue and all "outs" as red. This would be for a bar chart. If I put the text "in" and "out" in B1 and C1 In A2:A4 I have month names : Nov, Dec and Jan In B2:B4 I have in values and in C2LC4 I have out vlaues Seelct A1:C4 and make a bar chart The Out data is red, the In data blue Can y...

How do I stop dates from being entered everytime I enter a number
Everytime I enter data in a box (ex: ages 5-9) I then press enter to go to the next cell and it automatically puts in may 9 . I have tried putting in general in formatting cells and it still does it. can someone help before I shoot this pc out the window? I am making a population pyramid with males and females and their ages. Thanks, Kicking and Screaming You can precede your entry with a single quote... '5-9 ....or format your cells as 'Text'. Hope this helps! In article <64AE5219-035D-46E3-9488-5D01F6B012FA@microsoft.com>, Kicking and Screaming <Kicking and...

Userform to be referenced by different VBA Project?
How can userform in Project_Accounting be also referrence in Dynamics_GP or vice versa? thks. Hello HFLo, Open the Dynamics Go in VBA click Tools --> Reference --> Mark SmartList as reference to your current Dynamics GP With this you can refer any Project Accounting Form under dynamics. But VBA In Dynamics dosen't support Circular Reference. for Ex: You can have reference from Dynamics to Project Accounting. But again you cannot goto Project Accounting VBA and give the Reference to Dynamics VBA (this is not supported) Hope this helps Akram. "HFLo" wrote: &...

Odd Mailbox behavior
Hi, I have a user that has mailbox rights to her boss's mailbox. In addition, she has been granted full delegation rights thru his Outlook (2k3). All emails reside on the server, until Archiving performs its cleanup. Issue: The boss has subfolders under his inbox (all reside on server, no pst minus the Archive pst). He calls and requests that his admin assist move emails from his inbox to a subfolder. The email moves, but is gone the next day. If the boss performs the move, the email does not dissappear. What has been done: - verified she has full mailbox permissions on the...

entering time #2
hello all.. wondering if there is any way to do this... I have a column formatted for time as in this format 12:00 when entering a time it is cumbersome to have to enter the hour, then a colon, then the minutes. Is it possilbe to simply enter a 4 digit number such as 1245 and have it show up as 12:45 ?? thanks so much... I've found this group to be irreplaceable... glen in orlando Enter your times without colons in column A and put this formula in column B =(A1-RIGHT(A1,2))/2400+RIGHT(A1,2)/(24*60) and format column B for "Time" in the form you want it. Tod "Glen i...

Entering data
Please help me enter data into cells that seem to be locked. In article <15a201c43e09$dbea4e10$3501280a@phx.gbl>, "Alicia Contreras" <anonymous@discussions.microsoft.com> wrote: > Please help me enter data into cells that seem to be > locked. Hi Alicia, try: Tools > Protect > Unprotect Hope this helps! ...

Install version 10 on different server from version 9
I've got some questions on GP licensing. Can we can have multiple installs of the GP back end? How is the Dynamics GP license granted? I know we have multiple client licenses, one for each desktop, but what about the server portion? Can we have multiple servers? We've got about 3 companies under a parent company who has a GP 9 license with "Software Assurance". We were sent the GP 10 install CDs and product codes. 2 of our companies are already on GP 9 running on a sql2000 server, but we won't be able to upgrade them to version 10 for another year due to some int...

Date Function in query
I have two tables. One is INVOICES, the other is "INVENTORY. Each has a DATEMODIFIED field. The dates in INVENTORY are formatted dd/mm/yyyy hh:mm:ss. Yet dates in INVOICES are formatted dd/mm/yyyy without any time stamp. The Date() function as query criteria works on the field formatted dd/mm/yyyy but does not on the other. Both fields are filled by code. Both tables have the DATEMODIFIED field formatted the same in the design grid. Why is one table saving dates with the time stamp and the other table is not? Sorry, I forgot to tell you that I am using Access 2007. ...