Required field based on 2 conditions

I have a form that has a Close process as follow, when the user select “Won” 
or “Lost” from the combo box Reason, AwardAmount and AwardTo are required 
fields. They both need to be filled out by the user.  If other options other 
than “Won” or “Lost” are selected from the Reason combo box, the two fields 
are not required. I’m having a hard time to write the code; please help.
Here the code so far:
If Me.Closed = true then
   ifNz(Reason.won) = 0 then
	msgBox “this is a required field”, vbInformation, “missing value”
                 cancel = true
  ifNz(Reason.lost) = 0 then
               msgBox “this is a required field”, vbinformation, “missing 
value”
	cancel = true 
	ifNz(AwardTo, 0)= 0 then
	MsgBox “You must select a name from the list”, vbInformation, “Missing value”
	   Cancel = True
elseIf Nz(AwardAmount, 0)=0 then
	MsgBox “you must enter an award amount”, “vbinformation, “Missing value”
	Cancel = true
Endif
    Endif

Thank you
0
Utf
2/8/2010 5:18:01 PM
access.formscoding 7493 articles. 0 followers. Follow

8 Replies
1150 Views

Similar Articles

[PageSpeed] 11

"SylvieB" <SylvieB@discussions.microsoft.com> wrote in message 
news:0229C810-9AC9-4788-AB33-569F6843C00E@microsoft.com...
>I have a form that has a Close process as follow, when the user select 
> “Won”
> or “Lost” from the combo box Reason, AwardAmount and AwardTo are required
> fields. They both need to be filled out by the user.  If other options 
> other
> than “Won” or “Lost” are selected from the Reason combo box, the two 
> fields
> are not required. I’m having a hard time to write the code; please help.
> Here the code so far:
> If Me.Closed = true then
>   ifNz(Reason.won) = 0 then
> msgBox “this is a required field”, vbInformation, “missing value”
>                 cancel = true
>  ifNz(Reason.lost) = 0 then
>               msgBox “this is a required field”, vbinformation, “missing
> value”
> cancel = true
> ifNz(AwardTo, 0)= 0 then
> MsgBox “You must select a name from the list”, vbInformation, “Missing 
> value”
>    Cancel = True
> elseIf Nz(AwardAmount, 0)=0 then
> MsgBox “you must enter an award amount”, “vbinformation, “Missing value”
> Cancel = true
> Endif
>    Endif


There's a lot there that looks wrong, but there are things that aren't clear 
about how you have this set up.

When does the code get executed?  It looks to me as though it should be in 
the form's BeforeUpdate event.  Is that where it is?  For now, I'll assume 
it is.

Is "Closed" the name of a boolean (Yes/No) field in the form's recordsource? 
Is it the name of a checkbox control on the form, possibly bound to such a 
field?  For now, I'll assume both are correct.

Is the combo box "Reason" set up such that its values are text items such as 
"Won" and "Lost"?  I'll assume so.  Also, I think your code implies that the 
Reason combo may not be left blank.  I'm assuming that, too.

If all my assumptions are correct, your code should *probably* be something 
like the following:

'----- start of revised code -----
    Dim strMessage As String

    If Me.Closed = True Then

        Select Case Me.Reason

            Case "Won", "Lost"

                If IsNull(Me.AwardTo Then
                    Me.AwardTo.SetFocus
                    strMessage = vbCr & "You must select a name from the 
list.”
                End If

                If Nz(Me.AwardAmount, 0) = 0 Then
                    If Len(strMessage) = 0 Then Me.AwardAmount.SetFocus
                    strMessage = strMessage & vbCr & _
                        “You must enter an award amount.”,
                End If

            Case Else
                If IsNull(Me.Reason) Then
                    strMessage = vbCr & "You must select a reason."
                    Me.Reason.SetFocus
                End If

        End Select

    End If

    ' If we have any message to display, that means
    ' some required information is missing.
    If Len(strMessage) > 0 Then

        Cancel = True

        MsgBox _
            “Required information is missing:" & vbCr & strMessage, _
            vbInformation, _
            “Missing value”

    End If
'----- end of revised code -----


-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
2/8/2010 5:49:59 PM
Yes, I'm sure. Here is the error i'm getting:
compile Error
Method or data member not found

and yes, AwardTo is a control on the form

"Dirk Goldgar" wrote:

> "Sylvie Boulekfouf" wrote in message 
> news:201021011540sylvie1125@hotmail.com...
> > Yes, your assumptions are correct, the code gets executed in BeforeUpdate 
> > event; Closed is a check box control; Reason value are text and can not be 
> > blank. I'm confused with your lines of code. I'm new to VBA so please bear 
> > with me. Why are you using the Case statement and not If, Then, Else.
> 
> The same logic could be implemented using "If ... Then ... Else" or "If ... 
> Then ... ElseIf".  I used Select Case because it seemd more concise to me, 
> given that we are dealing with cases -- possible values of the Reason 
> control.
> 
> > the combo box Reason has 4 values (won, lost, not pursued, cancelled). the 
> > scenario is as follow: when a user select a reason (required field), only 
> > if "won" or "lost" is selected, he needs to fill out two text boxes: the 
> > award amount and a company name also on the same form. Any other option 
> > selected from Reason won't required the user to fill out the two text 
> > boxed mentioned above. To close the form, the user uses the Closed box 
> > control and check mark it. If no Reason is selected, the form wont' close 
> > and a message will pop up asking to select a reason. I hope it makes more 
> > sense.
> 
> That sounds like the logic my code is intended to implement.
> 
> > I used your codes and when I'm getting a compile error at
> > Me.AwardTo.SetFocus. Any ideas?
> 
> What is the error message?  If AwardTo is a control on the form and is 
> capable of receiving the focus, I don't see why you would get a compile 
> error there.
> 
> Are you sure the error was on that line and not on the next one?  I see that 
> in my post, the next line of code was wrapped by the newsreader, so that 
> what should have been on one line got broken into two.  If you didn't fix 
> that break, it would give a compile error.  So where the line in my original 
> post came across like this:
> 
> >                   strMessage = vbCr & "You must select a name from the
> > list.
> 
> ... you should change it so that it's like this:
> 
>          strMessage = vbCr & "You must select a name from the list.
> 
> (but with the indentation adjusted for consistency).
> 
> -- 
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
> 
> (please reply to the newsgroup)
> 
0
Utf
2/10/2010 8:28:02 PM
ok. My mistake i mistyped the control name. I fixed it. Now when i run the 
code, it says: "run-time error '2108': you must save the field before you 
execute teh GoToControl action, the GoToControl method, or the SetFocus 
method". 
Sorry to be a pain. Really i appreciate your patience.

"SylvieB" wrote:

> Yes, I'm sure. Here is the error i'm getting:
> compile Error
> Method or data member not found
> 
> and yes, AwardTo is a control on the form
> 
> "Dirk Goldgar" wrote:
> 
> > "Sylvie Boulekfouf" wrote in message 
> > news:201021011540sylvie1125@hotmail.com...
> > > Yes, your assumptions are correct, the code gets executed in BeforeUpdate 
> > > event; Closed is a check box control; Reason value are text and can not be 
> > > blank. I'm confused with your lines of code. I'm new to VBA so please bear 
> > > with me. Why are you using the Case statement and not If, Then, Else.
> > 
> > The same logic could be implemented using "If ... Then ... Else" or "If ... 
> > Then ... ElseIf".  I used Select Case because it seemd more concise to me, 
> > given that we are dealing with cases -- possible values of the Reason 
> > control.
> > 
> > > the combo box Reason has 4 values (won, lost, not pursued, cancelled). the 
> > > scenario is as follow: when a user select a reason (required field), only 
> > > if "won" or "lost" is selected, he needs to fill out two text boxes: the 
> > > award amount and a company name also on the same form. Any other option 
> > > selected from Reason won't required the user to fill out the two text 
> > > boxed mentioned above. To close the form, the user uses the Closed box 
> > > control and check mark it. If no Reason is selected, the form wont' close 
> > > and a message will pop up asking to select a reason. I hope it makes more 
> > > sense.
> > 
> > That sounds like the logic my code is intended to implement.
> > 
> > > I used your codes and when I'm getting a compile error at
> > > Me.AwardTo.SetFocus. Any ideas?
> > 
> > What is the error message?  If AwardTo is a control on the form and is 
> > capable of receiving the focus, I don't see why you would get a compile 
> > error there.
> > 
> > Are you sure the error was on that line and not on the next one?  I see that 
> > in my post, the next line of code was wrapped by the newsreader, so that 
> > what should have been on one line got broken into two.  If you didn't fix 
> > that break, it would give a compile error.  So where the line in my original 
> > post came across like this:
> > 
> > >                   strMessage = vbCr & "You must select a name from the
> > > list.
> > 
> > ... you should change it so that it's like this:
> > 
> >          strMessage = vbCr & "You must select a name from the list.
> > 
> > (but with the indentation adjusted for consistency).
> > 
> > -- 
> > Dirk Goldgar, MS Access MVP
> > Access tips: www.datagnostics.com/tips.html
> > 
> > (please reply to the newsgroup)
> > 
0
Utf
2/10/2010 9:04:07 PM
"SylvieB" <SylvieB@discussions.microsoft.com> wrote in message 
news:A5A209D8-ADB4-4283-B3E0-CBE07CBD384D@microsoft.com...
> ok. My mistake i mistyped the control name. I fixed it. Now when i run the
> code, it says: "run-time error '2108': you must save the field before you
> execute teh GoToControl action, the GoToControl method, or the SetFocus
> method".


That sounds like you don't have this code in the BeforeUpdate event of the 
form, but maybe in the BeforeUpdate event of a *control*.  Is that the case? 
You shouldn't really put multi-field validation code in the BeforeUpdate 
event of a control.  Validation that involves the values of mutliple 
controls belongs in the BeforeUpdate event of the form itself.

If that is not the problem, please post the complete event procedure.
-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
2/10/2010 9:11:34 PM
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMessage As String

If Me.ClosedOpp = True Then
   Select Case Me.OppReason
     Case "Won", "Lost"

If IsNull(Me.AwardTo) Then
   Me.AwardTo.SetFocus
      strMessage = vbCr & "You must select a name from the list" 
End If

If Nz(Me.AwardAmount, 0) = 0 Then
  If Len(strMessage) = 0 Then Me.AwardAmount.SetFocus
      strMessage = vbCr & "You must enter an award amount"
End If

Case Else
    If IsNull(Me.OppReason) Then
         strMessage = vbCr & "You must select a reason."
           Me.OppReason.SetFocus
End If

End Select

End If

If Len(strMessage) > 0 Then

   Cancel = True

       MsgBox "enter notes" & vbCr & strMessage, vbInformation, "missing     
value"

End If
End Sub



"Dirk Goldgar" wrote:

> "SylvieB" <SylvieB@discussions.microsoft.com> wrote in message 
> news:A5A209D8-ADB4-4283-B3E0-CBE07CBD384D@microsoft.com...
> > ok. My mistake i mistyped the control name. I fixed it. Now when i run the
> > code, it says: "run-time error '2108': you must save the field before you
> > execute teh GoToControl action, the GoToControl method, or the SetFocus
> > method".
> 
> 
> That sounds like you don't have this code in the BeforeUpdate event of the 
> form, but maybe in the BeforeUpdate event of a *control*.  Is that the case? 
> You shouldn't really put multi-field validation code in the BeforeUpdate 
> event of a control.  Validation that involves the values of mutliple 
> controls belongs in the BeforeUpdate event of the form itself.
> 
> If that is not the problem, please post the complete event procedure.
> -- 
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
> 
> (please reply to the newsgroup)
> 
0
Utf
2/10/2010 9:45:01 PM
"SylvieB" <SylvieB@discussions.microsoft.com> wrote in message 
news:6B032F7D-1EEF-46D7-A7DD-7643188E702D@microsoft.com...
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> Dim strMessage As String
>
> If Me.ClosedOpp = True Then
>   Select Case Me.OppReason
>     Case "Won", "Lost"
>
> If IsNull(Me.AwardTo) Then
>   Me.AwardTo.SetFocus
>      strMessage = vbCr & "You must select a name from the list"
> End If
>
> If Nz(Me.AwardAmount, 0) = 0 Then
>  If Len(strMessage) = 0 Then Me.AwardAmount.SetFocus
>      strMessage = vbCr & "You must enter an award amount"
> End If
>
> Case Else
>    If IsNull(Me.OppReason) Then
>         strMessage = vbCr & "You must select a reason."
>           Me.OppReason.SetFocus
> End If
>
> End Select
>
> End If
>
> If Len(strMessage) > 0 Then
>
>   Cancel = True
>
>       MsgBox "enter notes" & vbCr & strMessage, vbInformation, "missing
> value"
>
> End If
> End Sub


Hmm, I may be missing something but I don't see anything wrong there.  Are 
you sure the error is occurring in this procedure?  When the error occurs, 
does the message have a Debug button?  If so, click it and see what 
statement is highlighted as the cause of the error.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
2/10/2010 10:28:54 PM
On Feb 10, 5:28=A0pm, "Dirk Goldgar"
<d...@NOdataSPAMgnostics.com.invalid> wrote:
> "SylvieB" <Sylv...@discussions.microsoft.com> wrote in message
>
> news:6B032F7D-1EEF-46D7-A7DD-7643188E702D@microsoft.com...
>
>
>
>
>
> > Private Sub Form_BeforeUpdate(Cancel As Integer)
> > Dim strMessage As String
>
> > If Me.ClosedOpp =3D True Then
> > =A0 Select Case Me.OppReason
> > =A0 =A0 Case "Won", "Lost"
>
> > If IsNull(Me.AwardTo) Then
> > =A0 Me.AwardTo.SetFocus
> > =A0 =A0 =A0strMessage =3D vbCr & "You must select a name from the list"
> > End If
>
> > If Nz(Me.AwardAmount, 0) =3D 0 Then
> > =A0If Len(strMessage) =3D 0 Then Me.AwardAmount.SetFocus
> > =A0 =A0 =A0strMessage =3D vbCr & "You must enter an award amount"
> > End If
>
> > Case Else
> > =A0 =A0If IsNull(Me.OppReason) Then
> > =A0 =A0 =A0 =A0 strMessage =3D vbCr & "You must select a reason."
> > =A0 =A0 =A0 =A0 =A0 Me.OppReason.SetFocus
> > End If
>
> > End Select
>
> > End If
>
> > If Len(strMessage) > 0 Then
>
> > =A0 Cancel =3D True
>
> > =A0 =A0 =A0 MsgBox "enter notes" & vbCr & strMessage, vbInformation, "m=
issing
> > value"
>
> > End If
> > End Sub
>
> Hmm, I may be missing something but I don't see anything wrong there. =A0=
Are
> you sure the error is occurring in this procedure? =A0When the error occu=
rs,
> does the message have a Debug button? =A0If so, click it and see what
> statement is highlighted as the cause of the error.
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips:www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)- Hide quoted text -
>
> - Show quoted text -
Hey Dirk

It's working fine now. I don't know what changed since yesterday but
THANK YOU very much for all your help and patience. One last thing if
you don't mind, when i click OK to the pop up message telling me that
i need to add for example an award amount, the message does not go
away. I click a second time and it closes the form. I'm sure that's
something simple. How can i make the message to go away without
closing the form when clicking OK? again Thank you
0
Sylvie
2/11/2010 9:22:15 PM
"Sylvie" <sylvie1125@hotmail.com> wrote in message 
news:f7c8879a-eb24-4837-ad92-6f6a2b168ca9@b2g2000yqi.googlegroups.com...
>
> It's working fine now. I don't know what changed since yesterday but THANK 
> YOU very much for all your help and patience. One last thing if you don't 
> mind, when i click OK to the pop up message telling me that i need to add 
> for example an award amount, the message does not go away. I click a 
> second time and it closes the form. I'm sure that's something simple. How 
> can i make the message to go away without closing the form when clicking 
> OK?

Hmm, that doesn't sound like "working fine" to me.  Although you posted your 
code in an earlier message, would you mind copying and pasting it once 
again, as it is now, into a new reply message?  I don't see how the code 
I've seen could give the behavior you report.

Is there any code in other form events that might account for the second 
message?

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
2/11/2010 11:17:25 PM
Reply:

Similar Artilces:

ING Direct #2
I am trying to set up an ING Direct account in Ms Money 2003 following the directions on the ING web site. After completing step 12 which is "make sure ING DIRECT is selected" in the Online setup window, click next. When I do this it prompts me for a username and password. This is not what the instructions say should happen. They say Money should connect to the internet and download information for ING DIRECT. If I try to enter my customer # and PIN as the user name and password it tells me the password must not contain any numeric characters. Upon close inspection, under the passwor...

Managed User #2
Testing to see if this setup allows me to be properly identified as a managed user. ...

Problems inserting pictures in Excel 2002 sp-2
When inserting a picture into a work sheet, nothing shows up but the outline/edit box. If you grab the box and move it on the sheet, the picture will appear. If you scroll up or down, the portion of the picture that goes off screen will not show up when you scroll back to the center of the screen. Grab and move it again and it shows up again.... HELP?????? My IT guy is lost on this one! It sounds more like a windows display problem to me. Do you have the zoom factor set to 100%? (It may help.) But maybe you could try closing other applications or changing the display settings. I use...

Potential customer field in Quote
Hi, Is there a way to make the Potential customer field in Quote entity as NOT required? Thank you in advance for any suggestions, crsb The entire sales process from opportunity up to the invoice requires a customer. If you don't need the field, create a dummy account and set the customer id to this dummy account in OnLoad. -- Michael H�hne, Microsoft Dynamics CRM MVP CRM Blog on http://www.stunnware.com ---------------------------------------------------------- "crsb" <srinivasa.bharadwaj@gmail.com> schrieb im Newsbeitrag news:76d7f1b1-c86f-470b-bbec-0329d9d29f...

need help with formula #2
how to write a formula? if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply difference by 33 add together -- Kerri Where do I find the difference "Kerri Olsen" wrote: > how to write a formula? > if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply > difference by 33 add together > -- > Kerri See Help for "IF worksheet function" I can't be more specific without a better description of the alternative calculations. Jerry "Kerri Olsen" wrote: > how to write a formula? > if cell ...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

What version of Excel converts Lotus 1-2-3 spreadsheets?
I had a 30-day trial version of Microsoft Office (which I do not know what version it was) and purchased the Student 2003 Office which no longer converts Lotus 1-2-3 spreadsheets to the Excel spreadsheet. I'd like to upgrade and do not know which one to purchase. Roberta I don't know if the S&T sku for Excel limits the files to open, but in XL2003 (part of Office 2003 Pro) there is a Lotus 1-2-3 option under File>Open...>Files of type drop-down>Lotus 1-2-3 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS www.ni...

2003 Form fields losing formatting in 2007
Hi there, I have some forms, created in Word 2003, which when opened as a .docx, all form fields revert to Times New Roman font but if they are opened as a .doc, they are in the correct font of Arial. Anyone got any ideas why and how I can fix this? Thanks very much. This doesn't make any sense. Documents always open in the format in which they are saved. What *exactly* are you doing? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com ...

Click on link problems #2
When I click on a url link in email---a box opens saying "Open Link Browser". And also a browser window. How do I get rid of the smaller link browser? Read your reply on your post of the 17th? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Robin" <robinhood2@juno.com> wrote in message news:atmnk0pgs4m272iunkegt0rvq4ssjqisig@4ax.com... > When I click on a url link in email---a box opens saying "Open Link > Browser". A...

Socket relared problem (getting free port number dynamically) #2
Hi all, i have one problem related to thread. i am having my main thread running which will on some event create a thread for validation purpose and then will start working. and now this validator thread will create a socket and it will bind to port 20111. and then will wait for client to connect. but before waiting for client to connect it will send a message to the main thread that validation is done. but in this way i need to stick with port 20111.which i really don't want. Is it possible for me to get a free port from operating system or from winsock library and then i should bind t...

graphic deleting #2
I have made myself a simple macro that copy a cell and paste it 50 cells below. It work fine with text and numbers but I have tried it with graphic recently and strangly enough it is adding a other graphic the same as the first one each time it copies. I have discover that because when I tried to correct my graphic and want to erase the others there was one more graphic to erase each cell down below, very long process and I would like to know why it is doing that and if there is a command to erase all the selected cells contents all at once like the text contents. Thank you very much Show th...

Grid Pane Field Expressions
Ordinarily, I would put field expression code in the OnFormat_Detail event section. However, if it's possible to build the expression I want directly into a query grid pane while in grid design, I would prefer to do that in the current instance. Current case: I have a field "Title" (happens to be the title of a classical music piece.) and I also have the cataloging system and values in separate fields, CatSystem and CatSystemVal, e.g., "Op.", "78". So, if the current piece has a chronological catalog value, I want the query to return it appended to the title ...

Field with running count of records
Hi, I have a query with fields such as date, Item Number and Quantity. I want to create an additional field called Count that will act like an autonumber. It will assign a value of 1 for the first record, 2 for the second number etc. Can someone tell me how to do this? Thanks, -- Chuck W There are tons of posts on how to create a "ranking" query. You must have a field or fields that uniquely identify the sort order. If you provided significant table and field names as well as your desired sort order, someone could create the SQL for you if you can't search for and fi...

Set form field on double click
I am using MS Access 2003. I have a continuous form that is run off of a query "srbScientific Review". The query's criteria is set to [Enter Last Name] in the LastName field. That part works fine. I get my continuous form that shows LastName, DocID, and Title of Document. I would like to double click on the DocID and have the following happen: 1. Open another form "publications" 2. Set the SRBId field to the DocId field that I double clicked on. PLEASE help. I think I need to bookmark, clone.... I'm very confused. As a kick in the right ...

Locate a Cell based upon criteria
I'm trying to locate a cell based upon certain criteria. For example: =CELL("row",min(C6:C11)). What doesn't seem to work is using the Min function. Any thoughts on the best way to return a cell location? Many thanks for any help you can provide! Try... =CELL("row",INDEX(C6:C11,MATCH(MIN(C6:C11),C6:C11,0))) Hope this helps! In article <C598DBDD-0D25-48E3-AA44-6B6250176D67@microsoft.com>, "Stan" <Stan@discussions.microsoft.com> wrote: > I'm trying to locate a cell based upon certain criteria. For example: > =CELL("ro...

Wait Activity Condition
We have an activity that is added to the lead record automatically at the time the lead is created. It is a phone call activity for our reps to followup with the lead. We also have a sales process (workflow) set up for once the lead is converted to an opportunity. My question is: How can I keep the lead from being converted to an opportunity until the phone call activity on the lead is completed? I have looked at the Wait Activity Condition but can't seem to get anything to work. Any suggestions appreciated. Thanks, Wendi ...

Add a number based on list of criteria ??
Example: Cell A = 50, Cell B = 96%, Cell C = Cell A + (Cell A x Cell B) = 98 I want to be able to compare the list of criteria and add a number to Cell C accordingly For example: If Cell B is between 95 and 100%, Add 1 to the total in Cell C If between 85 and 94%, add 2... etc. There are more than 7 so I know I can't simply nest If statements... Any advice on how I could fairly simply accomplish this? TIA! One way: Put your % bands in a table (say in J1:Kx, e.g.: J K 1 0 11 2 5% 10 3 15% 9 .... 10 85% 2 11 ...

Hide/Unhide #2
I have two questions regarding Excel 2007 hide features: 1. What caused my entire Excel worksheet to disappear from view? When I tried to reopen the file, Excel kept saying the file was already open, but I saw NOTHING. A colleague finally told me what he did to solve the problem, but in what situations will selecting Hide hide the entire worksheet? 2. I have a split in my worksheet and when I point to the double arrows, the pop-up message reads "Hide White Space." I have no idea what that means; how do I get rid of the split? Nothing in the Help feature assists me with my ques...

Tax withholding 2 states
We have a client that wishes to have correct state tax withholding for two states - for example, they have employees that work in IL and live in MA. The Massachusetts state income tax is 5.3% of wages. The difference between the residential rate of 5.3% and the actual Illinois state income tax needs to be withheld as an additional tax attached and remitted to Massachusetts. Thanks ...

AUTO NUMBERING #2
hi all,i have made an invoice template for my work.how can i get these invoices to sequentially number each time i use the invoice and save as iun the customers name?any suggestions will be greatly appreciated. thanks wyn. -- WYN Hi one way (using the Windows registry for storing the last number). Put the following code in the workbook module (not in a standard module) of your template: - It changes cell A1 of the first sheet - you may change the key identifiert according to your needs (e.g. DEFAULTSTART, MYLOCATION, etc.) Private Sub Workbook_Open() Const DEFAULTSTART As Int...

Conditional formatting #87
Can I do a conditional formatting on a range if the value in one cell in the range = X in other words can I format the back ground for line 5 if C5 = X Use conditional formating. Select the whole row Set the condition Formula Is =$C$5="X" Choose the Format you want for the cells in that row. -- Regards Roger Govier "ESREI" <anonymous@discussions.microsoft.com> wrote in message news:339b01c51f10$38d61860$a601280a@phx.gbl... > Can I do a conditional formatting on a range if the value > in one cell in the range = X > > in other words can I format the b...

Customization guide #2
Anyone out there want to give up a copy of the developers guide? My store is on a very tight budget, and has a hard time calling support everytime we want to see what can be done with RMS. If you want to email it anonymously, I won't say anything..... Thanks! Is there a developer's guide? Every time I called MS about development information all I get is routed to a salesperson or told we can put you in touch with "a partner" I even went thru the route of trying to become a partner in order to have more dev materials... No luck so far. "New RMS User" <N...

I can't install the 12.2.1 update on Snow Leopard
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Office 2008 12.2.1 Update can't be installed on this disk. A version of the software required to install this update was not found on this volume. I guarantee a legitimate copy of Office 2008 for Mac is on my iMac. Any suggestions? On 9/30/09 3:43 PM, in article 59b7d9fc.-1@webcrossing.caR9absDaxw, "kpainte1@officeformac.com" <kpainte1@officeformac.com> wrote: > Office 2008 12.2.1 Update can't be installed on this disk. A version of the > software required to install this update was n...

File format is not valid #2
Can anyone help me with this. I tried to open an existing file (which a number of people have access to) and it has come up with this error. Can it be fixed? Thanks heaps. Is it an xls file? "FB" wrote: > Can anyone help me with this. I tried to open an existing file (which a > number of people have access to) and it has come up with this error. > Can it be fixed? > Thanks heaps. Yes. And it worked just fine a week ago prior to someone doing something to the file. "exceluserforeman" wrote: > Is it an xls file? > > "FB" wrote: >...

How do I save a worsheet from Excel 2002, for a user with Excel 2.
I know to use save as, I just don't know which one to save as. Hi excel 2000 will read version 2002 or 2003 files without any problems, so just a normal save should do it. BTW your question got trucated in the subject line, so it's a good idea to repeat the full question in the message body. Hope this helps Cheers JulieD "vkirkman" <vkirkman@discussions.microsoft.com> wrote in message news:99074D3E-1F9D-4B17-A474-FE22A5E6C81C@microsoft.com... >I know to use save as, I just don't know which one to save as. ...