Edit Macro 1st time use

[sparx]

Is there a way by means of a macro a macro can check itself to see if it has some data entered into it that would normally be entered into say 2 unlocked cells that the user would normally use.

Lets say when you open a file a macro brings up a dialogue box and asks you to enter 12 lots of 6 digit numbers - when they are entered for the first time they are saved into the macro never to be requested again unless the macro is edited and the 12 sets of 6 numbers cleard out.

I have a worksheet that uses a macro to lookup your pc's mac address and displays it for you within excel - because a mac address is made up of 12 numbers or letters - ive written a formulae that matches the character code for each mac character and if the 6 digit number matches the character set number - unlocks it. When all 12 characters are matched the overall sheet is unlocked then all the program formula's can be used using the "if" command that currently works very well. This system works well if used on the same pc but when used on another - it locks up stopping the program from working - I want to be able to hide the 12 sets of 6 digits within the macro itself.

[BW]

sparx,

One way to save this type of information is to use a hidden range
name. (I'm sure there are other ways too, like using a hidden sheet.)
The hidden range name cannot be unhidden except with VBA.
Here is some code that I used to save the count of updates
to a hidden range name in a workbook. I also saved the
last date checked. Later I retrieve the saved values.

Dim UpdateCount As Long 'the current count of updated cells
Dim vbaSavedCount As Integer 'the count saved in a range
Dim vbaSavedDate As String 'the date saved in a range

'Note the code included below doesn't show how the
' current update count was done

'Check to see if a previous count has been saved
On Error GoTo FirstTimeMacroRun
ActiveWorkbook.Names("SavedCount").Visible = True
'test to see if the range name exists; if not, an error occurs
'SavedCount is the range name; see FirstTimeMacroRun:
ActiveWorkbook.Names("SavedCount").Visible = False
'set visible back to false
ssavedcount = ActiveWorkbook.Names("SavedCount").RefersTo
'assign the value of the range to the variable ssavedcount
'note that the returned value is a string,
'the first character is an equals sign
vbaSavedCount = CInt(Right(ssavedcount, Len(ssavedcount) - 1))
'strip the equals sign

'Get the saved date
sSavedDate = ActiveWorkbook.Names("SavedDate").RefersTo
vbaSavedDate = Format(CDate(Right(sSavedDate, _
Len(sSavedDate) - 1)), "m/d/yy h:mm a/p")

'Report the number of updated cells
MsgText1 = "The previous update count was " & vbaSavedCount
MsgText1 = MsgText1 & " on " & vbaSavedDate
Reply = MsgBox(MsgText1, vbOKOnly, Greeting)

'Check if the update count has changed; if it has, then resave the count
'and date
If UpdateCount <> vbaSavedCount Then
ssavedcount = CStr(UpdateCount) 'assign update count to a string
ActiveWorkbook.Names.Add Name:="SavedCount", _
Visible:=False, RefersTo:=ssavedcount

'convert the current time and date to a string and save it as a
'range name
vbaSavedDate = Now
sSavedDate = CStr(vbaSavedDate) 'convert the date to a string
ActiveWorkbook.Names.Add Name:="SavedDate", _
Visible:=False, RefersTo:=sSavedDate
End If

Exit Sub

FirstTimeMacroRun:

On Error GoTo 0 'return to normal error handling
'MsgBox "This was the first time run for this workbook: no savedcount"

'convert the count to a string and save it as a range name
ssavedcount = CStr(UpdateCount) 'assign update count to a string
ActiveWorkbook.Names.Add Name:="SavedCount", _
Visible:=False, RefersTo:=ssavedcount

'convert the current time & date to a string and save it as a range name
vbaSavedDate = Now
sSavedDate = CStr(vbaSavedDate) 'convert the date to a string
ActiveWorkbook.Names.Add Name:="SavedDate", _
Visible:=False, RefersTo:=sSavedDate

'Report the number of updated cells
MsgText1 = "The number of updated cells for is " & UpdateCount
MsgText1 = MsgText1 & Chr(13) & Chr(13)
MsgText1 = MsgText1 & "This is the first time the update count "
MsgText1 = MsgText1 & "has been run on this workbook"
Reply = MsgBox(MsgText1, vbOKOnly, Greeting)

HTH,

Brian

sparx wrote:
> Is there a way by means of a macro a macro can check itself to see if it
> has some data entered into it that would normally be entered into say 2
> unlocked cells that the user would normally use.
>
> Lets say when you open a file a macro brings up a dialogue box and asks
> you to enter 12 lots of 6 digit numbers - when they are entered for the
> first time they are saved into the macro never to be requested again
> unless the macro is edited and the 12 sets of 6 numbers cleard out.
>
> I have a worksheet that uses a macro to lookup your pc's mac address
> and displays it for you within excel - because a mac address is made up
> of 12 numbers or letters - ive written a formulae that matches the
> character code for each mac character and if the 6 digit number matches
> the character set number - unlocks it. When all 12 characters are
> matched the overall sheet is unlocked then all the program formula's
> can be used using the "if" command that currently works very well.
> This system works well if used on the same pc but when used on another
> - it locks up stopping the program from working - I want to be able to
> hide the 12 sets of 6 digits within the macro itself.
>
>