Overriding a cell value?

[Cyberseeker]

I have a cell (B16) which has its content placed into it by the following macro:

If xferArray(14) <> 0 Then Cells(16, 2).Value = xferArray(14)
Else: Cells(20, 2).Value = ""
End If

However, in some instances I may wish to override it, so my cell has the code:

=IF(Inv_override,"",B16)

As with many of my bright ideas, it doesn't work. Im guessing that a small adjustment to my code will do the trick, but what? Can someone help?

[macropod]

Without seeing the rest of the code, it's impossible to know what's going on. That said, it's by no means apparent what:
=IF(Inv_override,"",B16)
is supposed to do.

[Cyberseeker]

OK, here is the file. If you look at the 'order' sheet, you will see that there are occasions when I want to override the Invoice number (B16-17), and the Post & Packaging. (B18-19)

P&P works correctly, but Invoice override doesnt.

The macro that deals with this is called 'Transfer Module'.

Hopefully Ive explained it.

[NoSparks]

Inv_override is name of cell B17
Can you put into words what you want to happen if B17 has something in it ?

ps: go into the Auto_Open module and uncomment Application.ScreeenUpdating = False
so the file will open quicker.

[Cyberseeker]

Originally Posted by NoSparks

Inv_override is name of cell B17
Can you put into words what you want to happen if B17 has something in it ?

ps: go into the Auto_Open module and uncomment Application.ScreenUpdating = False
so the file will open quicker.

Ive fixed the comment error, unprotected the 'order' sheet, and uploaded my attachment again. Please click 'View Group Records' button (c1) repeatedly and notice how the records from the other sheet are deposited in column 'B'.

An invoice number is automatically deposited in B16. However, I have had times when I have wanted to change this. So, I manually type the correct number in B17. It is supposed to override B16.

Post and package is similar, but it works in that case.

[NoSparks]

Assuming Inv_override is entered after the Transfer macro is run,
and assuming 'override B16' means over write B16,
then you should be able to use the Worksheet_Change event.
Try this in the "Order" sheet module

Private Sub Worksheet_Change(ByVal Target As Range)

'limit to single cell
If Target.Count > 1 Or Target.address <> "$B$17" Then Exit Sub
' value in b17 has changed to something other than blank
If Target.Value <> "" Then
    With Sheets("Order")
        .Unprotect
        Application.EnableEvents = False
        .Range("B16").Value = Target.Value
        Application.EnableEvents = True
        .Protect
    End With
End If

End Sub

[NoSparks]

Can this thread be marked 'Solved' or are there issues needing to be addressed ?

[Cyberseeker]

All done now, thanks.