Hi all

I have a problem, in that i have a spreadsheet with a
column with a unique codes (next to the codes in the same
row are products), i now want to send this spreadsheet (or
part of it) to a customer but i DO NOT want them to see
the unique code (as it identifies who our supplier is), so
i want to make a temporary code, but as the customer will
mess around with the spreadsheet i don't just want to make
a code up, so i want to XOR (or use a cipher of somekind)
to change the code. this new code must be unique, and must
be able to change back to the original code.

I guess this includes macro's, but i ain't got a clue how
to use them!

Thanks in advance

You can use ROT13 encoding. It rotates the character code by 13 positions,
so the same algorithm can be used for encoding and decoding.

Sub encode()

Dim S As String
Dim s1 As String
Dim s2 As String

S = InputBox("enter String")
s1 = Rot13$(S)
' now decode
s2 = Rot13$(s1)
MsgBox _
"original: " & S & vbNewLine & _
"encoded: " & s1 & vbNewLine & _
"decoded: " & s2
End Sub

Function Rot13$(S As String)
Dim I, J As Integer
Dim T As String

For I = 1 To Len(S)
J = Asc(Mid$(S, I, 1))
If J = 65 And J <= 90 Then
J = ((J - 52) Mod 26) + 65
ElseIf J = 97 And J <= 122 Then
J = ((J - 84) Mod 26) + 97
End If
T = T + Chr$(J)
Next I
Rot13$ = T
End Function


New to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Tutorials:
http://www.mvps.org/dmcritchie/excel....htm#tutorials

the vba tutorials are after the excel tutorials.

--
Regards,
Tom Ogilvy


jase wrote in message
...


Hi all

I have a problem, in that i have a spreadsheet with a
column with a unique codes (next to the codes in the same
row are products), i now want to send this spreadsheet (or
part of it) to a customer but i DO NOT want them to see
the unique code (as it identifies who our supplier is), so
i want to make a temporary code, but as the customer will
mess around with the spreadsheet i don't just want to make
a code up, so i want to XOR (or use a cipher of somekind)
to change the code. this new code must be unique, and must
be able to change back to the original code.

I guess this includes macro's, but i ain't got a clue how
to use them!

Thanks in advance

A macro would certainly do the job but anyone writing the code would
need to have details of the structures of the code e.g Are we looking
at numbers, uppercase/Lower case letters. Are there sometimes other
characters e.g. # * - etc


DavidP

On Tue, 11 Nov 2003 03:35:56 -0800, "jase"
wrote:



Hi all

I have a problem, in that i have a spreadsheet with a
column with a unique codes (next to the codes in the same
row are products), i now want to send this spreadsheet (or
part of it) to a customer but i DO NOT want them to see
the unique code (as it identifies who our supplier is), so
i want to make a temporary code, but as the customer will
mess around with the spreadsheet i don't just want to make
a code up, so i want to XOR (or use a cipher of somekind)
to change the code. this new code must be unique, and must
be able to change back to the original code.

I guess this includes macro's, but i ain't got a clue how
to use them!

Thanks in advance

Hi Jase,

Is the supplier ID required for some reason (i.e. is it used in
formulas or something)? I'm assuming there's a reason why you can't
simply delete the codes before distribution and put them back later
based on some other criteria. I'm assuming you know you can hide
columns and/or use a format of ";;;" or set the font to white so the
cell looks empty. This is obviously not very secure, but just some
options.

If you want anything half-way secure ... you'll need code (macros as
you described). I say "half-way" because Excel is not very secure
even if you password protect your code. If an expert really wants to
decifer your Excel file they can ... and very quickly I might add ...
there are better solutions than Excel if security is really critical.

I'm no security expert, but in the past I've used some custom VBA
functions w/ psuedo random sequences to create unique codes as you
described. Then a secret "seed" can be used to decipher what the
orginal code was. As you can imagine, this can get complex and is
still relatively easy to decipher. Frankly, why make this so complex?
Guess I'm missing something.

Why don't you simply substitute the supplier codes with something only
you know the meaning of? Change your formulas if necessary. Example:
say your supplier is Microsoft (MSFT). Change "MSFT" in your sheet
to "A" or "1" ... whatever. If necessary convert the temp codes back
when the customer returns the file to you. This is simple and
probably doesn't even need to be automated, although it could be with
code.

Hope this helps and gives you some ideas.

Regards,
Steve Hieb