strange vba interaction

[Guest]

hi all!

i have some vba code that is located on the worksheet it refers to, inside
the sub

Private Sub Worksheet_Change(ByVal Target As Range)

what the code basically does is based on the users choice, some decisions
are made as to what goes in the next three adjacent cells (same row,
continuing to the right) and if the users input is further required, the user
is prompted for input.

this code works fine and dandy

in another cell (same row, 2 columns to the left of where the user makes
their choice)

is this code (in B14):

=IF(C14="","",WhatIsLeft(D14,From))

C14 is either off ("") or a number from 1 to 20 (it's a line item number)
D14 is the users choice
From is a named range consisting of a single cell.

this is the code for the function WhatIsLeft(String, String)

Public Function WhatIsLeft(lot As String, warehouse As String)
WhatIsLeft = 64
End Function

64 is just a test value, i haven't written the complete function yet.

after i put the formula in B14, the cells E14,G14 and H14 will not 'update'

i have run the debugger and the Worksheet_Change sub gets triggered, and
gets to a point where the 'From' cell is modified, jumps to the WhatIsLeft
function (which it shouldn't) - and quits.

i can't find an error anywhere.

any ideas there?

hopefully i explained this clear enough.

tia

J

 

[Guest]

the cell in from does not actually get modified when this 'interaction'
occurs - but the value returned by WhatIsLeft appears.
when the B14 is cleared, the whole thing works again.

 

[Jim Rech]

You didn't post your code (or better a dumbed down version of your Change
event code that duplicates the problem) so I can't "see" the problem. But
I'm not sure I have to. Over the years I've seen a lot of peculiarities
with UDFs and, as a result, I do not use them. Just say no.

--
Jim
| the cell in from does not actually get modified when this 'interaction'
| occurs - but the value returned by WhatIsLeft appears.
| when the B14 is cleared, the whole thing works again.
|
| "Gixxer_J_97" wrote:
|
| > hi all!
| >
| > i have some vba code that is located on the worksheet it refers to,
inside
| > the sub
| >
| > Private Sub Worksheet_Change(ByVal Target As Range)
| >
| > what the code basically does is based on the users choice, some
decisions
| > are made as to what goes in the next three adjacent cells (same row,
| > continuing to the right) and if the users input is further required, the
user
| > is prompted for input.
| >
| > this code works fine and dandy
| >
| > in another cell (same row, 2 columns to the left of where the user makes
| > their choice)
| >
| > is this code (in B14):
| >
| > =IF(C14="","",WhatIsLeft(D14,From))
| >
| > C14 is either off ("") or a number from 1 to 20 (it's a line item
number)
| > D14 is the users choice
| > From is a named range consisting of a single cell.
| >
| > this is the code for the function WhatIsLeft(String, String)
| >
| > Public Function WhatIsLeft(lot As String, warehouse As String)
| > WhatIsLeft = 64
| > End Function
| >
| > 64 is just a test value, i haven't written the complete function yet.
| >
| > after i put the formula in B14, the cells E14,G14 and H14 will not
'update'
| >
| > i have run the debugger and the Worksheet_Change sub gets triggered, and
| > gets to a point where the 'From' cell is modified, jumps to the
WhatIsLeft
| > function (which it shouldn't) - and quits.
| >
| > i can't find an error anywhere.
| >
| > any ideas there?
| >
| > hopefully i explained this clear enough.
| >
| > tia
| >
| > J
| >