Code stops working when custom function was added????

[gschwint]

I have a user form with a button on it where I want the following code to run when clicked.

Private Sub CommandButton1_Click()

Worksheets("Manure Source Info").Activate

[C34] = [D45]
[C35] = [D46]
[C36] = [D47]
UserForm5.Hide

End Sub

For some reason the code stops executing right after the worksheet activate command. It does not change the cell values nor hide the userform (it doesn't create an error either). Any ideas as to what might be causing this?

This code did work well until I made a few changes to the workbook and created some custom functions. Why would the creation of custom functions cause this to happen?

I have also discovered that this problem only affects code that references cells that utilizes a drop down list created with data validation to place an entry in a cell.

When I comment out the custom functions the code runs well.

Any idea as to what might be causing this?

George

 

[gschwint]

I have been trying different things in the past couple of days and have not come up with a reason for my problem. Any help/ideas would be appreciated. Thanks

George

 

[gschwint]

If anyone has any ideas at all on what might be causing this, I would appreciate the help. I have tried all that I know how to do and still no luck. Thanks.

George

 

[Norie]

George

You don't need to activate the sheet.

Perhaps this might work.

Private Sub CommandButton1_Click() 

With Worksheets("Manure Source Info")
     .Range("C34:36").Copy  = .Range("D45") 
End With
UserForm5.Hide 
End Sub

 

[Nimrod]

Hello gschwint

When I comment out the custom functions the code runs well.

Since "This Function" seems to be causing the problem I think you need to post that function here so that we may look at it . :wink:

 

[gschwint]

Here is one of the functions:

Function N_Applied(source As Integer, rate As Integer) As Integer
'Application.Volatile

Dim N As Long
Dim sl As String

If source = 1 Then
    N = Sheets("Field Specific Land Application").Range("H16")
    sl = Sheets("Field Specific Land Application").Range("Q16")
    If sl = "lbs/1000 gal" Then
        N_Applied = N * rate / 1000
    ElseIf sl = "lbs/ton" Then
        N_Applied = N * rate
    Else: N_Applied = 0
    End If
End If

If source = 2 Then
    N = Sheets("Field Specific Land Application").Range("AB16")
    sl = Sheets("Field Specific Land Application").Range("AI16")
    If sl = "lbs/1000 gal" Then
        N_Applied = N * rate / 1000
    ElseIf sl = "lbs/ton" Then
        N_Applied = N * rate
    Else: N_Applied = 0
    End If
End If

If source = 3 Then
    N = Sheets("Field Specific Land Application").Range("H20")
    sl = Sheets("Field Specific Land Application").Range("Q20")
    If sl = "lbs/1000 gal" Then
        N_Applied = N * rate / 1000
    ElseIf sl = "lbs/ton" Then
        N_Applied = N * rate
    Else: N_Applied = 0
    End If
End If

If source = 4 Then
    N = Sheets("Field Specific Land Application").Range("AB20")
    sl = Sheets("Field Specific Land Application").Range("AI20")
    If sl = "lbs/1000 gal" Then
        N_Applied = N * rate / 1000
    ElseIf sl = "lbs/ton" Then
        N_Applied = N * rate
    Else: N_Applied = 0
    End If
End If


End Function

Through some line by line commenting out items I discovered that if I comment out the application.volitale line everything performs as it should. However, I need to leave this line in the code because I want the calculations to done by this function to run at all times. Speciffically the function should run before a worksheet change event occurs. Is there a different way of doing that?

Thanks for the help.

George

 

[gschwint]

Any help with my problem?

The problem I have is that I want my custom function to calculate even though a cell it references has not been directly edited. I use application.volatile to accomplish that, this also allows the function to calculate prior to a worksheet change event, which is also something that I want. However, the application.volatile causes problems with other code I have; specifically the code assigned to a button that references a cell where a choice has been made using a data validation list. The code will not execute. Any ideas as to what is causing this to happen? Or, is there a different way to use application.volatile?

George

 

[tusharm]

Application.Volatile is almost always a crutch for some poor UDF design most often because information other than that passed to it in its arguments is used "behind XL's back" so to say.

Redesign your UDF to accept N and S1 as arguments (instead of source). Your whole UDF will collapse into the what is currently the code for a single source value.

So, with

Function N_Applied(N as Long, S1 as String, rate As Integer) As Integer

you would then use it as
=Function_N_Applied(H16, Q16, {rate})

Here is one of the functions:

Function N_Applied(source As Integer, rate As Integer) As Integer
'Application.Volatile

Dim N As Long
Dim sl As String

If source = 1 Then
    N = Sheets("Field Specific Land Application").Range("H16")
    sl = Sheets("Field Specific Land Application").Range("Q16")
 {snip}

Through some line by line commenting out items I discovered that if I comment out the application.volitale line everything performs as it should. However, I need to leave this line in the code because I want the calculations to done by this function to run at all times. Speciffically the function should run before a worksheet change event occurs. Is there a different way of doing that?

Thanks for the help.

George