software

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 quot;ifquot; 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.--
sparx
------------------------------------------------------------------------
sparx's Profile: www.excelforum.com/member.php...oamp;userid=16787
View this thread: www.excelforum.com/showthread...hreadid=521480sparx,

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(quot;SavedCountquot;).Visible = True
'test to see if the range name exists; if not, an error occurs
'SavedCount is the range name; see FirstTimeMacroRun:
ActiveWorkbook.Names(quot;SavedCountquot;).Visible = False
'set visible back to false
ssavedcount = ActiveWorkbook.Names(quot;SavedCountquot;).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(quot;SavedDatequot;).RefersTo
vbaSavedDate = Format(CDate(Right(sSavedDate, _
Len(sSavedDate) - 1)), quot;m/d/yy h:mm a/pquot;)

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

'Check if the update count has changed; if it has, then resave the count
'and date
If UpdateCount lt;gt; vbaSavedCount Then
ssavedcount = CStr(UpdateCount) 'assign update count to a string
ActiveWorkbook.Names.Add Name:=quot;SavedCountquot;, _
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:=quot;SavedDatequot;, _
Visible:=False, RefersTo:=sSavedDate
End If

Exit Sub

FirstTimeMacroRun:

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

'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:=quot;SavedCountquot;, _
Visible:=False, RefersTo:=ssavedcount

'convert the current time amp; 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:=quot;SavedDatequot;, _
Visible:=False, RefersTo:=sSavedDate

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

HTH,

Brian

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