Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Functions Help (xl97)

    Anyone able to offer some assistance?

    In the macro below function macro below I would like a way bring up the message box, and then have the function delete the values in the two arguments so that the user then has to enter two more values.

    Function Number_to_Insure(Insured_Number1, Insured_Number2)
    Number_to_Insure = Insured_Number1 + Insured_Number2
    If Number_to_Insure > 50 Then
    MsgBox "The total number of insured exceeds 50. Please reduce the amount.", vbOKOnly
    Call Clear_Cells
    End If
    End Function

    Public Sub Clear_Cells()
    Range("D2425").Select
    ' Where D24 and D25 are the original inputs, i.e. Insured_Number1 and Insured_Number2 in the Function Number_To_Insure.
    Selection.ClearContents
    Range("D24").Select
    End Sub

    As you can see I have tried to do this by creating a separate macro that is called from within the function, which just clears the contents of the two cells. However, I would really like to be able to do this from within the function in the first place.
    To summarise, in the function two cells will be selected for the arguments (Insured_Number1 and Insured_Number2), and if the sum of these two is over 50, then clear both their contents.

    Can this be done???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Functions Help (xl97)

    If you want to use this as a worksheet function, it won't work. You cannot change the contents of other cells than the cell containing the formula in a worksheet function.

    If you want to use the function in code, you could use this, although it seems strange to me:

    Function Number_to_Insure(Insured_Number1 As Range, Insured_Number2 As Range) As Double
    Number_to_Insure = Insured_Number1 + Insured_Number2
    If Number_to_Insure > 50 Then
    MsgBox "The total number of insured exceeds 50. Please reduce the amount.", vbOKOnly
    Insured_Number1.ClearContents
    Insured_Number2.ClearContents
    End If
    End Function

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions Help (xl97)

    Thanks for you reply Hans, however this did not seem to work. The argument cells still contained their original values.
    I'm trying to set up a spreadsheet so that if the user enters two values, which when summed together are greater than 50, a box will display saying this is not valid, and the cell contents of the two cells the user has just entered will be erased.

    Any ideas???

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Functions Help (xl97)

    How are you using the function?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Functions Help (xl97)

    How about adding this to the worksheet object.?

    It checks for worksheet changes, if a change is made in D24 or D25 it determines if the sum is >50 and if so clears the cells and pops a message.

    Steve
    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("d24:d25")
    If Not Intersect(Target, rng) Is Nothing Then
    If Application.WorksheetFunction.Sum(rng) > 50 Then
    Application.EnableEvents = False
    rng.ClearContents
    Application.EnableEvents = True
    MsgBox "The total number of insured exceeds 50. " & _
    "Please reduce the amount in " & rng.Address(False, False) & "."
    End If
    End If
    Set rng = Nothing
    End Sub</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •