UDF, Change Event and Dropdown boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,

Excel 2000.

I have a UDF that looks like this (many thanks to Bob Phillips):

Function Function_Name(Code As Integer, Job As String, Country As String)
Dim sFormula As String

Application.Volatile
sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)

End Function

In several worksheets in the same workbook I have put a Worksheet Change
Event to display "Not updated". The code look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[Indicator].Value = "Not Updated"
End Sub

In the worksheets where I have the Worksheet Change Event I have dropdown
boxes (Validation - List) to ensure correct data entry.

Now... when I put Application.Volatile into the UDF, the UDF recalculates
automatically but the Worksheet Change Event is not executed when using the
dropdown boxes. Why?

If I remove Application.Volatile the Worksheet Change Event is executed
using the dropdown boxes but the UDF does not recalculate (as expected).

Talk about Catch22. Is there a piece of code I can add in the Worksheet
Change Event and/or the UDF to get them both to work properly?

Help much appreciated.
 
You can call the change event from within your UDF. It is a procedure like
any other procedure. You may need to change it to be public from private and
you have to supply it a range object (cell or group of cells).

Call Worksheet_Change(Sheets("Sheet1").Range("A1"))

or something like that...
 
I assume that your UDF is in a standard module, so if you do what Jim
suggests, you will need to make the event code public (It defaults to
private), and pre-pend the sheet codename to the event name.

--
HTH

Bob Phillips

Jim Thomlinson said:
You can call the change event from within your UDF. It is a procedure like
any other procedure. You may need to change it to be public from private and
you have to supply it a range object (cell or group of cells).

Call Worksheet_Change(Sheets("Sheet1").Range("A1"))

or something like that...
--
HTH...

Jim Thomlinson


Martin said:
Dear All,

Excel 2000.

I have a UDF that looks like this (many thanks to Bob Phillips):

Function Function_Name(Code As Integer, Job As String, Country As String)
Dim sFormula As String

Application.Volatile
sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)

End Function

In several worksheets in the same workbook I have put a Worksheet Change
Event to display "Not updated". The code look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[Indicator].Value = "Not Updated"
End Sub

In the worksheets where I have the Worksheet Change Event I have dropdown
boxes (Validation - List) to ensure correct data entry.

Now... when I put Application.Volatile into the UDF, the UDF recalculates
automatically but the Worksheet Change Event is not executed when using the
dropdown boxes. Why?

If I remove Application.Volatile the Worksheet Change Event is executed
using the dropdown boxes but the UDF does not recalculate (as expected).

Talk about Catch22. Is there a piece of code I can add in the Worksheet
Change Event and/or the UDF to get them both to work properly?

Help much appreciated.
Click to expand...
Click to expand...
 
Thanks a lot for your response.

I am not sure what "pre-pend the sheet codename to the event name" means?

My UDF is located in a standard module and I have changed the Change Event
from Private to Public and added
Call Worksheet_Change(Sheets("Sheet3").Range("A1:P100")) into the UDF.

Now I get a VB error message:
Compile error:
Sub or Function not defined

--
Regards,

Martin


Bob Phillips said:
I assume that your UDF is in a standard module, so if you do what Jim
suggests, you will need to make the event code public (It defaults to
private), and pre-pend the sheet codename to the event name.

--
HTH

Bob Phillips

Jim Thomlinson said:
You can call the change event from within your UDF. It is a procedure like
any other procedure. You may need to change it to be public from private and
you have to supply it a range object (cell or group of cells).

Call Worksheet_Change(Sheets("Sheet1").Range("A1"))

or something like that...
--
HTH...

Jim Thomlinson


Martin said:
Dear All,

Excel 2000.

I have a UDF that looks like this (many thanks to Bob Phillips):

Function Function_Name(Code As Integer, Job As String, Country As String)
Dim sFormula As String

Application.Volatile
sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)

End Function

In several worksheets in the same workbook I have put a Worksheet Change
Event to display "Not updated". The code look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[Indicator].Value = "Not Updated"
End Sub

In the worksheets where I have the Worksheet Change Event I have dropdown
boxes (Validation - List) to ensure correct data entry.

Now... when I put Application.Volatile into the UDF, the UDF recalculates
automatically but the Worksheet Change Event is not executed when using the
dropdown boxes. Why?

If I remove Application.Volatile the Worksheet Change Event is executed
using the dropdown boxes but the UDF does not recalculate (as expected).

Talk about Catch22. Is there a piece of code I can add in the Worksheet
Change Event and/or the UDF to get them both to work properly?

Help much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
pre-pend is opposite of append. You append after something, and prepen
before something.

Here Bob meant to use the parent sheet of the event. For e.g. th
combo-box is in sheet2 then use it as follows:

Sheet2.ComboBox1.Value

Herte you pre-pended the Sheet2 to ComboBox.Value

Manges
 
Open your workbook
Go into the VBE (alt-f11)
hit f4 to see the VBA project explorer
Find your workbook's project.

Hit the asterisk on the numeric keypad to expand all the branches of your
project.

You'll see a branch called: "Microsoft Excel Objects"
Then you'll see items like:

sheet1(sheet1)
sheet2(sheet2)
sheet3(sheet3)

Then name in parentheses is the name you see when you're in excel and look at
the worksheet tab.

The name (not in parentheses) is the code name for that sheet.

(It's not readily available to the user, so lots of developers will use that
code name instead of "worksheets("sheet1")--'cause if the user changes the name
of the worksheet, the code can break pretty easily.)

Then inside that worksheet module, remove Private from the Sub's declaration.

So:
Private Sub Worksheet_Change(ByVal Target As Range)
becomes
Sub Worksheet_Change(ByVal Target As Range)

So if you wanted to call that event for the sheet with codename Sheet3, you
could do:

Option Explicit
Sub testme()
Call Sheet3.Worksheet_Change(Sheets("Sheet3").Range("A1:P100"))
End Sub

======
You can make the codenames more mnemonically significant (sheet3 doesn't tell
you much about what it holds) by:

Expanding the project (like above) in the VBE
select the sheet object
hit the F4 button to see its properties
change the (Name) property to what you want.
(This is the one at the top of the alphabetical list.)

The Name (no parens) is what appears on the worksheet tab.

Thanks a lot for your response.

I am not sure what "pre-pend the sheet codename to the event name" means?

My UDF is located in a standard module and I have changed the Change Event
from Private to Public and added
Call Worksheet_Change(Sheets("Sheet3").Range("A1:P100")) into the UDF.

Now I get a VB error message:
Compile error:
Sub or Function not defined

--
Regards,

Martin

Bob Phillips said:
I assume that your UDF is in a standard module, so if you do what Jim
suggests, you will need to make the event code public (It defaults to
private), and pre-pend the sheet codename to the event name.

--
HTH

Bob Phillips

Jim Thomlinson said:
You can call the change event from within your UDF. It is a procedure like
any other procedure. You may need to change it to be public from private and
you have to supply it a range object (cell or group of cells).

Call Worksheet_Change(Sheets("Sheet1").Range("A1"))

or something like that...
--
HTH...

Jim Thomlinson


:

Dear All,

Excel 2000.

I have a UDF that looks like this (many thanks to Bob Phillips):

Function Function_Name(Code As Integer, Job As String, Country As String)
Dim sFormula As String

Application.Volatile
sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)

End Function

In several worksheets in the same workbook I have put a Worksheet Change
Event to display "Not updated". The code look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[Indicator].Value = "Not Updated"
End Sub

In the worksheets where I have the Worksheet Change Event I have dropdown
boxes (Validation - List) to ensure correct data entry.

Now... when I put Application.Volatile into the UDF, the UDF recalculates
automatically but the Worksheet Change Event is not executed when using the
dropdown boxes. Why?

If I remove Application.Volatile the Worksheet Change Event is executed
using the dropdown boxes but the UDF does not recalculate (as expected).

Talk about Catch22. Is there a piece of code I can add in the Worksheet
Change Event and/or the UDF to get them both to work properly?

Help much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Hi Dave,

Thank you for your response. But I think I might need to explain the main
problem again.

The Worksheet_Change event is not working together with the UDF when the
dropdown boxes have been used to make a change however it is working together
with the UDF when the change is keyed in from the keyboard.

--
Regards,

Martin


Dave Peterson said:
Open your workbook
Go into the VBE (alt-f11)
hit f4 to see the VBA project explorer
Find your workbook's project.

Hit the asterisk on the numeric keypad to expand all the branches of your
project.

You'll see a branch called: "Microsoft Excel Objects"
Then you'll see items like:

sheet1(sheet1)
sheet2(sheet2)
sheet3(sheet3)

Then name in parentheses is the name you see when you're in excel and look at
the worksheet tab.

The name (not in parentheses) is the code name for that sheet.

(It's not readily available to the user, so lots of developers will use that
code name instead of "worksheets("sheet1")--'cause if the user changes the name
of the worksheet, the code can break pretty easily.)

Then inside that worksheet module, remove Private from the Sub's declaration.

So:
Private Sub Worksheet_Change(ByVal Target As Range)
becomes
Sub Worksheet_Change(ByVal Target As Range)

So if you wanted to call that event for the sheet with codename Sheet3, you
could do:

Option Explicit
Sub testme()
Call Sheet3.Worksheet_Change(Sheets("Sheet3").Range("A1:P100"))
End Sub

======
You can make the codenames more mnemonically significant (sheet3 doesn't tell
you much about what it holds) by:

Expanding the project (like above) in the VBE
select the sheet object
hit the F4 button to see its properties
change the (Name) property to what you want.
(This is the one at the top of the alphabetical list.)

The Name (no parens) is what appears on the worksheet tab.

Thanks a lot for your response.

I am not sure what "pre-pend the sheet codename to the event name" means?

My UDF is located in a standard module and I have changed the Change Event
from Private to Public and added
Call Worksheet_Change(Sheets("Sheet3").Range("A1:P100")) into the UDF.

Now I get a VB error message:
Compile error:
Sub or Function not defined

--
Regards,

Martin

Bob Phillips said:
I assume that your UDF is in a standard module, so if you do what Jim
suggests, you will need to make the event code public (It defaults to
private), and pre-pend the sheet codename to the event name.

--
HTH

Bob Phillips

You can call the change event from within your UDF. It is a procedure like
any other procedure. You may need to change it to be public from private
and
you have to supply it a range object (cell or group of cells).

Call Worksheet_Change(Sheets("Sheet1").Range("A1"))

or something like that...
--
HTH...

Jim Thomlinson


:

Dear All,

Excel 2000.

I have a UDF that looks like this (many thanks to Bob Phillips):

Function Function_Name(Code As Integer, Job As String, Country As
String)
Dim sFormula As String

Application.Volatile
sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)

End Function

In several worksheets in the same workbook I have put a Worksheet Change
Event to display "Not updated". The code look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[Indicator].Value = "Not Updated"
End Sub

In the worksheets where I have the Worksheet Change Event I have
dropdown
boxes (Validation - List) to ensure correct data entry.

Now... when I put Application.Volatile into the UDF, the UDF
recalculates
automatically but the Worksheet Change Event is not executed when using
the
dropdown boxes. Why?

If I remove Application.Volatile the Worksheet Change Event is executed
using the dropdown boxes but the UDF does not recalculate (as expected).

Talk about Catch22. Is there a piece of code I can add in the Worksheet
Change Event and/or the UDF to get them both to work properly?

Help much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I missed the part that you were calling this from a UDF.

UDF's can return values to the cell--they can't change other stuff on the
worksheet.

Maybe you could move the code from the UDF and put it in the worksheet_change
event?????

Hi Dave,

Thank you for your response. But I think I might need to explain the main
problem again.

The Worksheet_Change event is not working together with the UDF when the
dropdown boxes have been used to make a change however it is working together
with the UDF when the change is keyed in from the keyboard.

--
Regards,

Martin

Dave Peterson said:
Open your workbook
Go into the VBE (alt-f11)
hit f4 to see the VBA project explorer
Find your workbook's project.

Hit the asterisk on the numeric keypad to expand all the branches of your
project.

You'll see a branch called: "Microsoft Excel Objects"
Then you'll see items like:

sheet1(sheet1)
sheet2(sheet2)
sheet3(sheet3)

Then name in parentheses is the name you see when you're in excel and look at
the worksheet tab.

The name (not in parentheses) is the code name for that sheet.

(It's not readily available to the user, so lots of developers will use that
code name instead of "worksheets("sheet1")--'cause if the user changes the name
of the worksheet, the code can break pretty easily.)

Then inside that worksheet module, remove Private from the Sub's declaration.

So:
Private Sub Worksheet_Change(ByVal Target As Range)
becomes
Sub Worksheet_Change(ByVal Target As Range)

So if you wanted to call that event for the sheet with codename Sheet3, you
could do:

Option Explicit
Sub testme()
Call Sheet3.Worksheet_Change(Sheets("Sheet3").Range("A1:P100"))
End Sub

======
You can make the codenames more mnemonically significant (sheet3 doesn't tell
you much about what it holds) by:

Expanding the project (like above) in the VBE
select the sheet object
hit the F4 button to see its properties
change the (Name) property to what you want.
(This is the one at the top of the alphabetical list.)

The Name (no parens) is what appears on the worksheet tab.

Thanks a lot for your response.

I am not sure what "pre-pend the sheet codename to the event name" means?

My UDF is located in a standard module and I have changed the Change Event
from Private to Public and added
Call Worksheet_Change(Sheets("Sheet3").Range("A1:P100")) into the UDF.

Now I get a VB error message:
Compile error:
Sub or Function not defined

--
Regards,

Martin

:

I assume that your UDF is in a standard module, so if you do what Jim
suggests, you will need to make the event code public (It defaults to
private), and pre-pend the sheet codename to the event name.

--
HTH

Bob Phillips

You can call the change event from within your UDF. It is a procedure like
any other procedure. You may need to change it to be public from private
and
you have to supply it a range object (cell or group of cells).

Call Worksheet_Change(Sheets("Sheet1").Range("A1"))

or something like that...
--
HTH...

Jim Thomlinson


:

Dear All,

Excel 2000.

I have a UDF that looks like this (many thanks to Bob Phillips):

Function Function_Name(Code As Integer, Job As String, Country As
String)
Dim sFormula As String

Application.Volatile
sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)

End Function

In several worksheets in the same workbook I have put a Worksheet Change
Event to display "Not updated". The code look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[Indicator].Value = "Not Updated"
End Sub

In the worksheets where I have the Worksheet Change Event I have
dropdown
boxes (Validation - List) to ensure correct data entry.

Now... when I put Application.Volatile into the UDF, the UDF
recalculates
automatically but the Worksheet Change Event is not executed when using
the
dropdown boxes. Why?

If I remove Application.Volatile the Worksheet Change Event is executed
using the dropdown boxes but the UDF does not recalculate (as expected).

Talk about Catch22. Is there a piece of code I can add in the Worksheet
Change Event and/or the UDF to get them both to work properly?

Help much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top