Mulitple If's in a function ?

Morning all, what  I would like to do is when a checkbox is checked, hide 
about 7 different controls. I am having success doing it with ONE, but 
failing after that.  I tried using a function and then calling it from my 
Form's On Current AND the Checkbox After Update. With the single line in 
there, like this:

Public Function MISLE_only()
If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else 
Me.cmdACTSUS_PIW.Visible = True
End Function

NO problems, works great. How can i add the other controls I want to close 
when checkbox Me.MISLE_case_only is checked?

Thanks in advance!

Steph
0
Utf
1/30/2010 2:13:01 PM
access.formscoding 7493 articles. 0 followers. Follow

10 Replies
1117 Views

Similar Articles

[PageSpeed] 4

Steph,
    A common method is to use the control's Tag property
    Give each of the controls you want to hide/unhide, a tag text value
of  "CaseOnly"
    Using the AfterUpdate event of MISLE_Case_Only...

Dim Ctl as Control
    If Me.MISLE_Case_Only = True Then
        For Each Ctl In Me.Controls
            If Ctl.Tag = "CaseOnly" Then
                Ctl.Visible = False
            End If
        Next Ctl
    ElseIf Me.MISLE_Case_Only = False Then
        For Each Ctl In Me.Controls
            If Ctl.Tag = "CaseOnly" Then
                Ctl.Visible = True
            End If
        Next Ctl
    End If

    Tested... and that should do it.
-- 
    hth
    Al Campagna
    Microsoft Access MVP 2006-2009
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

"Steph" <scott.a.backholm@uscg.mil> wrote in message 
news:2AE0886C-4037-4F1C-BE0F-E5426F11FF28@microsoft.com...
> Morning all, what  I would like to do is when a checkbox is checked, hide
> about 7 different controls. I am having success doing it with ONE, but
> failing after that.  I tried using a function and then calling it from my
> Form's On Current AND the Checkbox After Update. With the single line in
> there, like this:
>
> Public Function MISLE_only()
> If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else
> Me.cmdACTSUS_PIW.Visible = True
> End Function
>
> NO problems, works great. How can i add the other controls I want to close
> when checkbox Me.MISLE_case_only is checked?
>
> Thanks in advance!
>
> Steph 


0
Al
1/30/2010 2:39:40 PM
Try something like this:

If Me.MISLE_case_only = True Then
  Me.SomeControl1.Visible = False
  Me.SomeControl2.Visible = False
  Me.SomeControl3.Visible = False
Else
  Me.SomeCOntrol1.Visible = True
  Me.SomeControl2.Visible = True
  Me.SomeControl3.Visible = True
End If

This might be simplified by doing this...

Me.SomeControl1.Visible = Not Me.MISLE_case_only
Me.SomeControl2.Visible = Not Me.MISLE_case_only
Me.SomeControl3.Visible = Not Me.MISLE_case_only


hth
-- 
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931) 



"Steph" wrote:

> Morning all, what  I would like to do is when a checkbox is checked, hide 
> about 7 different controls. I am having success doing it with ONE, but 
> failing after that.  I tried using a function and then calling it from my 
> Form's On Current AND the Checkbox After Update. With the single line in 
> there, like this:
> 
> Public Function MISLE_only()
> If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else 
> Me.cmdACTSUS_PIW.Visible = True
> End Function
> 
> NO problems, works great. How can i add the other controls I want to close 
> when checkbox Me.MISLE_case_only is checked?
> 
> Thanks in advance!
> 
> Steph
0
Utf
1/30/2010 2:46:01 PM
Jack, you rock, thanks!  Will this work on a report as well?

Thanks again.

"Jack Leach" wrote:

> Try something like this:
> 
> If Me.MISLE_case_only = True Then
>   Me.SomeControl1.Visible = False
>   Me.SomeControl2.Visible = False
>   Me.SomeControl3.Visible = False
> Else
>   Me.SomeCOntrol1.Visible = True
>   Me.SomeControl2.Visible = True
>   Me.SomeControl3.Visible = True
> End If
> 
> This might be simplified by doing this...
> 
> Me.SomeControl1.Visible = Not Me.MISLE_case_only
> Me.SomeControl2.Visible = Not Me.MISLE_case_only
> Me.SomeControl3.Visible = Not Me.MISLE_case_only
> 
> 
> hth
> -- 
> Jack Leach
> www.tristatemachine.com
> 
> "I haven''t failed, I''ve found ten thousand ways that don''t work."  
> -Thomas Edison (1847-1931) 
> 
> 
> 
> "Steph" wrote:
> 
> > Morning all, what  I would like to do is when a checkbox is checked, hide 
> > about 7 different controls. I am having success doing it with ONE, but 
> > failing after that.  I tried using a function and then calling it from my 
> > Form's On Current AND the Checkbox After Update. With the single line in 
> > there, like this:
> > 
> > Public Function MISLE_only()
> > If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else 
> > Me.cmdACTSUS_PIW.Visible = True
> > End Function
> > 
> > NO problems, works great. How can i add the other controls I want to close 
> > when checkbox Me.MISLE_case_only is checked?
> > 
> > Thanks in advance!
> > 
> > Steph
0
Utf
1/30/2010 2:58:01 PM
that is great stuff, thanks Al!

"Al Campagna" wrote:

> Steph,
>     A common method is to use the control's Tag property
>     Give each of the controls you want to hide/unhide, a tag text value
> of  "CaseOnly"
>     Using the AfterUpdate event of MISLE_Case_Only...
> 
> Dim Ctl as Control
>     If Me.MISLE_Case_Only = True Then
>         For Each Ctl In Me.Controls
>             If Ctl.Tag = "CaseOnly" Then
>                 Ctl.Visible = False
>             End If
>         Next Ctl
>     ElseIf Me.MISLE_Case_Only = False Then
>         For Each Ctl In Me.Controls
>             If Ctl.Tag = "CaseOnly" Then
>                 Ctl.Visible = True
>             End If
>         Next Ctl
>     End If
> 
>     Tested... and that should do it.
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP 2006-2009
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> "Steph" <scott.a.backholm@uscg.mil> wrote in message 
> news:2AE0886C-4037-4F1C-BE0F-E5426F11FF28@microsoft.com...
> > Morning all, what  I would like to do is when a checkbox is checked, hide
> > about 7 different controls. I am having success doing it with ONE, but
> > failing after that.  I tried using a function and then calling it from my
> > Form's On Current AND the Checkbox After Update. With the single line in
> > there, like this:
> >
> > Public Function MISLE_only()
> > If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else
> > Me.cmdACTSUS_PIW.Visible = True
> > End Function
> >
> > NO problems, works great. How can i add the other controls I want to close
> > when checkbox Me.MISLE_case_only is checked?
> >
> > Thanks in advance!
> >
> > Steph 
> 
> 
> .
> 
0
Utf
1/30/2010 3:28:01 PM
Ok, now I am getting greedy!! How can I basically do the same thing to a 
report?  I have a report that prints right from a Form, taking only the 
information in the last record. Can I modify my reports controls the same way 
(I am guessing no), or if not, is there a way to open a different report 
based on the true/false of my MISLE_case_only checkbox?

Thanks again, having a blast!

Steph

"Steph" wrote:

> Morning all, what  I would like to do is when a checkbox is checked, hide 
> about 7 different controls. I am having success doing it with ONE, but 
> failing after that.  I tried using a function and then calling it from my 
> Form's On Current AND the Checkbox After Update. With the single line in 
> there, like this:
> 
> Public Function MISLE_only()
> If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else 
> Me.cmdACTSUS_PIW.Visible = True
> End Function
> 
> NO problems, works great. How can i add the other controls I want to close 
> when checkbox Me.MISLE_case_only is checked?
> 
> Thanks in advance!
> 
> Steph
0
Utf
1/30/2010 3:53:01 PM
Steph,
    Additional note regarding form Tags... using the Tag method, many more 
fields
can be made to obey the code just by giving them the CaseOnly Tag.
(You can select multiple text controls, and set each one's Tag in just
one Tag entry)
----------------
    Use that Report Section's OnFormat event to evaluate
Forms!frmYourFormName!MISLE_Case_Only
    If True, use the same method (and the same code for that matter) to
hide/show the report controls with CaseOnlt Tags.
-- 
    hth
    Al Campagna
    Microsoft Access MVP 2006-2009
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

"Steph" <scott.a.backholm@uscg.mil> wrote in message 
news:B190B8AB-D28F-4DD9-AE16-E42DFC0EF559@microsoft.com...
> Ok, now I am getting greedy!! How can I basically do the same thing to a
> report?  I have a report that prints right from a Form, taking only the
> information in the last record. Can I modify my reports controls the same 
> way
> (I am guessing no), or if not, is there a way to open a different report
> based on the true/false of my MISLE_case_only checkbox?
>
> Thanks again, having a blast!
>
> Steph
>
> "Steph" wrote:
>
>> Morning all, what  I would like to do is when a checkbox is checked, hide
>> about 7 different controls. I am having success doing it with ONE, but
>> failing after that.  I tried using a function and then calling it from my
>> Form's On Current AND the Checkbox After Update. With the single line in
>> there, like this:
>>
>> Public Function MISLE_only()
>> If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else
>> Me.cmdACTSUS_PIW.Visible = True
>> End Function
>>
>> NO problems, works great. How can i add the other controls I want to 
>> close
>> when checkbox Me.MISLE_case_only is checked?
>>
>> Thanks in advance!
>>
>> Steph 


0
Al
1/30/2010 5:34:54 PM
I agree about using tags... quite easier in most cases.  To be a little picky 
and make the code a bit more elegant and less to type, you could simplify...

Dim ctl As Control
For Each ctl In Me.Controls
  If ctl.Tag = "CaseOnly" Then ctl.Visible = Me.MISLE_case_only
Next ctl

-- 
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931) 



"Al Campagna" wrote:

> Steph,
>     A common method is to use the control's Tag property
>     Give each of the controls you want to hide/unhide, a tag text value
> of  "CaseOnly"
>     Using the AfterUpdate event of MISLE_Case_Only...
> 
> Dim Ctl as Control
>     If Me.MISLE_Case_Only = True Then
>         For Each Ctl In Me.Controls
>             If Ctl.Tag = "CaseOnly" Then
>                 Ctl.Visible = False
>             End If
>         Next Ctl
>     ElseIf Me.MISLE_Case_Only = False Then
>         For Each Ctl In Me.Controls
>             If Ctl.Tag = "CaseOnly" Then
>                 Ctl.Visible = True
>             End If
>         Next Ctl
>     End If
> 
>     Tested... and that should do it.
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP 2006-2009
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> "Steph" <scott.a.backholm@uscg.mil> wrote in message 
> news:2AE0886C-4037-4F1C-BE0F-E5426F11FF28@microsoft.com...
> > Morning all, what  I would like to do is when a checkbox is checked, hide
> > about 7 different controls. I am having success doing it with ONE, but
> > failing after that.  I tried using a function and then calling it from my
> > Form's On Current AND the Checkbox After Update. With the single line in
> > there, like this:
> >
> > Public Function MISLE_only()
> > If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else
> > Me.cmdACTSUS_PIW.Visible = True
> > End Function
> >
> > NO problems, works great. How can i add the other controls I want to close
> > when checkbox Me.MISLE_case_only is checked?
> >
> > Thanks in advance!
> >
> > Steph 
> 
> 
> .
> 
0
Utf
1/30/2010 6:20:01 PM
Rather...


>   If ctl.Tag = "CaseOnly" Then ctl.Visible = Me.MISLE_case_only

s/b

If ctl.Tag = "CaseOnly" Then ctl.Visible = Not Me.MISLE_case_only

-- 
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931) 



"Jack Leach" wrote:

> I agree about using tags... quite easier in most cases.  To be a little picky 
> and make the code a bit more elegant and less to type, you could simplify...
> 
> Dim ctl As Control
> For Each ctl In Me.Controls
>   If ctl.Tag = "CaseOnly" Then ctl.Visible = Me.MISLE_case_only
> Next ctl
> 
> -- 
> Jack Leach
> www.tristatemachine.com
> 
> "I haven''t failed, I''ve found ten thousand ways that don''t work."  
> -Thomas Edison (1847-1931) 
> 
> 
> 
> "Al Campagna" wrote:
> 
> > Steph,
> >     A common method is to use the control's Tag property
> >     Give each of the controls you want to hide/unhide, a tag text value
> > of  "CaseOnly"
> >     Using the AfterUpdate event of MISLE_Case_Only...
> > 
> > Dim Ctl as Control
> >     If Me.MISLE_Case_Only = True Then
> >         For Each Ctl In Me.Controls
> >             If Ctl.Tag = "CaseOnly" Then
> >                 Ctl.Visible = False
> >             End If
> >         Next Ctl
> >     ElseIf Me.MISLE_Case_Only = False Then
> >         For Each Ctl In Me.Controls
> >             If Ctl.Tag = "CaseOnly" Then
> >                 Ctl.Visible = True
> >             End If
> >         Next Ctl
> >     End If
> > 
> >     Tested... and that should do it.
> > -- 
> >     hth
> >     Al Campagna
> >     Microsoft Access MVP 2006-2009
> >     http://home.comcast.net/~cccsolutions/index.html
> > 
> >     "Find a job that you love... and you'll never work a day in your life."
> > 
> > "Steph" <scott.a.backholm@uscg.mil> wrote in message 
> > news:2AE0886C-4037-4F1C-BE0F-E5426F11FF28@microsoft.com...
> > > Morning all, what  I would like to do is when a checkbox is checked, hide
> > > about 7 different controls. I am having success doing it with ONE, but
> > > failing after that.  I tried using a function and then calling it from my
> > > Form's On Current AND the Checkbox After Update. With the single line in
> > > there, like this:
> > >
> > > Public Function MISLE_only()
> > > If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else
> > > Me.cmdACTSUS_PIW.Visible = True
> > > End Function
> > >
> > > NO problems, works great. How can i add the other controls I want to close
> > > when checkbox Me.MISLE_case_only is checked?
> > >
> > > Thanks in advance!
> > >
> > > Steph 
> > 
> > 
> > .
> > 
0
Utf
1/30/2010 6:21:01 PM
But I should also mention that Al's solution provides handling of Nulls for a 
triplestate checkbox, whereas mine would have to be wrapped with Nz() and 
have Nulls treated as False.


-- 
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931) 



"Jack Leach" wrote:

> Rather...
> 
> 
> >   If ctl.Tag = "CaseOnly" Then ctl.Visible = Me.MISLE_case_only
> 
> s/b
> 
> If ctl.Tag = "CaseOnly" Then ctl.Visible = Not Me.MISLE_case_only
> 
> -- 
> Jack Leach
> www.tristatemachine.com
> 
> "I haven''t failed, I''ve found ten thousand ways that don''t work."  
> -Thomas Edison (1847-1931) 
> 
> 
> 
> "Jack Leach" wrote:
> 
> > I agree about using tags... quite easier in most cases.  To be a little picky 
> > and make the code a bit more elegant and less to type, you could simplify...
> > 
> > Dim ctl As Control
> > For Each ctl In Me.Controls
> >   If ctl.Tag = "CaseOnly" Then ctl.Visible = Me.MISLE_case_only
> > Next ctl
> > 
> > -- 
> > Jack Leach
> > www.tristatemachine.com
> > 
> > "I haven''t failed, I''ve found ten thousand ways that don''t work."  
> > -Thomas Edison (1847-1931) 
> > 
> > 
> > 
> > "Al Campagna" wrote:
> > 
> > > Steph,
> > >     A common method is to use the control's Tag property
> > >     Give each of the controls you want to hide/unhide, a tag text value
> > > of  "CaseOnly"
> > >     Using the AfterUpdate event of MISLE_Case_Only...
> > > 
> > > Dim Ctl as Control
> > >     If Me.MISLE_Case_Only = True Then
> > >         For Each Ctl In Me.Controls
> > >             If Ctl.Tag = "CaseOnly" Then
> > >                 Ctl.Visible = False
> > >             End If
> > >         Next Ctl
> > >     ElseIf Me.MISLE_Case_Only = False Then
> > >         For Each Ctl In Me.Controls
> > >             If Ctl.Tag = "CaseOnly" Then
> > >                 Ctl.Visible = True
> > >             End If
> > >         Next Ctl
> > >     End If
> > > 
> > >     Tested... and that should do it.
> > > -- 
> > >     hth
> > >     Al Campagna
> > >     Microsoft Access MVP 2006-2009
> > >     http://home.comcast.net/~cccsolutions/index.html
> > > 
> > >     "Find a job that you love... and you'll never work a day in your life."
> > > 
> > > "Steph" <scott.a.backholm@uscg.mil> wrote in message 
> > > news:2AE0886C-4037-4F1C-BE0F-E5426F11FF28@microsoft.com...
> > > > Morning all, what  I would like to do is when a checkbox is checked, hide
> > > > about 7 different controls. I am having success doing it with ONE, but
> > > > failing after that.  I tried using a function and then calling it from my
> > > > Form's On Current AND the Checkbox After Update. With the single line in
> > > > there, like this:
> > > >
> > > > Public Function MISLE_only()
> > > > If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else
> > > > Me.cmdACTSUS_PIW.Visible = True
> > > > End Function
> > > >
> > > > NO problems, works great. How can i add the other controls I want to close
> > > > when checkbox Me.MISLE_case_only is checked?
> > > >
> > > > Thanks in advance!
> > > >
> > > > Steph 
> > > 
> > > 
> > > .
> > > 
0
Utf
1/30/2010 6:25:01 PM
Worked flawlessly!  Many, many thanks again to both of you!

"Al Campagna" wrote:

> Steph,
>     Additional note regarding form Tags... using the Tag method, many more 
> fields
> can be made to obey the code just by giving them the CaseOnly Tag.
> (You can select multiple text controls, and set each one's Tag in just
> one Tag entry)
> ----------------
>     Use that Report Section's OnFormat event to evaluate
> Forms!frmYourFormName!MISLE_Case_Only
>     If True, use the same method (and the same code for that matter) to
> hide/show the report controls with CaseOnlt Tags.
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP 2006-2009
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> "Steph" <scott.a.backholm@uscg.mil> wrote in message 
> news:B190B8AB-D28F-4DD9-AE16-E42DFC0EF559@microsoft.com...
> > Ok, now I am getting greedy!! How can I basically do the same thing to a
> > report?  I have a report that prints right from a Form, taking only the
> > information in the last record. Can I modify my reports controls the same 
> > way
> > (I am guessing no), or if not, is there a way to open a different report
> > based on the true/false of my MISLE_case_only checkbox?
> >
> > Thanks again, having a blast!
> >
> > Steph
> >
> > "Steph" wrote:
> >
> >> Morning all, what  I would like to do is when a checkbox is checked, hide
> >> about 7 different controls. I am having success doing it with ONE, but
> >> failing after that.  I tried using a function and then calling it from my
> >> Form's On Current AND the Checkbox After Update. With the single line in
> >> there, like this:
> >>
> >> Public Function MISLE_only()
> >> If Me.MISLE_case_only = True Then Me.cmdACTSUS_PIW.Visible = False Else
> >> Me.cmdACTSUS_PIW.Visible = True
> >> End Function
> >>
> >> NO problems, works great. How can i add the other controls I want to 
> >> close
> >> when checkbox Me.MISLE_case_only is checked?
> >>
> >> Thanks in advance!
> >>
> >> Steph 
> 
> 
> .
> 
0
Utf
1/30/2010 7:10:01 PM
Reply:

Similar Artilces:

function required
Hi all, I prepare shift schedule for my staff, how do I simplified the process of copy and paste by using a function. Let say I change the first day of the month to second day shift and I want the rest to follow the same patern NNOOOODDNNOOOODDNN and so on, and if I change the first day to N, I want the rest to follow the same patern. My sample: date: 1 2 3 4 5 6 7 8 9 10 11 12 ...........31 shift: d d n n d d n n .......... d = day shift n = night shift blank = off day tq. Bit tricky, as you don&...

Top x per criteria (Function)
Lets say I have 50 sheets, one for each state. I also have a sheet that has a years worth of data. So the data sheet has something like this (simplified) State Month Amount User RI Jan 5100 1234 RI Jan 5101 1235 RI Jan 5001 1244 RI Feb 5100 1234 RI Feb 5111 4343 RI Feb 5122 2343 RI Feb 5122 2343 RI Feb 4900 3234 RI Mar 5100 2344 TX Jan 5100 2345 TX Feb 8999 09...

Excel Functions and Macros
hello. is it possible to input a function into excel where if a cell does/doesnt meet a certain critera then excel automatically executes a macro? e.g. if A20 equals 22-12-05 then excel will run macro Date1. thank you for any assistance and merry christmas! -- - Rich A function called from a worksheet cell can only return a value (to that cell) neither it, nor any macros called by it can alter the Excel environment in any way (change format, change values in other cells, etc). Assuming that you want to execute a "macro" for one of these forbidden purposes, you would have to...

A Percentile IF function
Hi i was running if there was a percentile IF function or a way to run something similar to an average if function. Lets say I have a data set like below a 6 b 1 b 2 b 3 a 4 b 5 a 10 c 12 What I want to achieve is to find the percentile of all data points that are taged "a". The average if function for this data looks like =AVERAGEIF(D1:D8,"a",E1:E8) So ideally I want to be able to have a =PERCENTILEIF(D1:D8,"a",E1:E8), sorting column d is no appropriare as different filters are constantly applied to this data set. Potentially I would want to ...

isblank function in Excel VBA 2003 ???
It appears that most all 'is' functions are available in Excel VBA2003, that is via a 'WorksheetFunction.IsNumber' type of call. However 'isblank' is not available. Perhaps this is an oversight? My main work is to use 'istext' and 'isnumber', but it would be nice to use 'isblank'. TomC If you want an ISBLANK test that works the way the worksheet function does, just do this... If Len(Range("A1").Formula) = 0 Then MsgBox "Cell is blank like ISBLANK reports" Else MsgBox "Cell has a value or a formul...

looking for an Excel macro function reference library for Excel 20
Hello, I'm looking for an Excel macro function reference library for Excel 2003, I believe it was version 4.0 I had a manual with this information but seem to have misplaced it, If anybody knows of a place I can find this information I'd appreciate it. Regards My apologies. I posted in the wrong group. "Max" wrote: > Hello, I'm looking for an Excel macro function reference library for Excel > 2003, I believe it was version 4.0 > I had a manual with this information but seem to have misplaced it, If > anybody knows of a place I can find this information...

Custom Function
I am creating a custom function and want one of my arguments to be a range. For example, the sum function accepts a range argument (=Sum(range)). How does the Sum function define that range argument? Fill in the blank for me and it should help me with my problem. Function Sum(???) Sum = ??? End Function Enter as : Function zum(r As Range) As Variant Dim rr As Range zum = 0# For Each rr In r zum = zum + rr.Value Next End Function use as: =zum(B1:B6) -- Gary''s Student "SPeterson" wrote: > I am creating a custom function and want one of my arguments to be a...

How do I write a IF function formula?
I need to write a formula for the following but am confused as to how to do it. Can someone help? 1. determine gross pay in column F as follows: a. In cell F4, enter an IF function that applies the following logic and then copy it to the range F5:F10. If hours <= 40, then Rate * Hours, otherwise Rate * Hours + 0.5 * (Hours - 40) b. In cell I4, enter the IF function that applies the following logic: If (Gross Pay - Dependents * 38.46) > 0, then 20% * (Gross Pay -Dependents * 38.46), otherwise 0 What columns are used for Rate, Hours, Gross Pay and Dependents? "JulieA60"...

RE: Setting up the Goal function in MOney 2004
Once I have setup my budget and money reconizes I have x amount of extra money every month I try and use the goal fuction by setting up furture large purchases and I keep getting the error that savings goal unreachable even though I have enough money to reach goal ...

COM object wrapper not creating a function
First of all, let me state for the record that I am just a beginner when it comes to COM objects and ActiveX programming. I'm building a COM- object which is implemented in another application that I'm writing at the same time. It has been working well until now. I have a property that previously was only read, but I had to change it to be read/ write. So I hacked my way into the files, not knowing any other way. The COM project compiles without any errors, but when I create the wrapper class for the COM object in my other app, the new function is not there... Why? Some code: ...

Undo function suddenly lost ??
Hi NG. I have suddenly lost the abillty to "Undo" actions in my MicroSoft Visio Professional 2002. Are there any typical reasons ? I have checked that undo levels > 0, it's 20. -- mvh/rg. Christian ...

Function is not recognizing
hi I have two header files like "Headerfile1.h" "Headerfile2.h" , In "Headerfile2.h" i have declared "Headerfile1.h" So in my application i have declared "Headerfile2.h" and i am using some functions of "Headerfile1.h" in my application, and i could able to compile successfully but at runtime linker unable to find the function which is in "Headerfile1.h". Can anybody tell me where i am doing wrong? Regards Rasheed. Are you adding the implementation of the functions defined in the header file to your project? You wil...

ISNA and ISERROR functions
Hi. I need some samples of ISNA and ISERROR functiones. Is there a web where I can download samples of them? Thanks in advance Regards, Emece.- Try these formulas 'If the lookupvalues is not found it will return a na() error =VLOOKUP("something",Sheet2!A:B,2,0) 'returns true if value is not found and false if value is found =ISNA(VLOOKUP("something",Sheet2!A:B,2,0)) 'Handle na() errors using IF() =IF(ISNA(VLOOKUP("something",Sheet2!A:B,2,0)),"Not found","Found") Try the same with ISERROR() which will han...

Need help on IF function........
Hi guys. I have a survey which users must answer Y/N (5 questions in cell L8 to L12). - If all 5 are "N", cell L15 will show "Not tested". - If 1 question or more are "Y", cell L15 will show "tested". - If any questions are left blank, cell L15 will show "survey not completed" - Even 1 question or more are "Y", but survey is not completed, cell L15 will still show "survey not completed". How can this be achieved? Can u guys enlighten? Also, i'm a total newbie in vba and excel functions. Where would be a good place to...

Function to Assign a Value to a Different Cell
I am looking for a function (not a macro) that assigns a value to another cell. This allows me the options to a) automatically fill a cell if a condition is met and b) manually fill the cell if the condition is not met, without overwriting any function. The function would look something like the example below, where every recalculation of the spreadsheet the cell D4 is assigned the value 3 if the condition is met. B2: if(condition,Mike(3,D4)) Cheers, Mike. PS Obviously the example formula results in #NAME? error as Excel does not recognise my name as a valid function and can not gu...

Text Function: Store Value instead of Reference
I apologize if this is a newbie question but I can't seem to find th answer anywhere. I am trying to clean up some data and have a column that contains 1 character ID numbers. I only need the last 5 characters so I setup another column to get the using =RIGHT(B2,5) Now that I have the last 5, I don't need the original column and wan to delete it and retain my new column. I can't though since the ne column has a reference to the original. How can I store the value returned from the RIGHT function instead o the reference? Or, is there a better way to do this such as running so...

Hotmail account will not function within Outlook 2002
When I try to use my Hotmail account in Outlook 2002 I receive timeouts errors that state: There may be server or network problems or your timeout interval may be too short. also Task 'Hotmail: Folder:Inbox Synchronizing headers.' reported error (0x800CCC19) : 'A timeout occurred while communicating with the server.' My Pop3 accounts work just fine. Please help... are you using a firewall or proxy server? what about antivirus software with email scanning enabled? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Outlook Tips: http://...

Can the autofilter function run automatically when data changes?
I have a spreadsheet that pulls data in from other worksheets and has an autofilter function applied. If the information is changed on the worksheet, is there a way to have the re-filter the spreadsheet automatically? ...

questions on call dll function
Hi, all I have a MFC program and a DLL written by C language, I want to call the DLL function ,but when I try to compile the application, the compiler tells me that " C2065: 'dsfeed' : undeclared identifier".actually "dsfeed" is one function of the punch dll . Could any body give me help, thanks in advance! please see my code as below: #pragma comment(lib, "punch.lib") #include "punch.h" #include "stdafx.h" #include "test.h" #include "testDlg.h" .... ... void CTestDlg::OnButton2() { // TODO: Add your...

Help Needed!! Date Functions
i have a sheet full of different dates and i need to display if the dat is / has: Past today, or in the future just with a colour change or bold text etc. i've tried a few different ways but i've had no luck, please ca someone help? Thanks in advance -- Message posted from http://www.ExcelForum.com You could use Conditional formatting. Select your range of cells with dates. Then Format|conditional Formatting. Condition 1: Cell value is "less than" =today() (format as required) Condition 2: cell value is "equal to" =today() (format as required) Con...

Function/VB Code for finding pattern blocks in number sets?
I have several sets of numbers, e.g. : set 1 : 5, 4, 12, 15, 19, 15, 16, 19, 18, 21, 3, .... set 2 : 1, 7, 4, 9, 22, 83, 17, 61, 1, 73, 15, 16, 19, ... set 3 : 7, 93, 14, 73, 14, 25, 82, 21, 12, 17, ... and so on... and I am looking for a way to get excel to read through the numbers and find "blocks" of duplicated patterns between the different sets. For example, if I ran it through the above sets, it would point out "15, 16, 19" in set 1 and set 2 either by (prefably) highlighting the matching numbers, or just outputting them to a column/file. The sets of numbers conta...

Search function in VBA
I need help setting up a search function in VB - basically it would ru like this: The user presses the "Search" button, which brings up a dialogue bo that they input their information into. They press "Ok" and the searc macro finds the info they need. Any help would be appreciated! Thanks -- Message posted from http://www.ExcelForum.com Hi and why not use the build-in dialog? -- Regards Frank Kabel Frankfurt, Germany > I need help setting up a search function in VB - basically it would > run like this: > The user presses the "Search" button, which...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Function info()
I have a workbook with 20 sheets. However, when I insert the function info("numfile") it returns 21. If I add another sheet it returns 22. I have tried it on a new workbook with a couple of sheets and it returns the correct answer so the problem seems to be specific to the original workbook. Does anyone have any suggestions? TIA, -GHB Mine returns over 30 with one workbook because I have add-ins. Don't forget it returns "Number of active worksheets in the open workbooks" -- No private emails please, for everyone's benefit keep the discussion in the newsgro...

functions in criteria
The following phrase is in the criteria of a field; Between criteriachange(1,[Begin Date]) And DateAdd("d",daynumber([Begin Date]),criteriachange(1,[Begin Date])) The functions are as follows; Function daynumber(dtedate As Date) As Integer Select Case DatePart("m", dtedate) Case 1 daynumber = 30 Case 2 daynumber = 27 Case 3 daynumber = 30 Case 4 daynumber = 29 Case 5 daynumber = 30 Case 6 daynumber = 29 Case 7 daynumber = 30 Case 8 daynumber = 30 Case 9 ...