Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Edit Macro 1st time use
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. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=521480 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Edit Macro 1st time use
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning macro value to starting cell (different each time) | Excel Worksheet Functions | |||
Can you perform ctrl+shift+; in a macro to display time? | Excel Discussion (Misc queries) | |||
Run macro based on time | Excel Worksheet Functions | |||
macro to copy and edit then delete a worksheet | Excel Discussion (Misc queries) | |||
Can I get a macro to run at a certain time of day? | Excel Discussion (Misc queries) |