VBA to Identify changes in row1

[Guest]

Hello,

I inherit database files in Excel format from others. Because they are the
owner of the files, they sometimes modify the Header (in row 1) without
informing others.

I’m intending use a macro to verify if any additional new header has been
added or old header being removed.

Example today’s file header in row 1 is:
A1 = SupplierCode
B1 = SupName
C1 = SupGroup
D1 = Product
E1 = AltProduct
F1 = Description
……etc

Then, tomorrow, he could change by slotting in an extra header called
“Product Sales†right in the middle of any column.

I just need a very simple code. Just to get VBA to verify from cell A1 right
across to the last cell in row 1. Honestly, I don’t even need to know which
specific cell in row1 has been changed. All I need is for VBA to “tell me
there is a change†when I run the code.

Many of my other database suffers the same calamity. I don’t mind updating
by re-typing the header into your VBA codes. All I need is a way to spot such
changes.

Thanks a lot.

I'm a VBA rookie.

 

[davesexcel]

can you not protect the cells you don't want changed??

You could hilight the cells that are allowed to be changed and then
format.cells ,protection, uncheck locked

then protect the sheet, tools,protect sheet

the cells that are protected now cannot be changed

 

[jacod]

I'll bet this is a unique solution.
1) Being a new user, you may wish to investigate 'Option Explicit'. A
good help for them typos.
2) Since we are testing between sessions, we need to have some method
to hold the data over from one session to the next. You suggested
within a macro, so that's what I'll use.
3) We need one macro to record our data from one session and another
macro to compare that data to all later sessions until we find a
change. Then, we give the first macro another shot to re-record the
new data.
4) But wait. Instead of recording just the data, we can record the
whole, new macro so copying and pasting is VERY simple.
5) I've made good use of the .debug utiltity to create the second
macro from the first.

Start off as follows.
Option Explicit

Sub RecordHeaders()
Dim cell As Range

Debug.Print "Sub compheaders()"
For Each cell In Range("$A$1:" & [IV1].End(xlToLeft).Offset(0,
4).Address)
Debug.Print "If range(""" & cell.Address & """).value <> """;
cell.Value; """ then msgbox ""Change in " & cell.Address & """"
Next cell
Debug.Print "end sub"
End Sub

Run this first macro and the code for the new macro appears in the
immediate pane (choose View\Immediate Window (^G) if you don't know
about this).

Next, copy the code from the immediate window and insert it into your
main macro pane (replacing the old compheaders() if it exists). Then
run compheaders() until it shows you a change.

6) If you want to be a bit more advanced, write your code so there is
no new macro created. Store the data some other way. A couple of
suggestions ...
A) Please investigate the 'Open' statement to open a flat file to
store the header data from session to session. Other helpful
statements, if you go this route, may be 'Close', 'Line Input', and
'Write'.
B) Manually or programatically copy the header row to the
clipboard, open a new workbook, paste the header row and save the file.
Then, when the old workbook is opened (use the Private Sub
Workbook_Open() snippet provided for each workbook), compare the two
headers. This may be more elegant, but more difficult. Use the
'Record New Macro' facility to help.

Personally, I prefer the solution given. Seem really simple.

 

[Guest]

Dear jacod,

You are a fantastic! Thank you so very much for sharing in such great detail
& clarity.

Besides getting my answer, you’ve given me a Big Value-Added Bonus with such
an excellent method of achieving the task. It was a priceless eye-opener to a
rookie like me. Thank you for whetting my appetite for VBA.

Thanks to all the great people like you, I’m very motivated to learn more of
VBA from today onwards!