Overriding a cell value?

Cyberseeker · #1 04-02-2017, 07:19 PM

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

Code:

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:

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 · #2 04-02-2017, 08:16 PM

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 · #3 04-02-2017, 09:04 PM

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 · #4 04-02-2017, 09:45 PM

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 · #5 04-02-2017, 10:36 PM

Quote:

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 · #6 04-03-2017, 07:04 AM

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

Code:

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 · #7 04-05-2017, 01:05 PM

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

Cyberseeker · #8 04-05-2017, 01:36 PM

All done now, thanks.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow	FUGMAN	Excel Programming	7	02-05-2017 08:37 AM
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C	rick10r	Excel	1	07-05-2016 12:07 PM
Data validation,force cell to be filed with number if respective cell is not empty	nicholes	Excel Programming	0	08-01-2015 09:08 AM
Assign the value of a cell as a cell reference of another cell in Excel 2010 - How to?	bharathkumarst	Excel	7	10-13-2014 10:25 AM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM